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.
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.
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:
Der Inhalt des Excel-Anhang sieht dann wie folgt aus:
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.
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.
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.
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”.
Und dann im folgenden Dialog die entsprechenden Bibliotheken in das Projekt 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 SubVBA-Code konfigurieren
In den folgenden Codezeilen muss noch der Code noch auf die eigenen Anforderungen angepasst werden.
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“.
In dem hier verwendeten Beispiel sieht das Ergebniss in Excel dann wie folgt aus:
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.
2 Kommentare