Macro to save file at runtime - How to?

[Mickey A]

I am trying to write a macro to take a filename from a cell(I can do that)
and store in a variable. THen I want to save the current sheet into a new
Excel File(SAVE AS command). When I attempted to record the macro going
through the menu commands, it recorded the following:

ActiveWorkbook.SaveAs Filename:="C:\file.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=
_
False, CreateBackup:=False

Now what I want is to insert the filename variable in place of the
"C:\file.xls". Seemed pretty simple but it doesn't work.

Anythoughts on how to do this?

 

[Dave Peterson]

dim myFileName as string
'something that changes myfilename to what you want here...
myfilename="C:\folder\" & activesheet.range("a1").value & ".xls"

ActiveWorkbook.SaveAs Filename:=myfilename, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

 

[Mickey A]

Thanks that worked. That is exactly what I did yesterday or so I thought.
But today is a new day. I had to work around the file replace dialog by
killing the file first.

Thanks again.


dim myFileName as string
'something that changes myfilename to what you want here...
myfilename="C:\folder\" & activesheet.range("a1").value & ".xls"

ActiveWorkbook.SaveAs Filename:=myfilename, FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=
_
False, CreateBackup:=False

 

[Dave Peterson]

You can kill the existing file or you could just stop the warning:

application.displayalerts=false
your code to save the file
application.displayalerts=true