Online-Formular Daten in Excel importieren

Hat man auf seiner Internetseite ein Online-Formular platziert, zum Beipiel eine Terminanfrage oder ein Formular für eine Restaurantreservierung, damit die Webseitenbesucher darüber mit dem Webseitenbetreiber in Kontakt treten können, dann bekommt man die abgesendeten Daten per E-Mail zugeschickt. Bei einem einfachen Kontaktformular kann das ausreichend sein. Bei anderen Formularen, wie bei einer Umfrage, möchte man die Daten meistens weiter verarbeiten und auswerten. In diesem Fall kann es recht mühsam werden alle Daten per Hand in eine Tabellenkalkulation, wie Excel, oder in eine Datenbank, wie Access, zu kopieren.

Wer Microsoft Office nutzt, der kann die Vorteile von VBA und Makros nutzen und direkt mit einem Klick die Formulardaten in eine Excel-Tabelle importieren.

Videoanleitung

Ausgangsituation

Voraussetzung ist die Verwendung von Microsoft Outlook und Microsoft Excel zum Verarbeiten der Formulardaten. Getestet wurde der Export und Import der Formulardaten von Outlook nach Excel mit Office 365. Sollte aber auch in anderen Office-Versionen problemlos funktionieren.

In dem hier gezeigten Beispiel handelt es sich um ein Onlineformular, welches mit dem Formular-Generator DA-FormMaker erzeugt worden ist. Das Formular mit seinen einzelnen Feldern ist im nachfolgenden Bild zu sehen.

Screenshot Beispiel Online Formular
Beispiel Online Formular

Klickt der Benutzer auf den Absenden-Button, dann wird über das Formmail-Script eine E-Mail erzeugt. Zusätzlich ist das Formular so zu konfigurieren, dass mit dieser E-Mail eine Excel-Datei versendet wird, welche die Formulardaten als Anhang enhält. Dazu muss man im DA-FormMaker über den Reiter „Einstellungen“ ➤ „Erweiterte Einstellungen“ ➤ „Einstellungen“ ➤ „Hinzufügen von Formularinhalten als Excel-Dokument“ anklicken.

Screenshot Einstellungen DA-FormMaker
Checkbox für den Excel-Versand der Formulardaten anklicken

Wird dieses Beispielformular abgesendet, dann erhält man eine E-Mail inkl. Excel-Anhang, welcher die Daten enthält. Im Microsoft Outlook sieht das dann wie folgt aus:

Screenshot Microsoft Outlook
E-Mail mit Formulardaten im Excel-Anhang

Der Inhalt des Excel-Anhang sieht dann wie folgt aus:

Screenshot Inhalt Excel-Datei
Formulardaten in der Excel-Datei

Mit so einer Excel-Datei lässt es sich schon viel leichter arbeiten und man könnte die einzelne Zeile in eine andere Excel-Datei reinkopieren, in welcher alle Daten gesammelt und ausgewertet werden sollen. Aber Ziel ist es die Daten mit einem Klick in eine Excel-Tabelle zu übernehmen. In unserem Beispiel haben wir eine Excel-Datei namens „online-form-data.xlsx“ in welcher die von allen Reservierungsanfragen gesammelt werden sollen.

Screenshot Excel Import Daten
Formulardaten sollen hier eingefügt werden

Der neue Eintrag soll direkt hinter dem letzten Eintrag in der nächsten freien Zeile eingefügt werden.

VBA-Makro Code zum Kopieren der Daten von Outlook nach Excel

Visual Basic Editor aufrufen

Um den VBA-Code einzufügen, muss man den Visual Basic-Editor über die Entwicklertools aufrufen oder über die Tastenkombination „Alt+F11„. Standardmäßig sind die Entwicklertools ausgeblendet. Dann muss man diese über „Datei“ ➤ „Optionen“ ➤ „Menüband anpassen“ einblenden.

Outlook Screenshot Entwicklertools
VBA-Editor über Entwicklertools aufrufen

Separates Modul anlegen

Im Visual Basic-Editor kann man dann noch ein neues Modul hinzufügen, um den VBA-Code von ggf. anderen vorhandenen Makros zu trennen. In dem Beispiel wurde das neue Modul FormDataImport genannt.

Screenshot Visual Basic Editor neues Modul einfügen
Neues Modul anlegen

Verweise festlegen

Damit das Makro funktionioniert müssen die folgenden Libraries eingebunden werden:

  • Microsoft Excel 16.0 Object Library
  • Microsoft Outlook 16.0 Object Library
  • Microsoft Scripting Runtime

Dafür geht man im VBA-Editor auf „Extras“ ➤ „Verweise“.

Screenshot VBA-Editor Verweise aufrufen
Dialog zum Einbinden der Libraries aufrufen

Und dann im folgenden Dialog die entsprechenden Bibliotheken in das Projekt einbinden.

VBA Screenshot Verweise einbinden
Libraries einbinden

VBA-Code einfügen

Der nachfolgenden VBA-Code wird dann in das neue Modul eingefügt und muss noch entsprechend den eigenen Bedürfnissen angepasst werden.

