excel - How to browse for save directory?

Question

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

Answer 1

Excel has an inbuilt FileSave Dialog. It is called .GetSaveAsFilename. Use that.

Syntax

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Usage

Dim fileSaveName As Variant

fileSaveName = Application.GetSaveAsFilename( _
                                    fileFilter:="Excel Files (*.csv), *.csv")
If fileSaveName <> False Then
    '
    '~~> Your code to save the file here
    '
End If