Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen

Datei aus dem Internet herunterladen

Betrifft: Datei aus dem Internet herunterladen von: Niclaus Wurthberg
Geschrieben am: 03.09.2020 20:43:02

Grüezi

Das Schweizerische Bundesamt für Statistik veröffentlicht jeden Monat den Landesindex der Konsumentenpreise. Wir brauchen diese Daten für verschiedene Berechnungen und Kontrollen.

Ich möchte die entsprechende xlsx-Datei mit VBA herunterladen und im Ordner C:\bfs\ speichern. Ist das möglich, ohne dass ich auf der entsprechenden Seite den Link "Download Tabelle" anklicken muss? Ich arbeite mit Excel 2013. Es geht um folgende Seite:

https://www.bfs.admin.ch/asset/de/cc-d-05.02.08

Dort kann mit dem erwähnten Link die folgende Datei heruntergeladen werden:
cc-d-05.02.08.xlsx

Wenn das mit VBA nicht geht: Gibt es eine Möglichkeit, dass ich wenigstens per VBA den Ordner festlegen kann, in welchem die Datei gespeichert werden soll (C:\bfs\)?

Vielen Dank für eure Hilfe und freundliche Grüsse
Niclaus

Betrifft: AW: Datei aus dem Internet herunterladen
von: Zwenn
Geschrieben am: 04.09.2020 12:50:10

Hallo Niclaus,

die Datei kann nicht direkt runtergeladen werden, da es keinen fertigen Download-Link im HTML-Code gibt. Das habe ich allerdings erwartet, denn in der Regel ist es so, dass solche Dateien in dem Moment vom Server erstellt werden, in dem sie angefragt werden. Dadurch hat man den aktuell verfügbaren Datenstand.

Du kannst die Datei aber trotzdem runterladen. Das ist allerdings etwas tricky, weil es nur über den Download-Dialog des IE funktioniert, der am unteren Bildschirmrand angezeigt wird, sobald man den Link anklickt. Diesen kann man ausschließlich mit Sendkeys() bedienen. Darüber hinaus gibt es zwei unterschiedliche Orte, an denen der IE einen Download automatisch speichern kann. Du kannst Dir das Geraffel mit der Regestry sparen, wenn Du Deinen Pfad als Download-Pfad festlegen kannst. Sprich, wenn der IE nicht anderweitig genutzt wird.

Das war jetzt ein vorgreifender Hinweis auf den folgenden älteren Thread, in dem ich das genauer erklärt habe und auch etwas Code zur Verfügung gestellt habe, mit dem man so einen Download initiieren kann. Schau Dir den Thread mal an. Den Code aus dem ersten Posting brauchst Du nicht studieren, der ist sehr lang und unübersichtlich.
https://www.herber.de/forum/cgi-bin/callthread.pl?index=1702250

Wie ich gesehen habe, flackert die Seite einmal, wenn man den Download-Link anklickt. Es kann sein, dass dadurch die Verbindung zwischen IE und VBA unterbrochen wird. In dem Fall bekommt man die Fehlermeldung, dass der Remote-Server nicht mehr erreichbar ist. Dann muss der IE wieder "eingefangen" werden. Wie das funktioniert, kannst Du ggf. in diesem Thread nachlesen:
https://www.herber.de/forum/cgi-bin/callthread.pl?index=1660619

Ich hoffe Du kannst mit diesen Infos etwas anfangen. Wie Du siehst, musst Du etwas Aufwand betreiben, um Dein Ziel zu erreichen.

Viele Grüße,

Zwenn

Betrifft: Ignoriere das erstmal
von: Zwenn
Geschrieben am: 04.09.2020 12:58:53

Hallo noch einmal,

ich habe gerade etwas ausprobiert und evtl. kann man die Datei doch direkt runterladen. Ich melde mich dazu später oder am Wochenende noch einmal.

Viele Grüße,

Zwenn

Betrifft: AW: Ignoriere das erstmal
von: Niclaus Wurthberg
Geschrieben am: 04.09.2020 17:36:51

Hallo Zwenn
Herzlichen Dank, dass Du Dich so abplagst mit meinem Anliegen. Zur Ergänzung noch zwei Sachen.

a) Aus der Vergangenheit: Bis anfangs Juni wurden die monatlichen Indizes seit vielen Jahren direkt als Tabelle auf einer Homepage des Bundesamtes publiziert. Ich konnte (per VBA) über das Menü "DATEN / Externe Daten abrufen / Aus dem Web" diese Tabelle auswählen und in den eigenen Excel-Dateien mit VBA verarbeiten. Leider werden diese Tabellen nicht mehr so publiziert.

