| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! -- Object ------------------------------------------------------------------------ Object's Profile: http://www.excelforum.com/member.php...o&userid=33752 View this thread: http://www.excelforum.com/showthread...hreadid=537649 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
Thanks alot Tom, all working perfectly. Tom Ogilvy Wrote: 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 ------------------------------------------------------------------------ Object's Profile: http://www.excelforum.com/member.php...o&userid=33752 View this thread: http://www.excelforum.com/showthread...hreadid=537649 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save file using part of folder name
Yes, there are several subdirectories under "H:\2006\". The directory could potentially not exist aswell, in which case i would like the script to stop. Tim Williams Wrote: 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 ------------------------------------------------------------------------ Object's Profile: http://www.excelforum.com/member.php...o&userid=33752 View this thread: http://www.excelforum.com/showthread...hreadid=537649 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Save file to same directory folder | Excel Discussion (Misc queries) | |||
| in which folder can you save a .CSV file? | Excel Discussion (Misc queries) | |||
| Need code to save file to new folder, erase from old folder | Excel Discussion (Misc queries) | |||
| open file from folder save in new folder | Excel Programming | |||
| How do I save a read only file or folder? | Excel Discussion (Misc queries) | |||
Hybrid Mode
