Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1708to1712
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Loop Internetdaten in Excel importieren

VBA Loop Internetdaten in Excel importieren
30.08.2019 09:36:40
Simon
Guten Tag
Ich habe eine Arbeit, welche total ineffizient ist, wenn ich diese manuell machen muss.
Und zwar muss ich auf der Webseite: https://www.fundinfo.com/de/CH-prof
beim Suchfeld beinahe täglich etwa 50 Nummern (von meinem Excel aus Spalte A) hineinkopieren, z.B. CH0000700903, anschliessend muss ich dann auf Kosten und Gebühren klicken und die "Ongoing Charges" in meinem Excel in der Spalte H einfügen.
Diese Gebühren ändern von Zeit zu Zeit, weshalb diese Liste immer länger und aufwändiger wird.
Gibt es eine Möglichkeit dies via VBA zu automatisieren? Dies würde mir das Leben sehr erleichtern.

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ja, aber ...
30.08.2019 09:46:17
Fennek
Hallo Simon,
ja, mit VBA können solche Abfragen (vermutlich) automatisiert werden.
Zumindest früher gab es "Muster-Depo" mit denen man viele Wertpapiere beobachten konnte. Das wäre sicher einfacher.
mfg
AW: ja, aber ...
30.08.2019 11:04:24
Simon
Danke für die Antwort, die mir aber nichts bringt. Ich würde mich über einen VBA Code freuen.
Besten Dank
AW: Musterlösung
30.08.2019 11:34:00
Fennek
Hallo,
VBA-Code zum Auslesen von Webseiten müssen sehr spezifisch angepasst werden. Hier ein Beispiel, mal sehen ob da Freude aufkommt.

Sub T_1()
With CreateObject("MSXML2.XMLHTTP")
.Open "get", "https://www.herber.de/bbs/user/121136.txt", 0
.send
c00 = .responsetext
End With
Debug.Print c00
End Sub
Die Url ist ein Text, dabei muss jede WKN an die Grundform angepasst werden. Da ich keine WKN auswendig kenne, konnte ich den Responsetext nicht prüfen.
mfg
Anzeige
AW: Musterlösung
30.08.2019 13:27:36
Hajo_Zi
lasse den Fragesteller entscheiden ob offen.
Es ist heute nicht mehr üblich eine Rückmeldung zu geben und so ist der Beitrag über 6 Tage offen.
Das ist nur meine Meinung zu dem Thema.

AW: VBA Loop Internetdaten in Excel importieren
30.08.2019 13:39:00
Rob
Hier ein erster Ansatz, allerdings musst Du bei dieser Seite noch den Anlegertyp bestimmen und Datenschutzbestimmungen bestätigen. Müsste man sehen, ob das auch mit VBA geht oder machst die Voreinstellungen und führst anschl. den Code aus:

Option Explicit
Const UrlFundinfo = "https://www.fundinfo.com/en/CH-priv"
Sub OngoingCharges()
Dim ieApp As New SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.htmlDocument
Dim htmlInput As MSHTML.HTMLInputElement
With ieApp
.Visible = True
.Navigate UrlFundinfo
Do While .Busy
Loop
Set htmlDoc = .Document
Set htmlInput = htmlDoc.all("query")
Set htmlSubmit = htmlInput
With htmlInput
If Not htmlInput Is Nothing Then
.Value = Sheets(1).Range("A2")
.Document.forms(0).submit
End If
End With
End With
Set ieApp = Nothing
Set htmlDoc = Nothing
Set htmlInput = Nothing
End Sub