b) Zum Link "Download Tabelle" auf der Seite
https://www.bfs.admin.ch/asset/de/cc-d-05.02.08
Wenn ich diesen Link mit der rechten Maustaste anklicke und "Link kopieren" wähle, erhalte ich für die August-Zahlen folgende Adresse:
https://www.bfs.admin.ch/bfsstatic/dam/assets/14229453/master

Mit folgenden Zeilen wird eine Master-Datei auf meinem Desktop geöffnet, die ich ebenfalls mit VBA bestens verarbeiten kann.
LIKMaster = "https://www.bfs.admin.ch/bfsstatic/dam/assets/14229453/master"
ThisWorkbook.FollowHyperlink Address:=LIKMaster

Ganz blöd dabei ist allerdings, dass der Ziffernteil jeden Monat anders lautet. Für die Juli-Zahlen z. B. hiess der Link:
https://www.bfs.admin.ch/bfsstatic/dam/assets/13787106/master

Ich vermute, dieser Ziffernteil wird mit einem Zufallsgenerator gebildet. Oder siehst Du eine logische Erklärung für die Bildung der Zahlenreihe? Wenn ich diese Zahlenreihe kennen würde oder irgendwie errechnen könnte oder sie per VBA "ergattern" könnte, wäre mir natürlich auch sehr geholfen.

Noch einmal vielen Dank! Und ich warte gerne! Die nächsten Zahlen werden ja erst am 1. Oktober publiziert!
Viele Grüsse Niclaus

Betrifft: Nachtrag: Ignoriere das erstmal
von: Niclaus Wurthberg
Geschrieben am: 06.09.2020 12:37:02

Hallo Zwenn
Was ich unter b) geschrieben habe betr. Link: Ich habe das mit Microsoft Edge gemacht. Das entspricht dem, was Du geschrieben hast: Im IE erscheint diese Adresse beim Anklicken des Downloadlinks unten am Fensterrand.
Viele Grüsse Niclaus

Betrifft: AW: Nachtrag: Ignoriere das erstmal
von: Zwenn
Geschrieben am: 06.09.2020 13:40:50

Hallo Niclaus,

das mit dem IE kann man sich in der Tat komplett sparen. Ich gehe mal davon aus, dass der Grundlink sich nicht ändert. Das folgende Makro läd die aktuell vorliegende Datei runter und speichert sie in Deinem vorgegebenen Pfad. Ist der Download erfolgreich, wird die Datei direkt geöffnet.

Du kannst das Makro in Deiner persönlichen Exceldatei speichern, die beim Öffnen von Excel immer im Hintergrund mit geöffnet wird. Dann hast Du das Makro jederzeit zur Verfügung und musst Dich nicht weiter darum kümmern. Du kannst den Makronamen natürlich kürzen. Er ist jetzt recht sperrig.

Ich habe den Quelltext diesmal nicht kommentiert. Da war ich ehrlich gesagt zu faul zu ;-)

