XML Datei per VBA erzeugen

In diesem Beispiel zeigen wir Ihnen, wie XML Dateien per VBA erzeugt werden können.

Als Ausgangsdatei dient eine kleine Adressliste, die als XML-Datei abgespeichert werden soll, siehe Abbildung.

Abb: Adressliste

Der VBA-Code soll nun ab Zeile 6 die Einträge Vorname, Nachname und Ort mit den entsprechenden Tags in eine XML-Datei schreiben. Die XML-Datei soll wie folgt aufgebaut sein:

 

Erfassen Sie dazu in einem Code-Modul den folgenden VBA-Code:

Sub XML_Export()
'*****************************************
'** Excel-Inside Solutions - (C)                                *
'*****************************************
'** Dimensionierung der Variablen
Dim strFile As String, Text As String
Dim lngRow, lngCol As Long
Dim varShow
'** Errorhandling
On Error GoTo Fehlermeldung
'** XML-Dateipfad und -Name festlegen
strFile = ThisWorkbook.Path & "\test.xml"
'** Datei (ASCII) öffnen
Open strFile For Output As #1
'** XML-Header schreiben
Print #1, "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?> "
Print #1, "<daten xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"
'** Mit Schleife die ersten 3 Spalten der Tabelle schreiben
For lngRow = 6 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'** Schreiben Datensatz-Beginn
 Print #1, "<Adresse ID=""" & lngRow - 5 & """>" 'Tag Anfang
 
 '** Schreiben der Felder (Spalten A-C)
 Print #1, "<Vorname>" & Cells(lngRow, 1) & "</Vorname>"
 Print #1, "<Nachname>" & Cells(lngRow, 2) & "</Nachname>"
 Print #1, "<Ort>" & Cells(lngRow, 3) & "</Ort>"
 
 '** Schreiben Datensatz-Ende
 Print #1, "</Adresse>"
Next lngRow
'** Daten-Tag schließen
Print #1, "</daten>"
'** XML-Datei schließen
Close #1
'** Aufruf des Editors mit der geschriebenen xml-Datei
varShow = Shell(Environ("windir") & "\notepad.exe " & strFile, 1)
Exit Sub
'** Errorhandling
Fehlermeldung:
Close #1
 MsgBox "Fehler-Nr.: " & Err.Number & vbNewLine & vbNewLine _
 & "Beschreibung: " & Err.Description _
 , vbCritical, "Fehler"
End Sub

Die XML-Daten-Struktur wird mit diesem VBA-Code erzeugt und in der Datei "test.xml" im gleichen Verzeichnis abgelegt, in der sich die Excel-Datei befindet.

Das Ergebnis sieht wie folgt aus:

 
Abb: XML-Datenstruktur

Hinweis:

Dieser Beispielcode wandelt Umlautet nicht nicht in einen XML-Konformen String um. Dies muss zusätzlich implementiert werden.

Die Beispieldatei kann über den folgenden Link heruntergeladen werden.

Import von XML-Dateien

Nun haben Sie erfahren, wie sie per VBA ganz einfach XML Dateien exportieren können. Um diese erzeugen XML-Dateien wieder zu importieren, also einzulesen gehen Sie wie folgt vor:

1. Öffnen Sie den XML-Importbefehl über das Menü Daten | Verbindungen | Externe Daten abrufen | Aus anderen Quellen | Vom XML-Datenimport
2. Öffnen Sie in dem Import "Datenquelle auswählen" unsere exportierte Datei Text.xml oder jede andere beliebige XML-konforme Datei
3. Nach einem Klick auf die Schaltfläche Öffnen wird die XML-Datei importiert und in eine Excel-Tabelle eingelesen, siehe Abbildung.

Abb. importierte XML-Datei

Wie Sie sehen, verfügt Excel über eine komfortable XML-Importfunktion. Diese prüft beim Importvorgang die XML-Datei und gibt entsprechende Fehlermeldungen aus, falls die zu importierende Datei nicht konsistent ist.