24.06.2013

4378

Einzelne Excelzelle in XML einfügen?

Hallo Zusammen,

ich habe eine Excel Tabelle mit einer Datenreihe und möchte diese in einem bestimmten Format in XML einfügen.

Insgesamt sieht meine Tabelle so aus: ID: 4324, 5355, 5435, 6576 usw.

Diese Zahlen werden immer aktualisiert gehalten, deswegen sollte die xml immer wieder gucken, welche ID denn nun in der Zelle steht.

<DoBuy ItemListType="Item" ItemID="72103" Price="0" Amount="0" />

Da wo die 72103 soll nun in 50 gleichen Zeilen jeweils eine der oberen ID´s eingefügt werden. Möglichst automatisch und mit aktualisierung aus der excel, die sich per makro selbst aktualisiert.

Würde mich über Hilfe sehr freuen!

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 208493

Url: https://administrator.de/forum/einzelne-excelzelle-in-xml-einfuegen-208493.html

Ausgedruckt am: 19.04.2025 um 22:04 Uhr

16 Kommentare

Neuester Kommentar

Hallo Jan173,
kein Problem, habe Dir mal ein Beispiel zusammengestellt.
ich gehe mal von folgendem XML-Tempate aus:

<?xml version="1.0" encoding="utf-8"?> 
<root>
   .... hier kommen die <DoBuy> Elemente hin
</root>

Dies ist ein VBS das auch außerhalb von Excel funktioniert. Du musst noch die Pfade in Zeile 1 und 2 an deine Umgebung anpassen. In Zeile 8 musst du noch den Bereich angeben in dem deine IDs stehen und eventuell das Worksheet ändern.

XMLPATH = "C:\demo.xml"  
EXCELPATH = "C:\Mappe1.xlsx"  

Set objExcel = WScript.CreateObject("Excel.Application")  
objExcel.Visible = False
Set wb = objExcel.Workbooks.Open(EXCELPATH)
'Range in dem die ItemIDs stehen  
Set rangeNum = wb.Worksheets(1).Range("A2:A10")  

Set xmlDoc = WScript.CreateObject("Msxml2.DOMDocument")  
xmlDoc.Load XMLPATH
Set oDoc = xmlDoc.documentElement
For Each n In oDoc.childNodes
	oDoc.removeChild(n)
Next
For Each cell In rangeNum.Cells
	Set newnode = xmlDoc.createElement("DoBuy")  
	Set att1 = xmlDoc.createAttribute("ItemListType")  
	Set att2 = xmlDoc.createAttribute("ItemID")  
	Set att3 = xmlDoc.createAttribute("Price")  
	Set att4 = xmlDoc.createAttribute("Amount")  
	att1.nodeValue = "Item"  
	att2.nodeValue = cell.Value
 	att3.nodeValue = cell.Offset(0,1).Value
 	att4.nodeValue = cell.Offset(0,2).Value
	newnode.attributes.setNamedItem(att4)
	newnode.attributes.setNamedItem(att3)
	newnode.attributes.setNamedItem(att2)
	newnode.attributes.setNamedItem(att1)
	oDoc.appendChild(newnode)
Next
xmlDoc.save(XMLPATH)
Set xmlDoc = Nothing
objExcel.Quit

Hinweis: Sollte dein XML-Template Namespaces enthalten, funktioniert obiger Code nicht, hier sind dann noch mehr Anpassungen nötig.

Denke damit solltest du klarkommen. Wenn nicht fragen

Grüße Uwe

Hallo Uwe,

vielen vielen Dank für deine ausführliche Antwort! Ich werde es gleich ausprobieren, ob es klappt.
Eine Sache noch. Wird dein Beispiel von dem ausführenden Programm als die gleiche Struktur wie das
<DoBuy ItemListType="Item" ItemID="72103" Price="0" Amount="0" />

erkannt? Also kann ich das einfach ersetzen und habe im Prinzip das gleiche da stehen?

Vielen Dank
Jan

P.S. Noch etwas. Wenn nun Preis und Amount auch anders sein sollen. Wie definiere ich, welche Zelle für Item Id, für Price und Amount steht?
Und was mir noch auffällt.. Sorry, für die verstreuten Infos.

Jede dieser Zeilen steht in einer eigenen If-Schleife. Das bedeutet, dass ich jeweils nur eine einzige Itemnummer darin stehen hätte.

Zitat von @Jan173:
Eine Sache noch. Wird dein Beispiel von dem ausführenden Programm als die gleiche Struktur wie das
<DoBuy ItemListType="Item" ItemID="72103" Price="0" Amount="0" ></DoBuy>

erkannt? Also kann ich das einfach ersetzen und habe im Prinzip das gleiche da stehen?

Die DoBuy Elemente werden im Code erstellt und nicht erkannt. D.h beim Starten des Scripts werden erst alle DoBuy Elemente innerhalb von root gelöscht und dann mit den IDs in Excel neu erstellt.

