Save a excel file at a fixed path using VBA excel

Objective

To save a excel file at a fixed path using VBA excel.

Approach

Below code opens a excel file, renames it and saves it. Opened file is saved at the same location where macro file which we are using is located.

Code

Sub SaveExcelFile()
Dim MyTargetFile As Variant
'You can change *.xlsx to *.csv or any other format
MyTargetFile = Application.GetOpenFilename("Excel Files (*.xlsx), *xlsx")
'First open a excel file
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
'Save the opened excel file by renaming it , here MyNewName_ is added as prefix.
Workbooks(MyOpenedFile).SaveAs Filename:=ThisWorkbook.Path & "\" & "MyNewName_" & MyOpenedFile
'Close the saved workbook
ActiveWorkbook.Close
End Sub

Notes

1. Above code is suitable for cases when you open a file, make changes and then want to save it somewhere.
2. Save as path can be modified or a variable for path can also be provided.

Post you may like

Save a excel file by a dialogue box using VBA excel