Save to Path & Variable file name

O

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
 
C

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
 
D

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)
 
O

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
 
O

Otto Moehrbach

David
Thanks for that and the explanation. Another entry for my HowTo file.
Otto
David McRitchie said:
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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Otto Moehrbach said:
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
Click to expand...
Click to expand...
 
C

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
 
O

Otto Moehrbach

Thanks Chip, that simplifies things. Otto
Chip Pearson said:
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
Click to expand...
 
Top