Save as in VBA

[KatriinaL]

Hi,

I'm trying to make an my excel macro to save the file with a new name at the end of the script. The script saves the file but not in excel macro enabled format. Could you help me to correct it so that it would save my file as .xlsm? Thanks for your help!

Sub save_as()

Dim workbook_Name As Variant

workbook_Name = Application.GetSaveAsFilename

If workbook_Name <> False Then
ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=52

End If

End Sub

 

[Fluff]

HI & welcome to MrExcel.
What format is it being saved in?
Also are you adding a file extension when you specify the name?

 

[vmjan02]

try this

Dim wb as workbook
 set wb = workbooks("savefile.xlsm")
wb.save

 

[Fluff]

@vmjan02
The OP is trying to SaveAs not save, they are two totally different things.

 

[vmjan02]

@Fluff
correct, so I have given the eg to save the file with the name and ext

thanks

 

[Fluff]

But that will only save an existing file, it does not do what the OP is asking for.

 

[KatriinaL]

Thank you! I'm happy to join the forum. I'm rather new with macros and I spend lot of time googling around to find tips how to make things work.

The macro that I'm working with collects data from several files and after the data is collected I would like to save it with new file name (user specified).
The master excel that I'm working with is in .xlsm format.

I have not added any extension to the file name. I was hoping that I don't have to do that.

 

[Trevor G]

You do need to add the file extension.

 

[Fluff]

No need to add the extension, when you select the file name & folder, you can use

Sub save_as()

Dim workbook_Name As Variant

workbook_Name = Application.GetSaveAsFilename

If workbook_Name <> False Then
ActiveWorkbook.SaveAs workbook_Name & ".xlsm", 52

End If

End Sub

 

[Fluff]

Another option, this will check if the extension has been added.

Sub save_as()

Dim workbook_Name As Variant

workbook_Name = Application.GetSaveAsFilename

If workbook_Name <> False Then
If Right(workbook_Name, 5) <> ".xlsm" Then workbook_Name = workbook_Name & ".xlsm"
   ActiveWorkbook.SaveAs workbook_Name, 52
End If

End Sub