P.S. Noch etwas. Wenn nun Preis und Amount auch anders sein sollen. Wie definiere ich, welche Zelle für Item Id, für
Price und Amount steht?

hab ich mir schon gedacht, ich ändere gleich den Code noch dahingehend.

Und was mir noch auffällt.. Sorry, für die verstreuten Infos.
Jede dieser Zeilen steht in einer eigenen If-Schleife. Das bedeutet, dass ich jeweils nur eine einzige Itemnummer darin stehen
hätte.

verstehe nicht was du meinst. Du definierst den Bereich in dem die ItemIDs stehen, und diesen Bereich durchläuft das Script in einer Schleife für jede Zelle. D.h. für jede Zelle entsteht ein XML Element.

obiger Code ist nun für das Aufnehmen von Price und Amount in die XML Datei angepasst.

Deine Liste mit ID, Price, und Amount sollte dann wie folgt aussehen:

ID	Price	Amount
5567	100	10
...	...	...

Hey Uwe,

deine Antworten sind einfach unglaublich hilfreich, vielen Dank für deine Mühe!

Soweit hab ich alles eingebaut, aber wo muss ich denn den Pfad noch ändern, wenn die Tabelle nicht im ersten Sheet ist, sondern erst im 8.? Woher weiß Excel, welches Sheet es öffnen muss?

Warum stehen die newnode.attributes in der umgekehrten Reihenfolge da, hat das einen bestimmten Zweck?

Ich habe den Code jetzt in eine Subroutine kopiert, dabei färbt sich mein Text (in notepad++) lila. Jedoch wird die nächste Subroutineeinleitung (also SubRoutine SubRoutineName=...) auch lila. wie schließe ich die Subroutine denn nun richtig ab?

Vielen Dank und viele Grüße
Jan

Zitat von @Jan173:
Soweit hab ich alles eingebaut, aber wo muss ich denn den Pfad noch ändern, wenn die Tabelle nicht im ersten Sheet ist, sondern erst im 8.? Woher weiß Excel, welches Sheet es öffnen muss?

