To save a excel file by a dialogue box using VBA excel.
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.
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.
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