Save a excel file by a dialogue box using VBA excel

save excel file using dialog box vba excel

Objective

To save a excel file by a dialogue box using VBA excel.

Approach

Below code opens a excel file, then a Save As dialogue box will open up, a new file name is needs to be given, then save the file.

Code

Sub SaveExcelFile()
Dim MyTargetFile As Variant
Dim MyNewFileName As Variant
'First open a excel file ,You can change *.xlsx to *.csv or any other format
MyTargetFile = Application.GetOpenFilename("Excel Files (*.xlsx), *xlsx")
Workbooks.Open Filename:=MyTargetFile
'Give a name to the opened excel file
MyOpenedFile = ActiveWorkbook.Name
'In between you can modify the file as you require before saving it
'Dialogue box will appear, give any name to file to save as , then click on Save button, file will be saved.
MyNewFileName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xlsx), *.xlsx")
If MyNewFileName <> False Then
Workbooks(MyOpenedFile).SaveAs Filename:=MyNewFileName
End If
'Close the saved workbook
ActiveWorkbook.Close
End Sub

 

Image shown below depicts what kind of dialogue box will appear.

save excel file using dialog box vba excel

 

 

Notes

1.Above code is customized for cases when you open a file, make changes and then want to save it with a new name at any location.
2.Similar code can be customized for cases where you have a base template and you copy and paste the data from multiple files and want to save the template with new name.
3.You can modify the file extension [*.xlsx] according to your need.

Post you may like

Save a file with a specific name by input box in VBA

 

1 comment on “Save a excel file by a dialogue box using VBA excel

Comments are closed.