Excel Made Easy

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:

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.
'''
CancelFolderSelection:
End Sub

Description:

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?

Vba user to select folder to save file in Excel

You can find similar Excel Questions and Answer hereunder

Here the previous and next chapter