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:

  1. Option Explicit
  2. Sub ChooseFolder()
  3. Dim dlgSaveFolder As FileDialog
  4. Dim sFolderPathForSave As String
  5. '''
  6. 'Open a Folder picker dialog box.
  7. Set dlgSaveFolder = Application.FileDialog(msoFileDialogFolderPicker)
  8. With dlgSaveFolder
  9. .Title = "Select a Folder"
  10. .AllowMultiSelect = False
  11. .InitialFileName = ThisWorkBook.Path & "\"
  12. If .Show <> -1 Then GoTo CancelFolderSelection
  13. sFolderPathForSave = .SelectedItems(1)
  14. End With
  15. Set dlgSaveFolder = Nothing
  16. '''
  17. 'File saving code goes here.
  18. '''
  19. CancelFolderSelection:
  20. 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.

excel vba user to select folder to save file


You can find similar Excel Questions and Answer hereunder

1) How can worksheet functions be accessed in VBA?

2) How can I get row count of filtered data?

3) Vba length of an array in Excel

4) Excel 2010 vba replacement of application filesearch in Excel

5) How can I export a WorkSheet as a PDF using VBA?

6) How can I extract file name from a full path including folder path and file name?

7) How can I update a listbox based on data in a list using VBA?

8) Vba code to password protect workbook in Excel

9) How can I set Page orientation, Zoom % , Title Rows and footer using VBA?

10) How can I find the last used cell in a Column in VBA?

11) Vba to return week numbers in Excel

12) How do I add a shape using VBA?

13) How do I update my DropDown list whenever the sheet is activated?

14) Line break in vba message box in Excel

15) How can I set up ListBox using VBA to allow users to select multiple values?