jan173
Jun 23, 2013
view4185
view16
0
Goto Top

Einzelne Excelzelle in XML einfügen?

GermanQuestionXMLDevelopment
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!
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 208493

Url: https://administrator.de/contentid/208493

Printed on: April 27, 2024 at 00:04 o'clock

16 Comments
Latest comment
Goto Top
Member: colinardo
colinardo Jun 24, 2013, updated at Jun 26, 2013 at 06:53:00 (UTC)
Goto Top
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 face-wink
Grüße Uwe
heart1
Member: Jan173
Jan173 Jun 26, 2013 updated at 03:10:41 (UTC)
Goto Top
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.
heart1
Member: colinardo
colinardo Jun 26, 2013 updated at 09:07:51 (UTC)
Goto Top
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.
Member: colinardo
colinardo Jun 26, 2013 at 05:59:10 (UTC)
Goto Top
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
... ... ...
Member: Jan173
Jan173 Jun 26, 2013 at 17:15:02 (UTC)
Goto Top
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
Member: colinardo
colinardo Jun 26, 2013 updated at 17:28:52 (UTC)
Goto Top
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
Member: Jan173
Jan173 Jun 26, 2013 updated at 18:22:54 (UTC)
Goto Top
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
heart1
Member: colinardo
colinardo Jun 26, 2013 updated at 18:34:09 (UTC)
Goto Top
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 ?
Member: Jan173
Jan173 Jun 26, 2013 at 18:41:36 (UTC)
Goto Top
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.
Member: colinardo
colinardo Jun 26, 2013 updated at 18:52:34 (UTC)
Goto Top
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.
Member: Jan173
Jan173 Jun 26, 2013 at 18:59:27 (UTC)
Goto Top
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
Member: colinardo
colinardo Jun 26, 2013 at 18:59:50 (UTC)
Goto Top
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.
heart1
Member: Jan173
Jan173 Jun 26, 2013 at 19:57:43 (UTC)
Goto Top
Hab dir ne PM geschickt. Wenn du es hier machen willst, auch i.O.
Member: colinardo
colinardo Jun 26, 2013 at 22:35:51 (UTC)
Goto Top
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 ...
Member: colinardo
colinardo Jun 27, 2013 updated at 08:00:55 (UTC)
Goto Top
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:

back-to-topDas hier fügst du in der XML-Datei an der gewünschten Stelle ein:
<!-- MyGeneratedItems -->

<!-- End MyGeneratedItems -->

back-to-topFolgenden 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.
back-to-topAlternativ: 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
Member: Biber
Biber Jul 03, 2013 updated at 16:26:14 (UTC)
Goto Top
Moin Jan123,


Nachfrage: wassn jezz' Status von dieser Frage?
Gelöst? Weggedöst? Gegenstandslos?

Grüße
Biber
GermanQuestionXMLDevelopment