Public Sub ExportFromExelAttachmentToExcelFile()
    Dim objOL As Outlook.Application
    Dim objMsg As Outlook.MailItem
    Dim objAttachments As Outlook.Attachments
    Dim objSelection As Outlook.Selection
    Dim excelOnHardDisk As String
    Dim i As Long
    Dim lngCount As Long
    Dim excelWorkbookMail As Workbook
    Dim excelWorkbookHD As Workbook
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim dict As Dictionary
    Dim varKey As Variant
    Dim unusedRow As Long

    'Pfad zur Excel-Datei in die importiert werden soll
    excelOnHardDisk = "c:\Users\DeinUser\Documents\temp\online-form-data.xlsx"
    'Name des Tabellenblatt in das importiert werden soll
    Sheet = "Reservierung"
    'Spalten zuordnen; erster Werte Excel-Spalte des E-Mail-Anhangs aus der exportiert werden soll, zweiter Werte Excel-Spalte in die importiert werden soll
    Set dict = New Dictionary
    dict.Add "A", "A"
    dict.Add "B", "B"
    dict.Add "C", "C"
    dict.Add "D", "D"
    dict.Add "E", "E"
    dict.Add "F", "F"
    dict.Add "G", "G"
    dict.Add "H", "H"
    dict.Add "I", "I"
    dict.Add "J", "J"
    dict.Add "K", "K"
    
    ' Outlook Application Objekt
    Set objOL = CreateObject("Outlook.Application")
    ' Collection der ausgewählten Objekte (E-Mails) ermitteln
    Set objSelection = objOL.ActiveExplorer.Selection
    'Erste ausgewählte E-Mail benutzen
    Set objMsg = objSelection.Item(1)
    ' Die Anhänge des ausgewählten Objekts (E-Mail) ermitteln
    Set objAttachments = objMsg.Attachments
    lngCount = objAttachments.Count

    If lngCount > 0 Then
        For i = lngCount To 1 Step -1
            ' Dateinamen ermitteln
            strFile = objAttachments.Item(i).FileName            
            'prüfen, ob es eine Excel-Datei ist
            FileExtension = LCase(Right$(strFile, Len(strFile) - InStrRev(strFile, ".")))
            If FileExtension = "xls" Or FileExtension = "xlsx" Then
                tempFolderPath = Environ("Temp") + "\" + strFile
                ' Excel-Datei temporär als Datei speichern
                objAttachments.Item(i).SaveAsFile tempFolderPath
                Set excelWorkbookMail = Workbooks.Open(tempFolderPath)
                Set ws2 = excelWorkbookMail.Sheets(1)
                Set excelWorkbookHD = Workbooks.Open(excelOnHardDisk)
                
                Set ws = excelWorkbookHD.Worksheets(Sheet)
                'erste freie Zeile in Tabellenblatt ermitteln
                unusedRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
                
                For Each varKey In dict.Keys()
                    orignRange = varKey & "2"
                    targetRange = dict.Item(varKey) & unusedRow
                    ws.Range(targetRange) = ws2.Range(orignRange)
                Next
                
                Response = MsgBox("Data were exported successfully", vbOKOnly, "Success")
            End If
        Next i
    End If
    
ExitSub:
    Set dict = Nothing
    Set objAttachments = Nothing
    Set objMsg = Nothing
    Set objSelection = Nothing
    Set objOL = Nothing
    Set ws2 = Nothing
    excelWorkbookMail.Close (False)
    Set excelWorkbookMail = Nothing
    Set ws = Nothing
    excelWorkbookHD.Close (True)
    Set excelWorkbookHD = Nothing
End Sub

VBA-Code konfigurieren

In den folgenden Codezeilen muss noch der Code noch auf die eigenen Anforderungen angepasst werden.

Screenshot VBA-Code
VBA-Code anpassen und konfigurieren

Der Variable excelOnHardDisk muss der vollständige Pfad zur Excel-Datei zugewiesen werden, in welche die Daten importiert werden sollen. Der Variablen Sheet muss der Name des Tabellenblatt zugewiesen werden, in das die Daten eingefügt werden sollen. Über das Dictionary dict macht man die Zuweisung der Excel-Spalten. Der erste Wert/Buchstabe ist die Excel-Spalte des E-Mail-Anhangs. Der zweite Wert/Buchstabe ist die Zielspalte, in welche der das jeweilige Formulardatum eingefügt werden soll. Hat das Formular mehr oder weniger Felder, dann fügt man einfach weitere Codezeilen der Form dict.Add „…“, „…“ hinzu oder löscht diese heraus.

Der weitere VBA-Code speichert den E-Mail-Anhang innerhalb des Temp-Ordners, prüft anhand der Dateiendung, ob es sich um eine Excel-Datei handel und liest die einzelnen Formulardaten aus, um diese dann in die nächste freie Zeile des vorgebenen Excel-Tabellenblatts einzufügen.

Wichtiger Hinweis: Wenn es beim Debuggen zu Fehlern kommt und dadurch excelWorkbookMail.Close oder excelWorkbookHD.Close nicht mehr ausgeführt werden, dann kommt es danach zu weiteren Fehlermeldungen, weil auf die Dateien nicht mehr zugegriffen werden kann, weil diese noch geöffnet sind. In diesem Fall Excel einmal über den TaskManager beenden.

Makro Formulardaten-Import ausführen

Ist alles fertig eingerichten, dann kann man das VBA-Makros über die „Entwicklertools“ ➤ „Makros“ ausführen. Noch bequemer ist es, wenn man sich einen Schnellzugriff einrichtet. Das geht über „Datei“ ➤ „Optionen“ ➤ „Symbolleiste für den Schnellzugriff„.

Screenshot VBA-Makro ausführen
Makro für den Export der Formulardaten starten

In dem hier verwendeten Beispiel sieht das Ergebniss in Excel dann wie folgt aus:

Screenshot neuer Import in Exceldatei
Neu eingefügte Formulardaten zur weiteren Bearbeitung und Auswertung

Wichtiger Hinweis: Während das Makro ausgeführt wird muss die Excel-Datei in welche die Formulardaten importiert werden, geschlossen sein. Sonst kommte es zu einem Zugriffsfehler, weil dann zwei verschiedene Instanzen gleichzeitig die Datei beschreiben wollen.

Kommentar hinterlassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.