XML-Verarbeitung mit Excel

Heute stand ich vor der Aufgabe, aus einer XML-Datei mit Excel (bzw. VBA) gezielt den Inhalt eines bestimmten XML-Elements auszulesen. Am einfachsten macht man sowas natürlich mit XPath, was aber von Excel per se nicht angeboten wird. Mit Excel ist zunächt einmal nur die Verarbeitung von XML im Zusammenhang mit XML-Datenquellen in Form von XML-Schemas möglich. Diese Datenquellen können auch recht einfach angelegt und die entsprechenden Daten im- und exportiert werden: ActiveWorkbook.XmlMaps("Name_der_Zuordnung")

Aber wie gesagt, wollte ich aus einer beliebigen XML-Datei bzw. einem XML-String mittels XPath Elemente auslesen. Hierzu ist zunächst ein Verweis auf die Microsoft XML-Bibliothek zu setzen, die die benötigten Funktionen bereitstellt. Hierzu einfach im VBA-Editor unter Extras -> Verweise einen Verweis auf Microsoft XML, v6.0 (oder welche Version sonst verfügbar/gewünscht ist) setzen.

Danach können XML-Daten wie folgt verarbeitet werden (in meinem Beispiel lese ich den XML-String des Schemas der verknüpften Datenquelle aus):

  1. Sub GetXMLElementExample()
  2.   Dim doc As MSXML2.DOMDocument
  3.   Dim xmlLoaded As Boolean
  4.   Dim kws As IXMLDOMNodeList
  5.   Dim kw As IXMLDOMNode
  6.   Dim kwIndex As Integer
  7.  
  8.   Set doc = New DOMDocument
  9.   xmlLoaded = doc.loadXML(ActiveWorkbook.XmlMaps("XML_Source").Schemas.Item(0).XML)
  10.   If Not xmlLoaded Then
  11.     ‚ Fehlerbehandlung
  12.    Exit Sub
  13.   End If
  14.  
  15.   Set kws = doc.selectNodes("/xsd:schema/xsd:simpleType[@name=’KWBezeichner‘]/xsd:restriction/xsd:enumeration/@value")
  16.   For kwIndex = 0 To kws.Length – 1
  17.     Range("A" & (kwIndex + 1)).Value = kws.Item(kwIndex).Text
  18.   Next kwIndex
  19. End Sub

Über uns Stefan

Polyglot Clean Code Developer

