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.