Save to Path & Variable file name

[Otto Moehrbach]

Excel 2002, WinXP
When I know the file name, the following code works just fine:
ActiveWorkbook.SaveCopyAs ("C:\Folder1\Folder2\Folder3\MyFile.xls")

I now need to do the same thing but with a variable file name, say
VariableFileName (includes ".xls").
My problem is that I can't get the syntax right.
How do write the above line of code if the file name is a variable? Thanks
for your help. Otto

 

[Chip Pearson]

Otto,

If VarName contains the complete file name, just use something
like

ActiveWorkbook.SaveCopyAs VarName


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[David McRitchie]

Hi Otto,
I presume your were concerned with the double quotes.
The double quotes just enclose a string.
If you separate that string it might be easier to see

dim str as string
str = "C:\Folder1\Folder2\Folder3\MyFile.xls"
activeworkbook.savecopyas(str)

another example.

dim str as string, myfile as string
myfile = "MyFile.xls"
str = "c:\folder1\folder2\folder3\" & myfile
activeworkbook.savecopyas(str)

 

[Otto Moehrbach]

Chip
I've done that in the past and ended up saving the copy in some folder I
didn't want. That's why I specified the folder path. That brings up
another question. How do I set the current (default?) folder path so that
if I do what you said, the file would be saved in that folder? Thanks for
your help. Otto

 

[Otto Moehrbach]

David
Thanks for that and the explanation. Another entry for my HowTo file.
Otto

 

[Chip Pearson]

Otto,

You can use CurDir to return as a string the current default
drive and directory. E.g.,

Dim S As String
S = CurDir

You can use ChDrive and ChDir to change the default drive and
directory. E.g.,

ChDrive "C:"
ChDir "C:\Folder1\Folder2"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Otto Moehrbach]

Thanks Chip, that simplifies things. Otto

Otto,

You can use CurDir to return as a string the current default
drive and directory. E.g.,

Dim S As String
S = CurDir

You can use ChDrive and ChDir to change the default drive and
directory. E.g.,

ChDrive "C:"
ChDir "C:\Folder1\Folder2"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com