vba user to select folder to save file in Excel
Q150. In Excel, how can I get users to select a folder to save the output of my macro?
For example, my macro takes a file that is selected by user as input, processes the same and provides output file. In Excel, how can I get users to select a folder to save the output file?
To do it in Excel, here is the answer:
- Option Explicit
- Sub ChooseFolder()
- Dim dlgSaveFolder As FileDialog
- Dim sFolderPathForSave As String
- 'Open a Folder picker dialog box.
- Set dlgSaveFolder = Application.FileDialog(msoFileDialogFolderPicker)
- With dlgSaveFolder
- .Title = "Select a Folder"
- .AllowMultiSelect = False
- .InitialFileName = ThisWorkBook.Path & "\"
- If .Show <> -1 Then GoTo CancelFolderSelection
- sFolderPathForSave = .SelectedItems(1)
- End With
- Set dlgSaveFolder = Nothing
- 'File saving code goes here.
- End Sub
a) Line 7 brings up a dialog box as shown below. User can then select a folder and click "OK". The selected folder is then stored in a variable in Line 13 and the variable can subsequently be used to save file.