Vba user to select folder to save file in Excel

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 I turn off Alerts using VBA?

2) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

3) How to print a worksheet in Excel VBA

4) How do I restrict user entry in ActiveX Text Box to just numeric values?

5) How can I change the Marker size and Marker line color for all the series in a chart?

6) How to select based on multiple choices in VBA with the select case function

7) How can I get users to select a file for processing using my macro?

8) How can I delete all shapes in a WorkSheet?

9) How can I set the Source Data of charts using VBA?

10) How can I add a legend to a chart using VBA?


Here the previous and next chapter