Anzeige
Man muss die URL richtig aufbauen
01.09.2019 23:39:47
Zwenn
Hallo Simon,
zwar habe ich nicht verstanden, was der Unterschied zwischen der privaten und professionellen Anzeige ist, habe aber Deine Vorgabe verwendet. Wie Fennek schon schrieb, müssen solche Sachen immer individuell programmiert werden. Da ich Deine Tabelle nicht kenne, habe ich Dir in der angehängten Datei eine Beispieltabelle mit einem Makro, dass die Werte ausliest angelegt. Das kannst Du einfach auf Deine Datei anpassen.
Am Anfang dachte ich, es funktioniert nicht auf die Schnelle Deinen Wunsch zu erfüllen, da ich erst nicht gesehen habe, wie man den richtigen Tab aufruft. Aber sowohl die FondNr, wie auch den anzuzeigenden Tab kann man in der URL mit übergeben und damit wird dann direkt die Seite geladen, wie sie zum Auslesen des Wertes gebraucht wird.
Das ganze Teil ist ziemlich "zerskriptet", also es wird viel über JavaScript gemacht. Unter anderem werden die eigentlichen Werte erst nachgeladen, wenn die Seite drumrum vorher fertig geladen wurde. Deshalb muss eine manuelle Pause eingelegt werden, um das Laden der Werte sicher zu stellen. Ich habe das auf 5 Sekunden voreingestellt. Die Zeit kannst Du ggf. anpassen, damit es schneller geht. Es wird jedoch einfach gar nix eingetragen, wenn nix ausgelesen werden konnte.
Am Anfang des Makros kannst Du den Tabellennamen und die Spalten in den Konstanten für Deine Zwecke anpassen. Im Verlauf des Makros wird dann überall mit den dort festgelegten Werten gearbeitet. Ich denke damit solltest Du klar kommen.
Es werden immer alle Nummern in der Tabelle abgearbeitet, wobei bestehende Werte in einer Zeile einfach überschrieben werden. Bevor Du das Makro verwendest, musst Du eine der Nummern manuell im Internet Explorer aufrufen und die von Rob angesprochene Datenschutzerklärung und die Auswahl für professionell treffen. Das zu automatisieren ist vergebene Liebesmüh, weil es nur ein einziges Mal gebraucht wird. Anschließend werden die Seiten immer direkt geladen. Allerdings darf der Internet Explorer beim Beenden seinen Cache und seine Coockies nicht löschen. Das ist aber die Voreinstellung.
Es geht nur über den IE. Anders als Rob, verwende ich statt early binding für solche Makros immer late binding. Du musst erstmal nicht wissen was das bedeutet. Mit early binding muss man aber bestimmte Verweise setzen, damit es funktioniert und hat dann beim Programmieren andere Vorteile. Aber wie gesagt, das spielt keine weitere Rolle, ich schreibe das nur, falls Du Dich über den Unterschied zwischen Robs und meinem Ansatz wunderst.
Hier ist die Datei:
https://www.herber.de/bbs/user/131773.xlsm
Viele Grüße,
Zwenn
Anzeige
AW: Man muss die URL richtig aufbauen
02.09.2019 12:44:43
Rob
Hallo Zwenn,
sehr schön gelöst!
Ich fall vom Stuhl, dass mit .innerText der Wert aus OFST452200 so einfach zu beziehen ist. :-) Hab umständlicher Weise versucht eine weitere Objektvariable zu deklarieren und über ne Funktion den Wert zu beziehen etc, was nicht wirklich geklappt hat...hgnh...
Vllt noch zwei Anmerkungen, wenn Du erlaubst: Die Funktionen hätte ich als Private deklariert, _ sonst kannst Du sie auch über das Worksheet aufrufen. Den .innerText kannst Du dann auch direkt in die Funktions-Variable z.B.

OngoingCharchesHolen (DOMDokument) as String
übergeben:

