Variable Filenames
 

Hello,

I have a macro which incorporates a routine that writes files to a
local directory. I use the simple SaveAs Filename script, as follows:

ActiveWorkbook.SaveAs Filename:="C:\S&P 500 INDEX.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Which is fine, if I know the filename that I'm going to use in advance.
However, in most of the jobs I'm trying to run, I won't know the file
names in advance. These will come from a lookup that the macro
performs. Does anyone know how I might generalize the script above to
incorporate a variable? I was thinking about declaring a variable,
setting it equal to whatever filename it should be, and somehow using
that variable to save the file, so:


Dim currentfile as String '(or whatever the case may be)

currentfile=ActiveCell '(the cell which will contain the filename)


But how do I then use this variable 'currentfile' in a SaveAs script?
Obviously, if I use "currentfile" it will be saved as
"currentfile.xls". Do I simply put this in parentheses, instead? or is
there some completely different syntax that I'm missing? Apologies in
advance if this is way too elementary a question. Any help is much
appreciated!!

-Paul



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Variable Filenames
 

Hi Paul:

Filename:=currentfile

assuming the string currentfile includes the .xls extension. No quotes
should be used.

Regards,

Vasant.

"Paul Kendall" wrote in message
...
Hello,

I have a macro which incorporates a routine that writes files to a
local directory. I use the simple SaveAs Filename script, as follows:

ActiveWorkbook.SaveAs Filename:="C:\S&P 500 INDEX.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Which is fine, if I know the filename that I'm going to use in advance.
However, in most of the jobs I'm trying to run, I won't know the file
names in advance. These will come from a lookup that the macro
performs. Does anyone know how I might generalize the script above to
incorporate a variable? I was thinking about declaring a variable,
setting it equal to whatever filename it should be, and somehow using
that variable to save the file, so:


Dim currentfile as String '(or whatever the case may be)

currentfile=ActiveCell '(the cell which will contain the filename)


But how do I then use this variable 'currentfile' in a SaveAs script?
Obviously, if I use "currentfile" it will be saved as
"currentfile.xls". Do I simply put this in parentheses, instead? or is
there some completely different syntax that I'm missing? Apologies in
advance if this is way too elementary a question. Any help is much
appreciated!!

-Paul



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Variable Filenames
 

Thanks!! I appreciate your help.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/