PDA

View Full Version : Solved: Variable Filename / No Drive Letter Chosen



thomaspatton
03-14-2008, 02:20 PM
I've been playing around with a couple options I've seen in other places but I can't seem to get any of them to work right. I am trying in my Workbook_BeforeSave event to check the workbook name (WkBk v3.27) and if it's named the original file, name it "AMO-"& .Range.value.

I get the filename to save if it's just saving on my computer, however, I need to save the file to a different directory on an external(mobile) harddrive. Unfortunately, since everyone computer is different, I can't use Drive letter E: as a constant in the filepath.

How can I find the drive letter that the open file came from so I can save the open file as a different name under the same drive letter/different folder?

Bob Phillips
03-14-2008, 02:32 PM
left$(activeworkbook.path,2)

thomaspatton
03-17-2008, 05:59 AM
Thanks for the quick response Friday XLD, and sorry it took so long to get back, but I backed up the wrong file on my thumbdrive before I left work.

Anyway, I tried what you said and so far I've got this :
Public Sub AutoSv()
Dim OrgDir As String
OrgDir = Left$(ActiveWorkbook.Path, 2) & "amo\student information\blank materials\"
ActiveWorkbook.SaveAs Filename:=OrgDir & "AMO-" & _
Worksheets("Class Information").Range("j1").Value & ".xls"
End Sub


Unfortunately, I'm getting an "Excel can not read this directory" error.

Sub is in a Module.
When I run it, it returns the error because it's finding the wrong folder, or rather, not the folder I'm looking for.

I'm running the file from my desktop and instead of saving the file to "C:\amo\student information\blank materials\" as filename "AMO-13-13.xls", it's trying to save it as "C:\documents and settings\username\my documents\amo\student information\blank materials\".

What am I missin here?

Bob Phillips
03-17-2008, 06:17 AM
Try this



Public Sub AutoSv()
Dim OrgDir As String
OrgDir = Left$(ActiveWorkbook.Path, 2) & "\amo\student information\blank materials\"
ActiveWorkbook.SaveAs Filename:=OrgDir & "AMO-" & _
Worksheets("Class Information").Range("j1").Value & ".xls"
End Sub

thomaspatton
03-17-2008, 06:29 AM
wow... something so small... thanks yet again.