VBA: Query save path before (Save as dialog)

VBA can be a great helper in the Microsoft Office world for automating everyday tasks, for example automatically saving file attachments from Outlook emails or reading the contents of a PDF and inserting them into an Excel file. For such automatisms, it can be helpful to query the storage path, i.e. the directory where the data or files are to be saved, via a dialogue beforehand.

Public Sub SaveWithDialog()
    Dim folderToSave As String
    folderToSave = SaveToFolder
    'Here are the further instructions for using the save as path
    Debug.Print folderToSave
End Sub

The 3-4 lines of code shown here are our basic function, which can then be executed as a macro. Here we define a string variable folderToSave, to which the directory path is then passed. With Debug.Print we output the memory path determined via a selection dialogue in the direct window.

The query of the memory path using a “Save as” dialogue is outsourced here in a separate function. The problem is that such dialogue functions do not exist directly in VBA or are not available in all programs, such as Outlook. Instead, you have to use alternative workarounds. There are two variants for this.

The function that handles the user’s save path query is called SaveToFolder in our example, is parameterless and returns the directory path selected by the user.

Variant 1: “Find folder” – dialogue for save path

You can use the “Find folder” dialogue. You can see what this dialogue looks like in the following screenshot.

The folder search dialogue uses a Shell.Application, as you can see from the following code.

Function SaveToFolder(Optional OpenAt As String) As String
    Dim ShellApp As Object
    
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Select Folder", 0, OpenAt)
    
    On Error Resume Next
    SaveToFolder = ShellApp.self.Path
    On Error GoTo 0
    
    Select Case Mid(SaveToFolder, 2, 1)
    Case Is = ":"
        If Left(SaveToFolder, 1) = ":" Then
            SaveToFolder = ""
        End If
    Case Is = "\"
        If Not Left(SaveToFolder, 1) = "\" Then
            SaveToFolder = ""
        End If
    Case Else
        SaveToFolder = ""
    End Select
    
ExitFunction:    
    Set ShellApp = Nothing
End Function

This variant works under all Office applications, as the shell application is available Windows-wide.

Variant 2: “Save as” – use dialogue

The second variant shows the classic “Save as” dialogue as you are used to under Windows. Unfortunately, this is not directly available in all applications with VBA. For example, you cannot use VBA in Outlook to directly call up such a dialogue to specify a folder as the save path.

However, this file dialogue is always available for Excel. This means that you can also call up the file dialogue in other Office programs by simply defining an Excel application object in VBA. The following VBA code shows how to do this.

Function SaveToFolder() As String
    Dim fileDlg As fileDialog
    Set excelApp = CreateObject("Excel.application")
    excelApp.Visible = False
    Set fileDlg = excelApp.fileDialog(msoFileDialogFolderPicker)

    With fileDlg
        If .Show = -1 Then 'when user presses Okay button
            'Loop through each string in the FileDialogSelectedItems collection.
            For Each SelectedItem In .SelectedItems
                SaveToFolder = SelectedItem
            Next SelectedItem
        Else
            'when user presses the cancel button
            SaveToFolder = ""
        End If
    End With
    
    Set fileDlg = Nothing
    excelApp.Quit
    Set excelApp = Nothing
End Function

With one of these two variants, you offer the user the option of selecting a dedicated directory as the storage path in order to then use it in the VBA macro.