How to browse for save directory?
csv, excel, vba
Solution
As Patrick suggested, you're looking for the `.FileDialog` property.
To implement it, try this:
Sub Export()
Dim MyPath As String
Dim MyFileName As String
MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
Sheets("Export Data").Copy
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = "" '<~~ The start folder path for the file picker.
If .Show <> -1 Then GoTo NextCode
MyPath = .SelectedItems(1) & "\"
End With
NextCode:
With ActiveWorkbook
.SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV,CreateBackup:=False
.Close False
End With
End Sub
Problem
By clicking a button in Excel, the user exports a specific sheet to a csv with a dynamic filename and the csv is saved in a pre-determined directory. Instead of saving to a predetermined directory, can users have the browse window to choose a directory to save to? ``` Sub Export() Dim MyPath As String Dim MyFileName As String MyPath = "C:\importtest" MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy") If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\" If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv" Sheets("Export Data").Copy With ActiveWorkbook .SaveAs Filename:= _ MyPath & MyFileName, _ FileFormat:=xlCSV, _ CreateBackup:=False .Close False End With End Sub ```