Ganz oben in das Modul, unter Option Explicit, kopierst Du die folgende Windows API Funktion. Die wird benötigt, um eine Datei direkt aus dem Internet runterzuladen. Sie ist schon so angepasst, dass sie sowohl unter Excel 32 Bit, wie auch unter Excel 64 Bit funktioniert.
#If Win64 Then
  Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
      (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, _
       ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
  Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
      (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
      ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
Darunter kopierst Du das folgende Makro. Du brauchst keine Verweise setzen, da ich alles mit late binding programmiert habe. Die Datei wird immer unter dem gleichen Namen gespeichert, weil man den mit vorgeben muss und ich nicht weiß, wie man den originalnamen des Downloads auslesen könnte. Das bedeutet, eine vorhandene Datei mit gleichem Namen wird ohne Rückfrage überschrieben. Wenn Du das nicht willst, kannst Du das Makro so ändern, dass eine vorhandene Datei vor dem Download automatisch umbenannt wird oder das die neu runtergeladene Datei direkt einen neuen Namen bekommt.
Sub downloadSwissLandesIndexKonsumentenPreise()

Const url As String = "https://www.bfs.admin.ch/asset/de/cc-d-05.02.08"
Const downloadPath As String = "C:\bfs\cc-d-05.02.08.xlsx"

Dim xhr As Object
Dim htmlDoc As Object
Dim downloadURL As String
Dim nodeAllLinks As Object
Dim nodeOneLink As Object

  Set xhr = CreateObject("MSXML2.serverXMLHTTP")
  Set htmlDoc = CreateObject("HTMLFile")
  
  xhr.Open "GET", url, False
  xhr.send
  htmlDoc.body.innerHTML = xhr.responseText
  
  Set nodeAllLinks = htmlDoc.getElementsByTagName("a")
  For Each nodeOneLink In nodeAllLinks
    If InStr(1, nodeOneLink.href, "master") > 0 Then
      downloadURL = nodeOneLink.href
downloadURL = Replace(downloadURL, "about:", "https://www.bfs.admin.ch")
      Exit For
    End If
  Next nodeOneLink
  
  If URLDownloadToFile(0, downloadURL, downloadPath, 0, 0) = 0 Then
    Workbooks.Open downloadPath
  Else
    MsgBox "Download fehlgeschlagen"
  End If
End Sub
Der eigentliche Downloadvorgang funktioniert mit dem Code bei mir einwandfrei. Ich hoffe bei Dir auch.

Viele Grüße,

Zwenn

Betrifft: AW: Nachtrag: Ignoriere das erstmal
von: Niclaus Wurthberg
Geschrieben am: 06.09.2020 18:50:03

Grüezi Zwenn
SUPER! Es klappt wunderbar!
"Ich gehe mal davon aus, dass der Grundlink sich nicht ändert": In den letzten Jahren blieb er unverändert.
Bis anfangs Oktober, wenn die neuen Zahlen publiziert werden, habe ich nun Zeit, hinter die Geheimnisse Deiner Codes zu kommen. Oder viel einfacher: Wenn Du mal in Zürich bist, kannst Du sie uns im Detail erklären. Zwei Buchhalter werden Dir dabei voll Begeisterung um den Hals fallen - mit Corona-Masken selbstverständlich! Meldest Dich: weer at bluewin.ch
Ganz herzlichen Dank! Und viele Grüsse
Niclaus

Betrifft: AW: Nachtrag: Ignoriere das erstmal
von: Zwenn
Geschrieben am: 06.09.2020 20:49:03

Hallo Niclaus,

schön das es läuft wie Du es Dir gewünscht hast :-) Danke für die Einladung nach Zürich. Aufgrund der Situation komme ich da aus Berlin zur Zeit allerdings eher nicht in Eure Nähe.

Hmmm, also ich habe schon so oft Code dokumentiert und meistens das Gefühl, dass das sowieso niemanden interessiert. Jetzt habe ich mir das heute gespart und prompt willst Du verstehen, was ich da aufgeschrieben habe ;-)

Also Web Scraping kann man leider nicht in ein paar Sätzen erklären. Im Grunde muss man dafür neben VBA (wenn man das mit Excel machen möchte) auch HTML-Kenntnisse haben und sich mit dem DOM (Document Object Model) beschäftigen. In diesem ist vereinfacht gesagt für einen Browser festgelegt, wie die HTML-Elemente angeordnet sind.

Nun habe ich hier keinen Browser verwendet, sondern einen XMLHttpRequest. Daher der Variablenname xhr als Kurzform. Über dieses Objekt werden ebenfalls die Methoden des DOM zur Verfügung gestellt. Verwendet habe ich getElementsByTagName("a"). Damit wird eine sogenannte HTML Node Collection gebildet, die alle HTML-Elemente mit dem Namen a enthält. Also allen Links.

So eine HTML Node Collection ist ähnlich handzuhaben, wie ein Array. Alle enthaltenen Elemente sind mit einem Index versehen, über den man auf bestimmte Elemente zugreifen kann. Dieser Index beginnt bei 0. Da wir im vorliegenden Makro eine For Each Schleife verwenden, um alle Elemente abzuklappern, bis das richtige gefunden ist, siehtz man keine Verwendung des Index.

Viel mehr will ich hier jetzt auch gar nicht schreiben. Das ist echt ein weites Feld. Solltest Du da etwas recherchieren, wirst Du schnell feststellen, dass die meisten Informationen zum DOM mit JavaScript Code erklärt werden. Das liegt daran, dass die clientseitige Sprache für Webseiten eben JS ist.

Will man entsprechendes mit VBA programmieren, muss man sich die JS Erklärungen quasi übersetzen. Man findet aber auch viel fertigen VBA Code zu unterschiedlichen Aufgabenstellungen zum Web Scraping.

Das mal als kurzer Abriss. Jetzt schauen wir mal, wie sich die Schweiz gegen Deutschland in der Fußball Nations Ligue schlägt ;-)

Viele Grüße,

Zwenn

Beiträge aus dem Excel-Forum zum Thema "Datei aus dem Internet herunterladen"