15 Kommentare

  1. Schöne guten Tag,

    deine Seite hat mir einen guten Anstoss gegeben, da ich die XSD-Elemente für eine Liste mit Möglichkeiten zum ausfüllen von XML-Daten gebraucht habe. Nun mein anderes Problem…ich möchte Daten per Makro/VBA hinzufügen und/oder löschen. Habt ihr da einen Tipp für mich?

  2. Du möchtest per Makro Werte in eine Excel-Datei einfügen und dann als XML-Datei exportieren!? Das geht natürlich. Die Frage ist, ob du nicht besser gleich per VBA XML erstellst, anstatt den Umweg über Excel zu gehen.

    Solltest du ersteren Weg bevorzugen, kannst du die Werte ganz normal in die Excel-Zellen schreiben, die mit der XML-Quelle verknüpft sind, und dann über ActiveWorkbook.XmlMaps("XML_Source").Save (oder so, weiß ich gerade nicht genau) exportieren…

  3. Also ich habe eine Schemadatei (xsd) und ich lese die Values der Möglichen Werte für die Elemente in Excel per Makro ein. Nun möchte ich die wieder in die Schemadatei schreiben nach Änderung. Dieses funktioniert auch wunderbar, wenn ich nur die Attribute ändere, aber bei hinzukommen eines weiteren Elementes muss ich ja ein weiteres Element in der Schemadatei hinzufügen und da hänge ich gerade fest. Muss ja auch sagen, dass ich nicht wirklich vertraut bin mit Makros in Excel.

  4. Mhh… prinzipiell sollte ein Schema sich ja nicht allzu häufig ändern, aber gut. Wo ist denn jetzt genau dein Problem? Wie liest du die Werte aus und schreibst sie in das Schema?

  5. Mit dem Schema hast du Recht, es sollte sich nicht so häufig ändern.
    Also ich lese sie aus, um eine Art DropDown der möglichen Werte zu erstellen. Diese wird dann in einem anderne Tabellenblatt benutzt zum verändern und ausfüllen der XML Daten. Ich müsste per Makro nun weitere ChildNodes in der Schemadatei(XSD) für das jeweilige Element hinzufügen und da hackt es im Moment.

  6. So in der Art sollte es doch gehen, oder nicht?

    Set test = doc.CreateElement("test")
    test.Text = "das ist ein test"
    doc.AppendChild(test)
  7. So komme ich an die ChildElemente ran und ich versuche den parentNode zu erreichen. Vielleicht habe ich einfach auch eine nicht so gute Methode gewählt, um an die Elemente zu kommmen.

    elementNode = "/xs:schema/xs:element[@name='" & elementName & "']/xs:simpleType/xs:restriction/xs:enumeration/@value"
    Set xNodeList = MSXML.selectNodes(elementNode)
    

    Hierdurch kann ich wunderbar die für mich wichtigen Elemente rauslesen und auch ändern.

  8. Du musst als Parent-Element aus deinem obigen Pfad xs:restriction auswählen und dann wie folgt dein neues Element hinzufügen:

    elementNode = "/xs:schema/xs:element[@name='" & elementName & "']/xs:simpleType/xs:restriction"
    Set xNodeList = MSXML.selectNodes(elementNode)
    Set neu = MSXML.CreateElement("xsd:enumeration")
    neu.SetAttribute("value", "DerWert")
    xNodeList.AppendChild(neu)
  9. bekomme ne Fehlermeldung (Syntaxfehler bei neu.SetAttribute), aber ich werde diesen Ansatz weiter verfolgen.

  10. Habe es nun nach mehrmaligen probieren…try and error sozusagen zu einem Ergebnis gekommen

    Dim neu As IXMLDOMNode
    Dim xmlAttr As IXMLDOMAttribute
    Dim xNode As IXMLDOMNode
    elementNode = "/xs:schema/xs:element[@name='" & elementName & "']/xs:simpleType/xs:restriction"
    
    Set xNode = MSXML.selectSingleNode(elementNode)
    Set neu = MSXML.createElement("xs:enumeration")
    
    Set xmlAttr = MSXML.createAttribute("value")
    xmlAttr.value = "Wert in Excel Tabelle"
    
    neu.Attributes.setNamedItem xmlAttr
    xNode.appendChild neu
    

    Jetzt muss ich das ganze noch in eien Schleife packen und dann müsste es eigentlich so hinhauen. Danke nochmal für die Hilfe!

  11. ich habe auch eine Frage, die lautet:
    wie ändert sich das xsd Programm,wenn ich so xml(2) Programm bekommen möchte,zumBeispiel:
    xml(2):

    ABC
    BAC
    CBA</String<

    soweit das xml(1) Programm ich bekommen habe,ZumBeispiel:
    xml(1):

    ABC

    BAC

    CBA

    das wäre Programm,das durch die xml-Funktion von Excel2003 erstellt wurde, ich meine, wie kriege ich das xml(2),das system hat so gezeigt,dass die Excel den Form von xml(2) nicht akzeptiert, was kann man dafür machen oder änderen, damit man das Form wie xml(2) kriegt…

  12. sry! nochmal,
    xml(1):
    //
    //
    // ABC
    // BAC
    // CBA
    //
    //
    xml(2):
    //
    //
    // ABC
    //
    //
    // BAC
    //
    //
    // CBA
    //
    //

  13. @kevin: Sorry, ich habe keine Ahnung, was du meinst. Könntest du dich ein wenig klarer ausdrücken?

  14. Hallo Stefan,

    kann man mit deinem Code auch ein Xpath Element von einer Webseite auslesen?

  15. Hallo Tobias, grundsätzlich sollte das gehen. Du musst halt nur die XML-Daten in Excel reinbekommen. Aber dafür gibt es bestimmt Mittel in VBA. Viele Grüße! Stefan

Schreibe einen Kommentar

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax