Prompt to select a folder to save file in VBA Excel
In this VBA Excel automation, our objective is to showcase the VBA functionality, where a user has the option to choose the target folder, where a file will be saved.To demonstrate this functionality, we shall open a excel file from a location using dialog box, and then we shall save that file using the dialog box. We shall not make any changes in the opened file as we only intend to showcase how to save the file by dialog box.
- Open the dialog box to select a file
- Select a file and click ok.
- Open the dialog box to select a folder location, where opened file will be saved.
- Click ok to save the file
- File will be saved in the selected folder.
Option Explicit Sub SaveExcelFilePrompt() Application.DisplayAlerts = False Dim OpenExcelDialogBox As Object Dim SaveDialogBox As Object Dim MySelectedFile As String Dim OutputFolder As String 'Select the source excel file Set OpenExcelDialogBox = Application.FileDialog(msoFileDialogFilePicker) If OpenExcelDialogBox.Show = -1 Then MySelectedFile = OpenExcelDialogBox.SelectedItems(1) End If Workbooks.Open (MySelectedFile) 'Bring the macro file in front Windows(ThisWorkbook.Name).Visible = True AppActivate Application.Caption 'Select output folder where output files will be saved Set SaveDialogBox = Application.FileDialog(msoFileDialogFolderPicker) MsgBox ("Select a TARGET folder where opened file will be saved") If SaveDialogBox.Show = -1 Then OutputFolder = SaveDialogBox.SelectedItems(1) End If 'Saving this select file in output folder , we have added string "Modified_" in the file name ActiveWorkbook.SaveAs Filename:=OutputFolder & "\" & "Modified_" & ActiveWorkbook.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.Close End Sub
When we execute the VBA code ,first a dialog box will be displayed, which will ask to select the source excel file .
Now a message box will be shown which will ask us to select the output folder.
We shall select the folder where we want to save the file. See the screenshot below.
Once we select the target folder, output file will be saved at target folder. We can see that in the image below. We have added a prefix “Modified_” in the saved file. We have not made any changes in the source file.
We can apply the similar approach in any scenario where we want to save a file at any folder selected using dialog box.
Post you may like