VBA: Speicherpfad vor dem Speichern abfragen

VBA kann in der Microsoft Office-Welt schon ein großer Helfer sein, um alltägliche Aufgaben zu automatisieren, so kann man zum Beispiel Dateianhänge aus Outlook-Emails automatisch speichern oder den Inhalt einer PDF auslesen und in eine Excel-Datei einfügen. Für solche Automatismen kann es hilfreich sein, vorher den Speicherpfad, als das Verzeichnis, wo die Daten oder Dateien gespeichert werden sollen, über einen Dialog abzufragen.

Public Sub SaveWithDialog()
    Dim folderToSave As String
    folderToSave = SaveToFolder
    'hier folgen dann die weiteren Anweisungen, um den Speicherpfad zu nutzen
    Debug.Print folderToSave
End Sub

Die hier gezeigten 3-4 Code-Zeilen sind unsere Grundfunktion, die man dann über als Makro ausführen kann. Wir definieren hier eine String-Variable folderToSave, an welche dann der Verzeichnispfad übergeben wird. Mit Debug.Print geben wir den über einen Auswahl-Dialog ermittelten Speicherpfad im Direktfenster aus.

Die Abfrage des Speicherpfads mittels eines „Speichern unter“-Dialog haben wird hier in einer separaten Funktion ausgelagert. Das Problem ist, dass es bei VBA direkt gar nicht solche Dialog-Funktionen gibt bzw. nicht in allen Programmen, wie Outlook zur Verfügung steht. Stattdessen muss man sich alternativer Workarounds bedienen. Dafür gibt es zwei Varianten.

Die Funktion, welche die Speicherpfad-Abfrage beim User handhabt, heißt hier in unserem Beispiel SaveToFolder, ist parameterlos und gibt den vom Benutzer ausgewählten Verzeichnispfad zurück.

Variante 1: „Ordner suchen“ – Dialog für Speicherpfad

Man kann den „Ordner suchen„-Dialog verwenden. Wie dieser aussieht, sieht man im folgenden Screenshot.

Bei dem Ordner-Suchen-Dialog wird eine Shell.Application genutzt, wie man anhand des nachfolgenden Codes erkennen kann.

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

Diese Variante funktioniert unter allen Office-Anwendungen, da die Shell-Applikation Windows-weit verfügbar ist.

Variante 2: „Speichern unter“ – Dialog verwenden

Die zweite Variante zeigt den klassischen „Speichern unter“-Dialog, wie man ihn unter Windows gewöhnt ist. Leider ist dieser mit VBA nicht direkt in allen Anwendungen verfügbar. Zum Beispiel kann man mit VBA in Outlook nicht direkt einen solchen Dialog aufrufen, um einen Ordner als Speicherpfad festzulegen.

Jedoch ist dieser File-Dialog auf jeden Fall für Excel vorhanden. Dadurch hat man auch in anderen Office-Programmen die Möglichkeit den File-Dialog aufzurufen, indem man einfach ein Excel-Application-Objekt im VBA definiert. Wie das geht, zeigt der nachfolgende VBA-Code.

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 'wenn Benutzer Okay-Button drückt
            'Durchlaufen Sie jede Zeichenkette in der Sammlung FileDialogSelectedItems.
            For Each SelectedItem In .SelectedItems
                SaveToFolder = SelectedItem
            Next SelectedItem
        Else
            'wenn Benutzer den Cancel-Button drückt
            SaveToFolder = ""
        End If
    End With
    
    Set fileDlg = Nothing
    excelApp.Quit
    Set excelApp = Nothing
End Function

Mit einer dieser beiden Varianten bieten man also den Anwender die Möglichkeit dediziert ein Verzeichnis als Speicherpfad auszuwählen, um diesen dann im VBA-Makro weiterzuverwenden.