in dieser Zeile: (Du kannst die 1 mit der Position deines Sheets austauschen oder den Namen verwenden z.B. Worksheets("Tabelle8"). Der Range dahinter gibt ja den Bereich an in dem die IDs in diesem Sheet stehen.

Set rangeNum = wb.Worksheets(1).Range("A2:A10")

Warum stehen die newnode.attributes in der umgekehrten Reihenfolge da, hat das einen bestimmten Zweck?

Das ist nur fürs optische in der XML-Datei (Reihenfolge der Attribute)

Ich habe den Code jetzt in eine Subroutine kopiert, dabei färbt sich mein Text (in notepad++) lila. Jedoch wird die nächste Subroutineeinleitung (also SubRoutine SubRoutineName=...) auch lila. wie schließe ich die Subroutine denn nun richtig ab?

Verstehe ich jetzt nicht ganz.
Eine Subroutine sieht z.B. so aus:

Sub ExportXML()
...
End Sub

Genau:

<SubRoutine SubRoutineName="Buy">
<if>
...
excel import
...
</if>
</SubRoutine>
<SubRoutine SubRoutineName="Store">
...

und in der letzen Zeile sollte eigentlich eine neue SubRoutine anfangen, er färbt sie jedoch auch lila, so wie alle Zeilen innerhalb der oberen Subroutine. Vor dem Einfügen war die nachfolgende Subroutine noch blau...

Außerdem kommt, wenn ich den Code in w3schools validator eingebe ein Fehler in folgender Zeile:

Set objExcel = WScript.CreateObject("Excel.Application")

Fehlerbeschreibung:

Error on line 57 at column 4: Specification mandate value for attribute Set

hää ? jetzt steh ich auf'm Schlauch... für was für ein Programm ist das File worin du arbeitest? Irgendein Anweisungsfile für einen Webshop ?
dir ist schon klar das es sich oben um ein Visual Basic Script handelt und nicht um XML oder ?

Ok, dann nochmal zum Verständnis. Wo muss ich den Code reinkopieren?

Sorry, für die Verwirrung, das Teil ist neu für mich und wahrscheinlich auch etwas zu hoch.
Ja, es geht um ein xml skript, das von einem Online Shop intern gelesen und verarbeitet werden soll. Da sich die Preise aber dauernd ändern, möchte ich die Teile, die gekauft werden vom Preis abhängig machen. Deswegen die Excel Tabelle, in der immer andere Item Nummern stehen.

dazu müsste ich aber erst mal wissen um welches Shopsystem es sich handelt, damit ich weis wie und in welcher Programmiersprache das ganze geschrieben werden muss .... Hellsehen kann ich nicht. Bitte mehr Infos ....sonst kann ich Dir nicht sagen wo das hinkopiert werden muss.

Wir haben keinen Zugriff auf den Shop selbst, ich kann also nichts darin verändern. Ich kann NUR die xml Datei verändern, die ich dann laden kann und die dann verarbeitet wird.

Der Shop selbst arbeitet mit C Sharp

ach so, du möchtest nur die DoBuy Items in das Script einfügen oder ?
Mich hatte verwirrt das du den VBS-Code in die XML-Datei eingefügt hast.
Nein, der Code kommt alleine in eine Textdatei mit der Endung *.vbs
Wenn das oben dein XML-Code ist muss das Script aber noch an die Struktur angepasst werden, dazu brauche ich den kompletten Anfang der XML-Datei bis zur Stelle wo die Items eingefügt werden sollen.

Hab dir ne PM geschickt. Wenn du es hier machen willst, auch i.O.

ohne jetzt mehr vom XML Dokument zu kennen ändere Zeile 12 des Scripts in diese:

Set oDoc = xmlDoc.selectSingleNode("//SubRoutine[@SubRoutineName='Buy']/if")

Diese Angabe ist ohne Gewähr, da ich nicht mehr Info bekomme ...

Als alternative falls die XML-Datei Namespaces enthält, kannst du auch folgenden Code nehmen der nur mit String Methoden arbeitet.
Dazu ist es aber dann nötig das du in das XML-File zwei benutzerdefinierte Kommentarzeilen an der Stelle einbaust an der die DoBuy-Items eingefügt werden sollen:

Das hier fügst du in der XML-Datei an der gewünschten Stelle ein:

<!-- MyGeneratedItems -->

<!-- End MyGeneratedItems -->

Folgenden Code speicherst du dann in einer Datei mit der Endung *.vbs. (Anpassungen bitte wie im ersten Post geschrieben in den Zeilen 1,2,7 vornehmen)

XMLPATH  = "C:\test.xml"  
EXCELPATH = "C:\Mappe1.xlsx"  
Set objFSO = CreateObject("Scripting.FileSystemObject")  
Set objExcel = WScript.CreateObject("Excel.Application")  
objExcel.Visible = False
Set wb = objExcel.Workbooks.Open(EXCELPATH)
Set rangeNum = wb.Worksheets(1).Range("A2:A10")  

Set objText = objFSO.OpenTextFile(XMLPATH, 1)
allContent = objText.ReadAll()
objText.Close()
strSearch = "<!-- MyGeneratedItems -->"  
startPos = InStr(allContent,strSearch)+ Len(strSearch)
nextIfPos = InStr(startPos,allContent,"<!-- End MyGeneratedItems -->")  
strFirstPart = Left(allContent,startPos)
strLastPart = Mid(allContent,nextIfPos)

strDoBuy = ""  
For Each cell In rangeNum.Cells
	strDoBuy = strDoBuy & "<DoBuy ItemListType=""Item"" ItemID=" & cell.Value & " Price=" & cell.Offset(0,1).Value & " Amount=" & cell.Offset(0,2).Value & " />" & vbCrLf  
Next 
Set objText = objFSO.OpenTextFile(XMLPATH, 2)
strFinal = strFirstPart & strDoBuy & strLastPart
objText.Write(strFinal)
objText.Close
objExcel.Quit
wscript.echo "Verarbeitung abgeschlossen!"  

Jetzt noch einen Doppelklick auf die VBS-Datei und dein XML-File sollte die DoBuy-Items an der gewünschten Stelle enthalten.

Alternativ: Direktes integrieren der Funktion in Excel

Alternativ kannst du den Code auch direkt in Excel einbauen. Dazu öffnest du in dem Excel-File den VBA Editor (ALT-F11), klickst doppelt auf den Knoten "Diese Arbeitsmappe" und fügst folgenden Code ein (auch hier wieder Anpassungen vornehmen in Zeile 1 und 3):

Sub Generate()
    XMLPATH = "C:\test.xml"  
    Set objFSO = CreateObject("Scripting.FileSystemObject")  
    Set rangeNum = Worksheets(1).Range("A2:A10")  
    
    Set objText = objFSO.OpenTextFile(XMLPATH, 1)
    allContent = objText.ReadAll()
    objText.Close
    strSearch = "<!-- MyGeneratedItems -->"  
    startPos = InStr(allContent, strSearch) + Len(strSearch)
    nextIfPos = InStr(startPos, allContent, "<!-- End MyGeneratedItems -->")  
    strFirstPart = Left(allContent, startPos)
    strLastPart = Mid(allContent, nextIfPos)
    
    strDoBuy = ""  
    For Each cell In rangeNum.Cells
        strDoBuy = strDoBuy & "<DoBuy ItemListType=""Item"" ItemID=" & cell.Value & " Price=" & cell.Offset(0, 1).Value & " Amount=" & cell.Offset(0, 2).Value & " />" & vbCrLf  
    Next
    Set objText = objFSO.OpenTextFile(XMLPATH, 2)
    strFinal = strFirstPart & strDoBuy & strLastPart
    objText.Write (strFinal)
    objText.Close
End Sub

Das Excel-File musst du dann aber als *.xlsm abspeichern weil es Makros enthält.
Das Makro Generate kannst du in Excel dann z.B. einer Schaltfläche zuweisen.

So, damit solltest du jetzt aber klar kommen.
Grüße Uwe