Save file using part of folder name

Hi,

Is there a way to save to a folder when only part of the folder name is known?

The path i want to save it into H:\2006\2006_SomeProjectName\transmittal.xls

2006 is stored in a variable ProjectYear and will always be know.
SomeProjectName will always be unknown.

So with the exceptioin of SomeProjectName (which i have shown as question marks) the save path would look something like this.
ProjectPathXLS = "H:\" + ProjectYear + "\" + ProjectYear + ???? + "\ transmittal.xls"

Thanks for any help!

Well, you could perhaps try to discover what it is:

s = "H:\" & ProjectYear & "\"

MyPath = s & ProjectYear & "*" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.

If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
Msgbox s & MyName ' Display entry only if it
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop

But to actually save, you would need the actual path which you should be
able to get from the above code.

--
Regards,
Tom Ogilvy



"Object" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> Is there a way to save to a folder when only part of the folder name is
> known?
>
> The path i want to save it into
> H:\2006\2006_SomeProjectName\transmittal.xls
>
> 2006 is stored in a variable ProjectYear and will always be know.
> SomeProjectName will always be unknown.
>
> So with the exceptioin of SomeProjectName (which i have shown as
> question marks) the save path would look something like this.
> ProjectPathXLS = "H:\" + ProjectYear + "\" + ProjectYear + ???? + "\
> transmittal.xls"
>
> Thanks for any help!
>
>
> --
> Object
> ------------------------------------------------------------------------
> Object's Profile:
http://www.excelforum.com/member.php...o&userid=33752
> View this thread: http://www.excelforum.com/showthread...hreadid=537649
>

Is there only ever one subdirectory under "H:\2006\" ?

What if there were two: (eg) 2006_SomeProjectName and 2006_SomeOtherProject ?
Could that ever happen ?

Tim


"Object" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> Is there a way to save to a folder when only part of the folder name is
> known?
>
> The path i want to save it into
> H:\2006\2006_SomeProjectName\transmittal.xls
>
> 2006 is stored in a variable ProjectYear and will always be know.
> SomeProjectName will always be unknown.
>
> So with the exceptioin of SomeProjectName (which i have shown as
> question marks) the save path would look something like this.
> ProjectPathXLS = "H:\" + ProjectYear + "\" + ProjectYear + ???? + "\
> transmittal.xls"
>
> Thanks for any help!
>
>
> --
> Object
> ------------------------------------------------------------------------
> Object's Profile: http://www.excelforum.com/member.php...o&userid=33752
> View this thread: http://www.excelforum.com/showthread...hreadid=537649
>

Yes, I didn't completely revise the code (it is some of the sample code from
the DIR command)

the offending line should actually be


If (GetAttr(s & MyName) And vbDirectory) = vbDirectory Then

--
Regards,
Tom Ogilvy


"Object" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Tom,
>
> However the script keeps stopping at
>
> If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
>
> any ideas?
>
>
>
> Tom Ogilvy Wrote:
> > Well, you could perhaps try to discover what it is:
> >
> > s = "H:\" & ProjectYear & "\"
> >
> > MyPath = s & ProjectYear & "*" ' Set the path.
> > MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
> > Do While MyName <> "" ' Start the loop.
> > ' Ignore the current directory and the encompassing directory.
> > If MyName <> "." And MyName <> ".." Then
> > ' Use bitwise comparison to make sure MyName is a directory.
> >
> > If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
> > Msgbox s & MyName ' Display entry only if it
> > End If ' it represents a directory.
> > End If
> > MyName = Dir ' Get next entry.
> > Loop
> >
> > But to actually save, you would need the actual path which you should
> > be
> > able to get from the above code.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
>
>
> --
> Object
> ------------------------------------------------------------------------
> Object's Profile:
http://www.excelforum.com/member.php...o&userid=33752
> View this thread: http://www.excelforum.com/showthread...hreadid=537649
>