Macro to save file at runtime - How to?

  • Thread starter Thread starter Mickey A
  • Start date Start date
M

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?
 
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
 
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
 
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
 
Back
Top