Thursday, 6 December 2012

File Handling

In Microsoft Excel, I'd like to create a "Save As" macro that I can assign to a button. This macro should let me save my Excel spreadsheet while allowing me to select the file name.

How can I do this?

Answer: The following macro will perform a "File Save As" and then display a message box stating where the file was saved to.

Macro Code

The macro code looks like this:

Sub mcrSave()

'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

MsgBox "Save as " & fileSaveName
End If

End Sub

No comments:

Post a Comment