Prompt to select a folder to save file in VBA Excel – debugvba.com

select or choose folder to save vba excel

Objective

Prompt to select a folder to save file in VBA Excel

Approach

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.

Steps

  1. Open the dialog box to select a file
  2. Select a file and click ok.
  3. Open the dialog box to select a folder location, where opened file will be saved.
  4. Click ok to save the file
  5. File will be saved in the selected folder.


Code

 

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 .

 

Chose folder to save vba excel

 

Now a message box will be shown which will ask us to select the output folder.

 

select or choose folder to save vba excel

 

We shall select the folder where we want to save the file. See the screenshot below.

 

select or choose folder to save vba excel

 

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.

select or choose folder to save vba excel


Note

We can apply the similar approach in any scenario where we want to save a file at any folder selected using dialog box.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.filedialog

Post you may like

Import multiple text files in separate excel files using vba excel – debugvba.com