xml-to-excel-via-url

[PeterN]

Hola!

Ich habe eine Liste mit eindeutigen urls (Spalte A, Tabelle1) die jeweils eine xml abfrage beinhalten.

Ich würde gerne über eine automatisierte Abfrage aus jeder dieser xml dateien immer dieselben 3 werte (WERT1, WERT2, WERT3 s.u.) in excel importieren, aber immer nur die ersten zehn 3er-pärchen (Es gibt immer mehr wie 10 pärchen pro link bzw. xml file)

Jeder xml file ist geich aufgebaut:

Code:

<response version="2">
<query></query>
<location></location>
<totalresults></totalresults>
<start></start>
<end></end>
<results><result>
<jobkey>WERT1</jobkey>
<jobtitle>WERT2</jobtitle>
<company></company>
<city></city>
<state></state>
<country></country>
<formattedLocation></formattedLocation>
<source></source>
<date></date>
<snippet></snippet>
<url>WERT3</url>
<onmousedown></onmousedown>
<logo></logo>
</result><result>
<jobkey>WERT1</jobkey>
<jobtitle>WERT2</jobtitle>
<company></company>
<city></city>
<state></state>
<country></country>
<formattedLocation></formattedLocation>
<source></source>
<date></date>
<snippet></snippet>
<url>WERT3</url>
<onmousedown></onmousedown>
<logo></logo>
</result><result>

usw.

</results>
</response>

Ich brauche nur die drei Werte:

Code:

<jobkey>WERT1</jobkey>
<jobtitle>WERT2</jobtitle>
<url>WERT3</url>

Ein mögliches Ergebnis wäre dann:

Tabelle2

Spalte A1-A10 = die abgefragte url1
Spalte B1-B10 = dazu passend WERT1
Spalte C1-C10 = dazu passend WERT2
Spalte D1-D10 = dazu passend WERT3

Spalte A11-A20 = die abgefragte url2
Spalte B11-B20 = dazu passend WERT1
Spalte C11-C20 = dazu passend WERT2
Spalte D11-D20 = dazu passend WERT3
usw.

Herzlichen Dank

[schauan]

Hallöchen,

wie holst Du die Daten bisher rein? Mit PowerQuery?

[PeterN]

Hi André!

Bisher hole ich die Daten noch gar nicht.

P.S. Wenn ich das richtig verstehe, würde Power Query bei meinem 2007 auch gar nicht laufen.

[snb]

Du kannst in ein separatem Arbeitsblatt für jeden Link ein Web Querytable erstellen.
Nach 'refresh' sind alle neue Daten da.
Dann kannst du mittels Formel oder VBA die 10 erste Werten in ein anderes Arbeitsblatt kopiieren.

[PeterN]

Danke für den Vorschlag.

Aber es sind stellenweise mehrere hundert Links und die wechseln auch noch ab und zu.

[Zwenn]

Hallo Peter,

lade mal bitte eine Beispiel-XML-Datei hoch.

Viele Grüße,

Zwenn

[snb]

Das änderst nichts an meinem Vorschlag.

[PeterN]

Wenn ich das richtig verstehe muss ich (später) jedes Arbeitsblatt EINZELN aktualisieren oder gibt es da noch eine andere Lösung?

Ich habe jetzt manuell mehrere xml untereinander in ein Arbeitsblatt importiert. Wenn ich das Arbeitsblatt aktualisiere, wird nur die LETZTE xml datei aktualisiert. Gibt es eine Lösung wo ALLE importierten xml dateien aktualisiert werden?

[snb]

Es gibt nur 1 Arbeitsblatt, das sehr viele Querytables enthalten kann.ThisWorkbook.Refreshall aktualisiert alle Querytables in one go .Insgesamt brauchst du nur 2 Arbeitsblätter.

[Zwenn]

Hallo Peter,

Du kannst auch über das DOM gehen (Document Object Model). Ich habe jetzt nur Dein einkopiertes Beispiel-XML aus Deinem ersten Post verwendet. Die Schleife um Deine URLs, was wo hinkopiert werden soll und die Begrenzung auf max. 10 Werte pro Datei musst Du noch einbauen. Ich würde die Sub aus einer anderen aufrufen und url, aktuelleZeile und aktuelleSpalteEins als Parameter übergeben.

Code:

Sub XMLEinlesen()

'Variablen für den Internetzugriff und das DOM-Handling
Dim url As String
Dim xmlDocument As Object
Dim knotenResult As Object
Dim knotenResultEinzel As Object
Dim knotenJobkey As Object
Dim knotenJobtitle As Object
Dim knotenURL As Object
Dim aktuelleZeile As Long
Dim aktuelleSpalteEins As Integer

 aktuelleZeile = 2
 aktuelleSpalteEins = 1
 url = "G:\Rest\Herber Forum\XML aus Web einlesen\xmlBeispiel.xml"
 
 'XML-Dokument instanzieren und XML-Dokument einlesen
 Set xmlDocument = CreateObject("MSXML2.DOMDocument")
 xmlDocument.Load url
 
 'Resul-Tags einsammeln
 Set knotenResult = xmlDocument.getElementsByTagName("result")
 
 If Not knotenResult Is Nothing Then
   'Wenn vorhanden alle Result-Tags durchgehen
   For Each knotenResultEinzel In knotenResult
     'Gewünschte Werte holen
     Set knotenJobkey = knotenResultEinzel.getElementsByTagName("jobkey")(0)
     Set knotenJobtitle = knotenResultEinzel.getElementsByTagName("jobtitle")(0)
     Set knotenURL = knotenResultEinzel.getElementsByTagName("url")(0)
     
     'Gewünschte Werte in die Tabelle eintragen, aus der das Makro gestartet wurde
     '(feste Tabelle kann natürlich vorangestellt werden)
     Cells(aktuelleZeile, aktuelleSpalteEins).Value = knotenJobkey.Text
     Cells(aktuelleZeile, aktuelleSpalteEins + 1).Value = knotenJobtitle.Text
     Cells(aktuelleZeile, aktuelleSpalteEins + 2).Value = knotenURL.Text
     
     'Nächste Zeile zum beschreiben festlegen
     aktuelleZeile = aktuelleZeile + 1
   Next knotenResultEinzel
 End If
End Sub

Viele Grüße,

Zwenn