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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com