In dieser kleinen Tutorial-Serie will ich anhand einer Online-Handy-Datenbank zeigen, wie man mit VBA Seiten aus dem Internet abruft und nach Informationen sucht, die sich dann in einer Tabelle abspeichern lassen. Warum VBA? Es gibt vermutlich elegantere Lösungen, doch Excel ist eine Software, die die meisten zuhause nutzen. Man benötigt also keine zusätzliche Entwicklungsumgebung. Außerdem hat diese Methode den  Vorteil, dass die Daten sofort zur weiteren Verarbeitung verfügbar sind. VBA ist vielleicht nicht die performanteste Programmiersprache, dafür aber relativ leicht zu beherrschen.

(FYI: Rechtliches zum Thema Web- oder Screen-Scraping)

Diese Artikelserie richtet sich an den fortgeschrittenen Nutzer. Für den Einsteiger gehe ich nicht auf grundlegendes Programmierwissen ein (was sind Klassen, welche Variablen-Typen bietet Excel, etc.pp.) und für den professionellen Softwareentwickler sind meine Codebeispiele vermutlich zu infantil. Ich habe aber die Erfahrung gemacht, dass VBA und Excel für kurzfristige und kleine Projekte dieser Art recht nützliche Hilfsmittel sind. Außerdem bin ich kein “ausgebildeter Softwareentwickler”, weshalb ich an der Stelle auch gleich darauf Hinweise, dass Verbesserungsvorschläge sehr gerne gesehen sind!

Welches wissen solltest du also mitbringen?  Da es um das Parsen von HTML-Code geht, solltest du zumindest Bescheid wissen, wenn ich von div- und a-Elementen und css-Klassen rede. Du solltest auch wissen, wie man in VBA Variablen deklariert oder was eine if-Abfrage ist.

Das Endergebnis ist eine Tabelle mit technischen Spezifikationen zu den Mobiltelefonen, die inside-handy.de listet. Insgesamt werden drei Routinen genutzt, die - in umgekehrter Reihenfolge des Vorgehens - folgende Aufgabe haben:

Die letzte Routine greift auf eine Liste von URL zu, die auf die Datenblätter der Geräte verweisen. Von dort wird der HTML-Quellcode nach den  technischen Informationen durchsucht.

Auf inside-handy.de sind die Geräte nach Herstellern sortiert. Jede Herstellerseite verweist auf die entsprechenden Geräte. Wir werden also jede Herstellerseite (bzw. den entsprechenden HTML-Code) zunächst nach den URL zu den Geräten durchsuchen. Diese Aufgabe übernimmt die zweite Routine bzw. Prozedur.

Die erste Routine schließlich liefert die Liste aller URL zu den Herstellern, die wir uns aus dem HTML-Code der Herstellerübersicht auf inside-handy.de laden. Und damit geht es nun los:

1. Die URL zu den Hersteller-Unterseiten auslesen - sub getManufacturer

1.1 Datei per HTTP von einem Server laden

Im ersten Schritt laden wir die komplette HTML-Datei in den Zwischenspeicher. Dazu gibt es mindesten zwei Methoden, die gängigste ist vermutlich die über WinHttpRequest. Bevor du das nutzen kannst, musst du bei VBA unter Extras - Verweise jedoch erst die Microsoft HTML Object Library einbinden.

Dim url As String Dim result As String Dim winHttpReq As Object url = “http://www.inside-handy.de/hersteller/handys"

Set winHttpReq = CreateObject(“WinHttp.WinHttpRequest.5.1”) winHttpReq.Open “GET”, url, False winHttpReq.send result = winHttpReq.responseText

Die Deklaration der Variablen erklärt sich von selbst. Nachdem ich eine Instanz vom WinHTTP-Objekt erzeugt habe, kann ich die Parameter übergeben. Dazu gehört neben der URL auch die Bestimmung des HTTP-Requests - nämlich GET. Der letzte, booleansche, Paramter gibt an, ob die Verbindung im asynchronen Modus geöffnet werden soll. Mit .send wird der Request tatsächlich ausgelöst und das Ergebni dann an die String-Variable result zurückgegeben. Dort befindet sich nun unser HTML-Code

Wir können unseren Request natürlich auch per POST absetzen und noch andere Header-Informationen anhängen:

Set winHttpReq = CreateObject(“WinHttp.WinHttpRequest.5.1”)

winHttpReq.Open = “Post”, url, False winHttpReq.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” winHttpReq.setRequestHeader “Content-type”, “application/x-www-form-urlencoded” winHttpReq.send (URLEncode(“username=user1&password=secret”) )

result = winHttpReq.responseText