OngoingCharchesHolen = knotenOngoingCharches.innerText
, dann sparst Du dir noch zwei Zeilen Code.
LG,
Rob
Anzeige
AW: Man muss die URL richtig aufbauen
03.09.2019 00:15:28
Zwenn
Hallo Rob,
danke für das Lob. Das man mal Tomaten auf den Augen hat ist normal denke ich und macht uns ja auch menschlich :-) Von daher gesehen brauchst Du nicht vom Stuhl fallen.
Anmerkungen sind immer gut. Schließlich bin ich auch hier um etwas zu lernen und habe selbst auch schon viel von den Gedanken und Erklärungen anderer profitiert. Die Funktionen als Private zu deklarieren wäre sicher konsequenter, das stimmt. Wenn ich den Rest Deiner Einlassung richtig verstanden habe, bleibe ich aber lieber bei meiner Lösung und begründe das folgendermaßen:
  • Mir ist klar, dass es eine Art Wettbewerb unter Entwicklern gibt, wer den kürzesten Code schreiben kann, um eine bestimmte Aufgabe auszuführen. Ich finde das aber auch im Kontext eines Forums aus zwei Gründen kontraproduktiv:
    1. Die fragende Person ist oft recht neu in der Materie, was man auch an Fragestellungen sieht, die in vielen Fällen viel zu wenig Informationen für eine Lösung bieten. Da finde ich die ein oder andere Zeile mehr im Code hilfreich, die eigentlich unnötig ist.
    2. Für mich selbst habe ich rausgefunden, dass nicht nur Kommentare und sprechende Variablen hilfreich sind, sondern auch, wenn man z.B. Variablen einführt, die man auch vermeiden könnte, wenn man den Wert direkt berechnet oder übergibt. Das ist der Lesbarkeit des Codes aber abträglich, wenn man da nach einiger Zeit nochmal ran muss oder gar jemand anders sich damit beschäftigen muss. Für die Laufzeit sind solche Mehrzeilen absolut belanglos, da es sich um "popelige" Sequenzerweiterungen handelt, die nicht ins Gewicht fallen. (Bezogen auf den Kern Strukturierter Programmierung, dass jedes Problem sich durch Sequenz, Entscheidung und Iteration und ohne Sprünge lösen lässt.)

  • Die von Dir genannte Lösung scheint kürzer und direkter zu sein. Sie unterschlägt aber einen sehr wichtigen Fall, der eintreten kann. Was passiert bei Deinem Vorschlag, wenn kein Wert ausgelesen werden kann? Bei meiner Funktion wird dann ein Leerstring zurück gegeben. Das ist nicht direkt ersichtlich und wenn man von einer Sprache wie C kommt, auch zunächst nicht verständlich.
    In C ist es so, dass einer definierten Variable auch ein Speicherbereich zugewiesen wird. Da die Variable aber nur definiert und nicht deklariert wird, hat sie eben den Wert, der grade an der zugewiesenen Speicherstelle steht. In VBA werden Variablen aber deklariert. Der Unterschied ist, dass nicht nur der Speicherbedarf zur Verfügung gestellt wird, sondern dass es auch einen definierten Ausgangswert gibt. Ja, ich weiß, dass es da eine ewige Diskussion um diese Begriffe gibt und dass es auch nicht nur Variablen, sondern auch Prozeduren/ Methoden betrifft. Aber so habe ich es mal gelernt. Das ist Deinem Kommentar dazu vorangestellt Luc ;-)
    In VBA ist es so, dass jede Variable einen Standardwert hat, die mit DIM deklariert wurde. Zahlentypen werden mit 0 vorbelegt, Boolean mit False und Strings sind leer. Das finde ich sehr angenehm und nutze das in meiner Funktion aus. Die Variable muss nicht als Leerstring initialisiert werden, gibt aber ganau diesen zurück, wenn mit der Zeile
    Set knotenOngoingCharches = DOMDokument.getElementsByClassName("member-only OFST452200")(0)
    

    keine NodeList gebildet werden konnte. Dann wird in der Folge durch
    If Not knotenOngoingCharches Is Nothing Then
    

    nämlich gar nicht erst versucht den Wert auszulesen, der mit Deinem Vorgehen aber auf jeden Fall versucht würde zu holen, was zu einem Laufzeitfehler führt. Das kann man natürlich auch anders verhindern, aber die zwei eingesparten Zeilen sind dann schnell eingeholt.

  • Falls ich Deinen Codevorschlag falsch verstanden habe, freue ich mich über eine ausführlichere Beschreibung, was Du meinst. Dann hat mein Grips nicht ausgereicht ;-)
    Viele Grüße,
    Zwenn
    Anzeige
    AW: Man muss die URL richtig aufbauen
    03.09.2019 08:33:09
    Rob
    Hi Zwenn,
    wenn kein String in die entsprechende Variable übergeben werden kann, führt das in VBA zu keinem Laufzeitfehler. Du schreibst ja selber, dass die Variablen durch das Deklarieren/Definieren (darunter verstehe ich dasselbe - also Dim etc. Siehe auch Microsoft.com) vorbelegt sind. Nur bei Objekt-Variablen kann es bei der Zuweisung (also Set etc) zu Laufzeitfehlern kommen, wenn der Variablen kein Objekt zugewiesen werden kann, deshalb hast Du auch richtig geschrieben:
    If Not knotenOngoingCharches Is Nothing Then
    
    Aber mit
    
    Set knotenOngoingCharches = DOMDokument.getElementsByClassName("member-only OFST452200")(0)
    
    wird kein String-Wert ausgelesen. Damit wird nur das Objekt der Variablen zugewiesen. Erst anschließend wird mit der 'Klassen'-Methode .innerText der String-Wert in Deine String-Variable übergeben.
    Ich gebe Dir aber recht, dass fürs grundsätzliche Verständnis eine Variable mehr hilfreich sein kann.
    LG, Rob
    Anzeige
    AW: Man muss die URL richtig aufbauen
    03.09.2019 12:30:48
    Zwenn
    Hallo Rob,
    wie das DOM funktioniert habe ich für meine Zwecke ganz gut verinnerlicht denke ich, habe Dich aber wahrscheinlich doch missverstanden. Ich dachte Du würdest die innerText-Methode einfach pauschal am Ende der Funktion aufrufen, um den Wert direkt zu übergeben. Mein Gedanke dazu war, wenn kein Objekt mit Set erzeugt werden konnte, dann gibt es natürlich auch keine Methode innerText, die man aufrufen könnte. Deshalb schrieb ich, es gibt einen Laufzeitfehler, wenn nix ausgelesen werden konnte.
    Sorry für meine ausführlichen "Erklärungen". Die wirken als halte ich Dich für dämlich. So ist es aber nicht gemeint. Ich neige nur manchmal dazu Dinge ausführlicher darzulegen, als notwendig.
    Viele Grüße,
    Zwenn
    Anzeige
    AW: Man muss die URL richtig aufbauen
    03.09.2019 09:40:38
    Simon
    Wow. Danke Zwenn!
    Ich werde es nun versuchen auf mein Excelfile zu übertragen und gleich testen.
    Aber noch ein paar Fragen, da es mich einfach interessiert:
    1) Wieso funktioniert dieser Code nur über den IE und z.B. nicht auch über Firefox oder einen anderen Web Browser? Zum einen sicher, weil du den IE im Code eingebaut hast, aber gibt es da noch einen anderen Grund?
    2) Die Wartezeit von 5 Sek. ist nicht unbedingt notwendig, wenn ich vorher zuerst die Seite richtig lade und erst dann das Makro aus
    führe, richtig?
    Beste Grüsse
    AW: Man muss die URL richtig aufbauen
    03.09.2019 12:14:36
    Zwenn
    Hallo Simon,
    es funktioniert nur über den IE, weil er der einzige Browser ist, der sich direkt aus VBA heraus Fernsteuern lässt. Es ist ja ein ganz anderes Programm und um andere Software direkt automatisch bedienen zu können, braucht sie eine COM-Schnittstelle, die von den Browsern halt nur der IE implementiert hat.
    COM steht dabei für Component Object Model und wurde von Microsoft für genau solche Dinge entwickelt. Genauere Infos findes Du bei Interesse hier:
    http://de.wikipedia.org/wiki/Component_Object_Model
    Um andere Browser zu steuern gibt es eine Schnittstelle namens Selenium. Die habe ich aber noch nie verwendet. Da sie extra installiert werden muss, ist der Einsatz bei vielen Leuten aufgrund der Richtlinien in den Firmen auch gar nicht möglich.
    Weitere Infos zu Selenium findest Du unter folgenden beiden Links:
    http://www.seleniumhq.org/
    http://de.wikipedia.org/wiki/Selenium
    Zu Deiner Frage der Pause stelle ich die Gegenfrage, hast Du es ausprobiert? ;-)
    Der Mechanismus beim Laden einer Seite im IE über VBA ist folgender:
    - Instanzieren des zu verwendenden IE-Objektes (ist im Makro dann eine Objektvariable mit dem Namen browser)
    - An browser wird die URL übergeben und geladen
    - Das Makro soll so lange warten, bis die Seite fertig geladen ist
    Der letzte Punkt ist Kern Deiner Frage. Das Warten wird mit einer Schleife bewerkstelligt, die so lange läuft, bis browser seinen readyState mit 4 zurückmeldet. Das die gesamte Schleife in einer Zeile steht ist durch die Doppelpunkte möglich. Diese bedeuten in VBA, dass ein neuer Befehl folgt, als würde es sich um einen Zeilenumbruch handeln. In der Regel wird Code dadurch unübersichtlicher, aber in diesem Fall ist das eine Standardzeile, die ich immer wieder genau so in diesem Kontext verwende:
    Do Until browser.readyState = 4: DoEvents: Loop
    

    Was readyState bedeutet ist noch leicht zu verstehen, die 4 hingegen nicht. Die 4 steht für complete bzw done. Wie man sich leicht vorstellen kann, muss es also noch andere Zustände geben. Leider hat MS einiges an seinen Seiten zum IE und der Programmierung dazu umgestellt oder komplett entfernt. Deshalb ein Link zu Mozilla für weitere Informationen:
    http://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/readyState
    Moderne Internetseiten sind aber trotzdem sie readyState = 4 zurückgeben nicht komplett geladen. Die Grundseite wurde komplett geladen, es gibt aber oft Container, in die die eigentliche Infomation nachgeladen wird, also der dynamische Teil einer Seite. So ist es auch bei "Deiner" Seite. Wenn Du die Sichtbarkeit von browser auf True umstellst, kannst Du das beobachten. Nachdem die eigentliche Seite geladen wurde, erscheint ein sich drehender oranger Kreis (sage ich mal) und kurz danach erscheinen die abzugreifenden Informationen.
    Die Dauer des sich drehenden Kreises habe ich recht großzügig mit 5 Sekunden ausgelegt. Mir ist keine Möglichkeit bekannt, mit der man die exakte Dauer abpassen kann, weil die Abfrage auf readyState nach meiner Erfahrung an solchen Stellen nicht noch einmal funktioniert. Also muss eine manuelle Pause eingelegt werden, weil sonst noch keine Informationen da sind, die ausgelesen werden könnten. Es gibt zwar noch eine Möglichkeit das Laden der Seite abzufragen, nämlich indem allgemein geprüft wird, ob der Browser noch busy (also beschäftigt) ist. Aber auch damit bin ich in der Vergangenheit nicht ans gewünschte Ziel gekommen.
    Deine Tabelle würde sich also mit Daten und Uhrzeiten füllen, aber mit keinen ausgelesenen Informationen. Diese Pause kann man nur abschätzen und ausprobieren. Ich würde da immer 1 oder 2 Sekunden Sicherheit einbauen, falls der Server auf der anderen Seite mal nicht so schnell antwortet wie gewohnt. Um diese manuelle Pause einzulegen, steht die folgende Zeile im Makro, die ab jetzt (Now) für 5 Sekunden Pause sorgt:
    Application.Wait (Now + TimeSerial(0, 0, 5))
    
    Man kann die Grundseite auch nicht einmal laden und dann immer nur die Informationen für die Nummern nachladen. Für jede Nummer wird in einer Schleife ein neuer IE mit Namen browser instanziert, der vor dem nächsten Schleifendurchlauf wieder geschlossen wird. Das ist zwar rein theoretisch nicht nötig, aber in der Vergangenheit reagierte der IE oft instabil, wenn man viele Seitenaufrufe in der gleichen Instanz durchführte.
    Ich hoffe Du bist jetzt nicht noch verwirrter als vorher ;-)
    Viele Grüße,
    Zwenn
    Anzeige

    304 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige