Office: WebScraping-Laufzeitfehler 91: Objekvariable nicht festgelegt

Hi zusammen,
ich erhalte die obergenannte Fehlermeldung bei meinem Code, welcher mir die Tabelle aus dem Link: https://data.snb.ch/en/topics/uvo#!/...T5,HYP,KON,UKR) ziehen soll.

Ich hoffe mir kann jemand helfen. Es sind alle Variablen definiert und nötige Verweise zum Web Scraping sind aktiviert. Der Fehler tritt in der html.getElementsByClass - Zeile auf. Freue mich sehr über Antworten und vielen Dank im Voraus!

Code:
Sub Get_Web_Data()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant


' Website to go to.
website = "Swiss economic affairs"

MsgBox website


' Create the object that will make the webpage request.
Set request = CreateObject("MSXML2.XMLHTTP")

' Where to go and how to go there
request.Open "GET", website, False

' Get fresh data.
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"


' Send the request for the webpage.
request.send


' Get the webpage response data into a variable.
response = StrConv(request.responseBody, vbUnicode)

' Put the webpage into an html object to make data references easier.
html.body.innerHTML = response

' Get the price from the specified element on the page.
credit = html.getElementsByClassName("epbTable").Item(0).innerText

Stop

' Output the table into a new worksheet.
Debug.Print credit

End Sub

Den Teil, die Tabelle in ein neues Workbook zu kopieren, habe ich noch nicht geschrieben.

Liebsten Dank!
Krahllu

:)

 

Ich finde auch kein Class-Element Namens "epbTable", wenn ich mir den Quelltext der Seite so anschaue.

 

Ok, es war div.epbTable.

Nun aber mit wirklich einem Class Element:


price = html.getElementsByClassName("dataTable table-striped table-bordered table-curved").Item(0).innerText
Stop

Oder ist das kein Class Element?
Kann leider keinen Screenshot hochladen.

Beste grüße
Krahllu

 

Hallo Krahllu,

das ist eine Seite, die dynamisch Daten über JavaScript nachlädt, wenn der "HTML-Rahmen" geladen wurde. Solche dynamischen Daten kannst Du in Deiner versuchten Weise nicht über einen xhr abfragen, weil sie schlicht nicht geladen werden. Du bekommst nur besagten "HTML-Rahmen" ohne Datentabelle. Das kannst Du sehen, wenn Du den response ausgeben lässt.

Du kannst versuchen das JSON zu laden, in dem die Daten sich befinden (ist mir bei einem Kurzversuch jetzt nicht gelungen) und dieses zu verarbeiten. Oder Du machst das Ganze über den Internet Explorer.

Zur Info:
Deine zuletzt verwendetes Class Element, sind in echt 4 Klassen. Die einzelnen Klassen werden durch ein Freizeichen voneinander getrennt und können auch einzeln abgefragt werden. Allerdings nur, wenn Du den xhr über early binding gestaltest. getElementsByClassName() funktioniert für xhr nicht mit late binding.

In diesem Fall hat die Tabelle aber eine ID. Die ist eindeutig und Du kannst die Tabelle darüber direkt abgreifen. Falls es Dir nur auf die Daten ohne Kopfzeile ankommt, kannst Du auch direkt auf den tbody zugreifen, denn der hat eine eigene ID.

Das erstmal als Info in Kurzform. Aus Zeitmangel ohne irgendwelchen Code.

Ich will Dir eigentlich nur mitteilen, dass Du den falschen Ansatz hast, um von der Seite Daten abzugreifen.

Viele Grüße,

Zwenn

 

hi Zwenn,
Danke dir für deine Antwort! Ich werde einen neuen Ansatz suchen. Es ist äußerst schwer, basierend auf YT Videos den passend Ansatz für mein Problem zu finden. In Anbetracht der Tatsache, dass ich wenig Verständnis von JSON files & xhr habe.

 

Ich habe einen neuen Ansatz. Ich will nur per Link eine Datei runterladen, in mein Worksheet kopieren. Fertig. Selbst bei dem Code (1:1 vom Prof kopiert) bekomme ich die Fehlermeldung " Could not make a connection to Database". Dabei ist der Link ja einfach der Downloadlink.
- ich bin am Verweifeln

Besten dank für jegliche Hilfe.

Code:


Sub import()
'Defining empty variable and Objects
Dim url As String
Dim TableName As String
Dim wb As Workbook
Dim wb_temp As Workbook
Dim ws_raw As Worksheet

' Create a variable that contains the Name of our SNB Data Table I want to download
TableName = "frsekgevehupq"
'Just checking our variable exists:
Debug.Print TableName

' Name our current workbook
Set wb = ActiveWorkbook

' Create and Name the worksheet where we put the raw data
Call CreateNewRawDataWorksheet
' "Set" creates an "object" called "ws_raw" we can use later on by using this object name
Set ws_raw = wb.Sheets("RawData")

' If there would be an error, skip the next steps..
On Error GoTo Errorhandler

'SNB webiste allows us to access their online data through an API
'check out: Help

' Create the URL as a string variable
url = "Volkswirtschaft der Schweiz"
' Prompt a Message box with the URL
MsgBox url

' By creating the "object" with Set. we "open" the URL in form of a CSV
' Note: this works the same way as we would open a workbook from our computer
Set wb_temp = Workbooks.Open(url)
' activate the new worksheet we loaded
wb_temp.Activate
' copy "UsedRange"
ActiveSheet.UsedRange.Copy
' Paste in our Worksheet RawData (the object we call "ws_raw")
ws_raw.Range("A1").PasteSpecial
' Close the Temporary file (the one we opened from the SNB data Page
wb_temp.Close
' Activate our Worksheet "RawData"
ws_raw.Activate
' Transform "Text to columns"
Range("A4").CurrentRegion.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Semicolon:=True

'



' The Macro without Error Finishes here... ("Exit Sub")
Exit Sub

' NOTE: The downloaded file contains abbreviations, here you find the list with these "dimensions"
' https://data.snb.ch/api/cube/babilkunddoua/dimensions/en"

' if there would be an error, jump here
Errorhandler:
MsgBox "Could not make a connection to datasource, please check if the dataset exits."
' This Ends the Error hanlder
End

End Sub


'#######################################
' This creates a new Worksheet called "RawData", after potentially deleting a worksheet with this name:
'#######################################

Sub CreateNewRawDataWorksheet()
' Loop through all Sheets
For Each ws In Worksheets
' If there's a Sheet named "RawData"
If ws.Name = "RawData" Then
' Delete It (1st need to disable alerts, then switching them on again)
Application.DisplayAlerts = False
Sheets("RawData").Delete
Application.DisplayAlerts = True
End If
Next
' now create a Worsheet Named "RawData" as the last sheet
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RawData"
End Sub


grüße
Krahllu

 

Hallo Krahllu,

ich habe eine gute und eine schlechte Nachricht für Dich.

Die Gute:
Es klappt über das Runterladen des richtigen JSONs und es ist sehr schnell

Die Schlechte:
Es ist in der Verarbeitung nicht trivial, weil verschachtelte Dictionaries und Collections die Hirarchie des JSON beim Parsen nachbilden. Da hatte ich jetzt schon etwas Mühe an die Daten zu gelangen, die Du haben willst.

Falls Du mit Dictionaries und Collections nicht nix zu tun hattest, wird es schwer nachzuvollziehen, was da passiert. Zumal ich das hier (mindestens heute Nacht) nicht mehr erklären werde. Auch, wie man an die funktionierende Abfrage für das JSON kommt, erkläre ich in dieser Antwort nicht.

Aber fangen wir mal vorne an:
Du hast ausprobiert, eine CSV Datei direkt über die URL in Excel zu öffnen. Ansich eine gute Idee. Das Problem bei Seiten, die solche dynamischen Daten zur Verfügung stellen ist aber, dass auch die Dateien dynamisch erzeugt werden. Also in dem Moment, in dem man den Downloadbutton anklickt. Das liegt daran, dass es gar nicht möglich ist auf dem Server alle möglichen Dateien vorzuhalten, die man durch die einstellbaren Parameter abfragen kann.

Wie gesagt, geht es aber über das Runterladen einer JSON Datei, die alle benötigten Daten der Tabelle enthält. Sie enthält noch mehr, was sie etwas kompliziert macht.

Ok, an dieser Stelle macht es noch Sinn Code zu Posten. Um das JSON runterzuladen habe ich die Funktion GetJSON() geschrieben. Die funktioniert ganz speziell nur für die von Dir verlinkte Seite. Die Funktion selbst ist flexiebel einsetzbar. Wenn Du ihr dynamische Jahre mit Quartalen für Start und Ende übergibst, wird ein JSON mit genau diesem Zeitraum runtergeladen. Das JSON selbs wird von der Funktion als String zurückgegeben.

Der abruf findet über xhr statt und es hat neulich bei meinem Schnelltest nicht geklappt, weil ich übersehen hatte, dass es eine POST Anfrage ist. Ich habe es mit GET versucht. Auch das führe ich jetzt nicht weiter aus. 1&1 erklärt das für den Einstieg erstmal ausreichend:
GET vs. POST (1&1)

JSON steht übrigens für JavaScript Object Notation. Das ist ein Datenaustauschformat, wie CSV oder XML. Es hat halt ein eigenes Format. Bei Interesse ist Wikipedia eine gute Adresse für erste weitererklärende Informationen:
JavaScript Object Notation (Wikipedia)

Dies ist die Funktion GetJSON():
Code:

Um sie zu testen, kannst Du folgende Sub() darauf loslassen. Die schreibt das JSON einfach in die Zelle A1 der aktiven Tabelle:
Code:

Kopiere die Zelle A1 und wirf das JSON in die Textbox der folgenden Seite. Anschließend klickst Du auf den Button Process
JSON Formatter

Wenn Du das gemacht hast, scollt die Seite zu einer Textbox mit dem JSON, wie es in der vorhin genannten Hierarchie aussieht. Die von Dir gewünschten Daten stehen spaltenweise in der Sektion observations im Eintrag "var".

Um dieses Ungetüm nun auch in unserem Makro zu verarbeiten, benutzen wir, was Mr. Tim Hall uns dankenswerterweise schon fertig zur Verfügung stellt.

Zum einen den JsonConverter:
VBA-JSON (GitHub)

Zum anderen ersetzen wir das Directory von Windows. Die Funktionalität bleibt exakt gleich:
Dictionary Klasse (GitHub)

Das Dictionary von Tim Hall verwenden wir, um den Verweis auf die Microsoft Scripting Runtime zu umgehen. Damit ist sichergestellt, dass das Makro auf jedem Rechner läuft, ohne etwas konfigurieren zu müssen. Auf jedem Rechner? Nein, auf einem Mac läuft das Ganze nicht. Das Dictionary zwar schon, aber xhr ist eine Windows eigene Sache. Das gibts nicht auf dem Mac.

So, damit Du Dich damit schonmal nicht rumquälen musst, habe ich eine Datei hochgeladen, in der das alles schon fertig importiert ist und die natürlich auch den von mir erstellten Teil enthält.

Das eigentliche Makro, um die gerade beschriebenen Codes von Tim Hall zu verwenden ist bisher nicht so flexiebel, als dass man damit beliebige Zeiträume verarbeiten kann. Irgendwann muss ich auch mal schlafen ;-)

Das Makro selbst sieht bisher so aus:
Code:

Ich hatte überlegt, ob ich diesen Stand hier überhaupt hochladen soll. Aber da es nun schonmal so weit ist, hast Du vielleicht die Muße, Dich damit zu beschäftigen. Aber wie gesagt, falls Du Anfänger im Programmieren bist, sei nicht frustriert. JsonConverter und Dictionary verwende ich übrigens auch nur als BlackBox.

Viele Grüße,

Zwenn

PS: Ich habe es nicht ausprobiert, aber es ließe sich wahrscheinlich auch eine Lösung über den Internet Explorer erstellen. Über xhr ist es aber wesentlich besser.

 

Hi Zwenn,

tausend Danke für deine Mühe!!! Ich schätze das.

Leider bin ich immer noch nicht am Ziel. Wenn ich deinen Code nutze, bekomme ich einen "Objekt unterstützt diese Eigenschaft oder Methode nicht” (FEHLER 438)" Fehler. Natürlich habe ich den Fehler gegooglet. Es werden leider keinen expliziten Gründe für den Fehler genannt. Ich vermute, dass du eine andere Version von Excel nutzt? Ich nutze Excel 2016 durch eine VDI solution.
Der Fehler tritt im DataFromSnbCh sub in der Schleife auf. Bei:
or Each firstLvlCol In parsed("observations")

Ich hoffe das lässt sich noch klären... *Smilie

Danke noch mal für die hilfe bis jetzt!

Beste grüße
Krahllu

 

Hallo Krahllu,

hast Du meine bereitgestellte Datei verwendet und dort den Fehler bekommen? Falls nicht und Du hast die Makros aus meinem Posting kopiert, musst Du zwingend die beiden verlinkten GitHub Links "studieren" und den Code in Deine Datei importieren.

• Was passiert, wenn Du nur das JSON in die Zelle A1 schreiben lässt, wie ich es beschrieben habe?
• Kann jemand anders das von Krahllu beschriebene Verhalten nachvollziehen?

Ich verwende ebenfalls Excel 2016.

Viele Grüße,

Zwenn

 

Hallo zusammen,

die könnte etwa so aussehen:
Code:

Ich habe bereitgestellte Datei verwendet und keinen Fehler bekommen.

PS: Die Adresse musste ich aufteilen, weil sonst hat die Forumsofware den Link kaputtgemacht.

mfg Anton

 

Hallo Anton,

danke fürs Testen und den Anasatz zur Lösung über den IE. Leider scheint der TO noch keine Zeit gefunden zu haben, sich mit dieser Sache weiter auseinander zu setzen oder er hat inzwischen eine für sich zufriednestellende Lösdung gefunden.

Viele Grüße,

Zwenn

 

Sag jetz' nicht, dass Du überrascht bist *biggrin.gif*

 

Ich habe es nur höflich formuliert *wink.gif* Vor allem ging es mir aber um ein Feedback an Anton *Smilie