So ist es z.B. möglich, Formulardaten zu übermitteln, um an eine passwortgeschützte Seite oder die Ergebnisseite einer Suche zu gelangen. Dabei werden die POST-Daten als weiterer Parameter beim Senden mitgegeben. Eine weitere Möglichkeit ist die Durchführung einer HTTP-Authentifizierung:

Dim HTTPREQUEST_SETCREDENTIALS_FOR_SERVER As Boolean

winHttpReq.Open “GET”, url, False winHttpReq.SetCredentials “user”, “password”, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER winHttpReq.send

Neben dem winHttpRequest-Objekt gibt es noch eine weniger elegante Methoden, in dem direkt eine Instanz des Internet Explorers erzeugt wird:

Dim sPostData As String Dim bPostData() As Byte Dim WebBrowser: Set WebBrowser = CreateObject(“InternetExplorer.Application”)

WebBrowser.Visible = True

sPostData = URLEncode(“username=user1&password=secret”) ReDim bPostData(Len(sPostData) - 1) bPostData = StrConv(sPostDataData, vbFromUnicode)

WebBrowser.navigate url, 2 + 4 + 8, , bPostData, “Content-type: application/x-www-form-urlencoded” Do While WebBrowser.Busy DoEvents Loop result = WebBrowser.document.body.innerHTML WebBrowser.Quit

Da diese Methode - wie gesagt - nicht sonderlich elegant ist, werde ich aber nicht weiter darauf eingehen.

Nun zurück zu unserem Skript. Den HTML-Code der Seite haben wir nun erstmal in einen String gelegt. Damit wir das HTML-Dokument bequem lesen können, erzeugen wir ein HTML-Document, an das wir den HTML-Code übergeben:

Set HTMLDoc = New HTMLDocument Set HTMLDoc = CreateObject(“htmlfile”) HTMLDoc.Open HTMLDoc.write (CStr(result)) HTMLDoc.Close

Auch hier erklärt sich der Code fast von selber: Eine Instanz des Objektes erzeugen, diese Instanz zum “Befüllen” vorbereiten, den String übergebne und die Instanz wieder “schließen”. Soweit, so unkompliziert. Im nächsten Schritt geht es nun direkt an das Parsen des Quellcodes um die Links zu den Herstellerseiten zu erhalten. Die Schleife dazu ist nicht sehr aufwendig:

Dim oneElement1, allElements1 As IHTMLElementCollection
Dim oneElement2, allElements2 As IHTMLElementCollection
Dim oneElement3, allElements3 As IHTMLElementCollection

Worksheets("srcURL").Range("a2").Select
i = 0
Set allElements1 = HTMLDoc.getElementsByTagName("a")
      
i = 0
For Each oneElement1 In allElements1
    If oneElement1.parentElement.className = "h\_img" Then
        If oneElement1.parentElement.parentElement.ID = "h\_alle" Then
            If InStr(1, oneElement1.getAttribute("href"), "/tablets") <= 0 Then
                Selection.Offset(i, 0).Value = Replace(oneElement1.getAttribute("href"), "about:", "http://www.inside-handy.de")
                i = i + 1
            End If
        End If
        
    End If    
Next oneElement1

End Sub

Wie bin ich vorgegangen? Ich habe mir zunächst den Quellcode der Seite angeschaut. Die Liste der Hersteller ist dort eine Tabelle mit den Logos der Unternehmen. Die gewünschte Information befindet sich in a-Elementen, die wiederum innerhalb eines div-Elements liegen. Das gemeinsame “Oberelement” ist ein div-Container mit der CSS-Klasse “h_img” bzw. einem weiterne div-Container (“h_alle”). Außerdem gibt es einen ausgeblendeten div-Container mit einer Liste von Tablet-Herstellern, diese haben den Begriff “/tablet” in der href-Angabe und müssen ignoriert werden.

Mit “Set allElements1 = HTMLDoc.getElementsByTagName(“a”)” lasse ich mir also erst alle a-Elemente aus dem Quellcode in meinen “Container” legen.

Mit der ersten for-each-Schleife durchlaufe ich nun diesen Container und prüfe mit den ersten zwei if-Abfragen, ob sich das a-Element unterhalb der erwähnten div-Container befindet. Da auf der Seite noch ein weitere identische div-Container mit diesen css-Klassen für die Liste der Tablet-Hersteller existiert, muss ich mit einer dritten if-Abfrage die URL des a-Elements überprüfen. Erst dann kann ich das Attribut des a-Elements auslesen und in mein Excel-Worksheet schreiben.

Fertig ist der erste Schritt - eine Liste der URL zu den jeweiligen Herstellern. Im nächsten Teil werde ich diese Liste durchgehen und von den jeweiligen Seiten die URL zu den Geräten auslesen.