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
- Above code is suitable for cases when you open a file, make changes and then want to save it somewhere.
- 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
1 comment on “Save a excel file at a fixed path using VBA excel”
Comments are closed.