Excel 2016 - Daten aus Website (und deren Verlinkungen) importieren

HoneyBadger · 1 Feb. 2016

Hi,

suche mich gerade dumm und dämlich.
Versuche Daten aus dem Web in eine Exceltabelle zu ziehen. Problem ist nur, dass die Daten größtenteils aus weiterführenden Links kommen.

Kurz die Erklärung:
Hauptdatensatz auf www.xyz.de
erweiterte Daten auf www.xyz.de/1.html

Die erweiterten Daten sind fortlaufend. von www.xyz.de/1.html bis www.xyz.de/999.html

Die Standardwebafrage in Excel funktioniert für den Hauptdatensatz problemlos. Die erweiterten Infos müsste ich nun allerdings manuell auswerten. Das wären in meinem Fall knapp 900 Seiten. Ergo eine Menge vermeidbarer Fleißarbeit. Gerade da die Daten auf dieser Seite fortlaufend nummeriert sind, sollte das doch eigentlich auch einfacher machbar sein? Ich brauch immer die gleiche Information auf den gleichen Stellen der einzelnen Links.

Für einen Tipp wäre ich sehr dankbar.

drfuture · 1 Feb. 2016

Eigentlich über ein "Simples" VBA-Makro,
die winhttp Schnittstelle ansprechen

You do not have permission to view link please Anmelden or Registrieren

- Daten auswerten und in Zelle schreiben.
Die URL in einer Schleife hochzählen ...

HoneyBadger · 1 Feb. 2016

Sowas dachte ich mir auch schon. Meine Visual Basic Kenntnisse sind nur leider nicht die besten.

obedi · 1 Feb. 2016

Stand kürzlich vor einer ähnlichen Aufgabenstellung und da Excel (2013) nur Daten aus Tabellen holen kann habe ich mir dann über

You do not have permission to view link please Anmelden or Registrieren

(mit dem Scite Editor) und einer für AutoIt geschriebenen Firefox Bibliothek (

You do not have permission to view link please Anmelden or Registrieren

) geholfen (benötigt wiederum das

You do not have permission to view link please Anmelden or Registrieren

).

Zugegeben als nicht-Programmierer man braucht ein wenig um einzusteigen, kann dann mit etwas Übung fast alles automatisieren - nutze meine AutoIt Scripte regelmäßig, angefangen bei simplen Dingen wie Scanner Voreinstellungen automatisch setzen (da die Software sich diese nicht merket).

ein paar Beispiele für das Prinzip (einfach so nicht zu gebrauchen):

Code:

#include <FF.au3>
_FFStart("www.xyz.de", Default, 2)
_FFConnect(Default, Default, 3000)

If _FFIsConnected() Then
	_FFFrameEnter("myFrame", "name")
	_FFSetValue("myvalue", "hhid", "name")
	;_FFClick("input", "tag", 3)
	_FFFormsubmit(0)
	_FFStart("http://www.xyz.de/1.html ", Default, 2)
	_FFDisConnect()
EndIf

Code:

Opt("WinTitleMatchMode", 2)
Opt("CaretCoordMode", 0)

for $i = 1 to 1
	WinActivate("Rezept","")
	WinWaitActive("Rezept","")
	MouseClick("left",660,425,1)
	MouseUp("left")
	sleep(1000)
	Mousedown("left")
	mousemove(650,570,3)
	sleep(1500)
	mousemove(650,528,3)
	MouseUp("left")
	Send("{CTRLDOWN}c{CTRLUP}")
	sleep(100)
	WinActivate("Microsoft Excel - EatSmarter Rezepte mit Bewertungen.xlsx","")
	Send("{APPSKEY}")
	Send("{DOWN}{DOWN}{DOWN}{ENTER}")
	WinActivate("Inhalte einfügen","")
	Send("{DOWN}{DOWN}{ENTER}{DOWN}")
	WinActivate("Microsoft Excel - EatSmarter Rezepte mit Bewertungen.xlsx","")
next

Code:

WinActivate("Wurzelimperium - Google Chrome","")

for $j = 2 to 13			; gesamte Feldbreite nach unten  max: 12
	$y = 320 + ($j*41)		; erster Y-Wert (minus ein Feld)
	For $i = 0 To 17		; gesamte Feldbreite nach rechts max: 17
		$x = 710 + ($i*41)	; erster X-Wert (minus ein Feld)
		MouseClick("left",$x,$y,1,2)
	Next
Next

; ein Feld je 41px hoch und breit

Gruß
obedi

drfuture · 1 Feb. 2016

öhm nein siehe oben @obedi - das Gleiche bekommst du auch über vba und winhttp hin ...

@TS mit dem Code können wir dir ja Theo. ein wenig helfen - nur fehlen dazu jede Menge Infos

Wie man in eine Zelle schreibt findest du z.B. sehr leicht mit dem Makro-Editor heraus.
Wie eine Schleife funktioniert > google - schleifen in vba

HoneyBadger · 2 Feb. 2016

You do not have permission to view link please Anmelden or Registrieren

Hi drfuture,

versuche mich da nun schon den ganzen morgen dran zu tasten.

Das grobe Prinzip der Schleifen, habe ich schon mal verstanden und auch hinbekommen.

[src=vb]Sub Schleife()

Dim intzaehler As Integer

For intzaehler = 1 To 900

Cells(intzaehler, 1).Value = intzaehler

Next intzaehler

End Sub[/src]

Nun versuche ich die Datenabfrage zu basteln. Habe mich da mal durch´s Netz geklickt und versucht, mir was zu recht zu schustern.
Das schaut aktuell so aus, funktioniert aber noch nicht.

[src=vb]Sub b()

Dim IEApp As Object

Dim IEDocument As Object

Set IEApp = CreateObject("InternetExplorer.Application")

IEApp.Visible = False

IEApp.Navigate "www.zoo-infos.de/zoos/1.html"

Do: Loop Until IEApp.Busy = False

Do: Loop Until IEApp.Busy = False

Set IEDocument = IEApp.Document

Do: Loop Until IEDocument.ReadyState = "complete"

IEDocument.getElementById.Value = Ort

Do: Loop Until IEDocument.ReadyState = "complete"

For i = 0 To IEDocument.all.Length - 1

If IEDocument.all.Item(i).nodeName = "TABLE" Then

If IEDocument.all.Item(i).Rows.Length = 27 Then

j = 1

For k = 0 To IEDocument.all.Item(i).Rows.Length - 1

Cells(j, 1) = IEDocument.all.Item(i).Rows(k).Cells(0).innerText

Cells(j, 2) = IEDocument.all.Item(i).Rows(k).Cells(1).innerText

j = j + 1

Next

End If

End If

Next

Cells(1, 1) = Ort

IEApp.Quit

Set IEDocument = Nothing

Set IEApp = Nothing

End Sub[/src]

Ich brauche folgendes:

Auf der Seite www.zoo-infos.de ist rechts ein Menü. Dort kann man auf * alle Einträge * umstellen. Dann sieht man darunter alle möglichen Zoos in Deutschland. Klickt man auf den ersten, wird man auf

You do not have permission to view link please Anmelden or Registrieren

geleitet.
Dort findet man oben folgende Infos:

Name des Zoos
Ort und PLZ
Größe
Tierbestand
Eröffnungsjahr

Diese Daten brauche ich in einer Exceltabelle nebeneinander in Spalten.

Die Seiten sind dann fortlaufend nummeriert. :unknown:

Danke schon mal für deine Unterstützung.

drfuture · 2 Feb. 2016

Ohjee hab schon lange keine so schlecht Programmierte Seite gesehen - wobei ich fast vermute das das schon absichtlich so mies gemacht wurde um ein Auslesen zu verhindern....
Mit Excel / VBA kommst du da leider nicht weiter, Hintergrund ist das die Seite aus ~10 ineinander Verschachtelten Frames besteht die sich gegenseitig *schützen* - Sprich man kann dir URL die du versucht hast nicht direkt Aufrufen um an die Daten zu kommen sondern es wird eine Verschtelte Seite geladen durch die es aufwendig ist per Programm zu Navigieren (Nebenbei auch für Google, Google dürfte über die Webseite gar nichts finden)

Damit ein Sinnvoller Inhalt beim Aufruf der 1.html kommt muss man der Webseite vorgaugeln man würde die seite nicht von Dir persönlich aus - sondern intern von der Webseite aus aufrufen.

Das geht z.B. mit Powershell. Dazu musst du Windows 10 bzw. Powershell 5.0 installiert haben (lässt sich auch unter windows 7 als update installieren)

Dann habe ich dir folgendes Script geschrieben:

[src=bash]$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True

$Workbook = $ExcelApp.Workbooks.Add()
$Worksheet = $ExcelApp.Worksheets.Item(1)
$Worksheet.Range("A1").Value2 = "Name"
$Worksheet.Range("B1").Value2 = "Ort"
$Worksheet.Range("C1").Value2 = "Jahr"
$Worksheet.Range("D1").Value2 = "Größe"
$Worksheet.Range("E1").Value2 = "Arten"

$headervals = @{'Referer'='http://www.zoo-infos.de/set.html?/zoos/1.html';'Content-Type'='application/x-www-form-urlencoded'}
$response = Invoke-Webrequest -Uri ("http://www.zoo-infos.de/zoos/1.html") -Body $postParams -Method Get
$Seite = $response.Content

$seite -match "(e:<\/b>)(.*)( Tierbestand

"
$Groesse = $Matches[2].Trim()

$seite -match "(Eröffnungsjahr:<\/b> )([0-9]{4})"
$Jahr = $Matches[2].Trim()

$seite -match "(Tierbestand:<\/b>)(.*)( )"
$Arten = $Matches[2].Trim()

$seite -match "(<TITLE>)(.*)( bei)"
$Name = $Matches[2].Trim()

$seite -match "(Ort:<\/b> )(.*)( L)"
$Ort = $Matches[2].Trim()

$Worksheet.Range("A2").Value2 = $Name
$Worksheet.Range("B2").Value2 = $Ort
$Worksheet.Range("C2").Value2 = $Jahr
$Worksheet.Range("D2").Value2 = $Groesse
$Worksheet.Range("E2").Value2 = $Arten[/src]

Was noch fehlt ist eine Schleife drumrum ...
So für ein wenig Eigenleistung

HoneyBadger · 2 Feb. 2016

@drfuture:

Die Powershell habe ich glaube ich schon. Hatte mir mal Choco installiert. Den Code dann einfach da hineinkopieren und ausführen?
Die Schleife müsste ich dann so aufbauen, wie ich das oben beschrieben habe, richtig?

Irre, wie schnell du das aus dem Hut zauberst. Vielen Dank für die schnelle Unterstützung.

drfuture · 2 Feb. 2016

Die Version ist wichtig... Invoke-Webrequest gibt es erst seit version 4.

gib mal im Startmenü (vorausgesetzt du hast ein Betriebssystem ab Windows 7) "powershell" ein. das Starten und dort dann $PSVersionTable und enter.
Die Obige Aussage war etwas übertrieben, v4 reicht auch.

Den Inhalt der Datei am Besten in eine .ps1 Datei speichern und dann mit rechtslick > in Powershell ausführen starten.

für den Fall das das nicht funktioniert: Wie oben ein Powershellfenster öffnen, diesmal aber mit rechtslick > als Administrator.
dann im Fenster folgendes eingeben und enter drücken:

Set-ExecutionPolicy Unrestricted

und J

HoneyBadger · 3 Feb. 2016

Also meine PowerShell-Version ist 5 irgendwas. Versuche mich gerade darin die Schleife zu basteln.
Theoretisch müsste die ja beginnen, nachdem die Überschriften geschrieben wurden, richtig? Und dann im nächsten Schritt bei der URL und bei "A2" bis "E2" immer um einen nach oben Zählen. Habe das gerade (nach diversen anderen Varianten, die ich probiert habe und ebenso falsch waren) so probiert. Da zählt sich die Schleife allerdings plötzlich zu tode und spuckt immer wieder Fehlermeldungen aus. Die Überschriften kommen noch. Puh, gar nicht so einfach. :unknown:

[src=bash]
$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True

$Workbook = $ExcelApp.Workbooks.Add()
$Worksheet = $ExcelApp.Worksheets.Item(1)
$Worksheet.Range("A1").Value2 = "Name"
$Worksheet.Range("B1").Value2 = "Ort"
$Worksheet.Range("C1").Value2 = "Jahr"
$Worksheet.Range("D1").Value2 = "Größe"
$Worksheet.Range("E1").Value2 = "Arten"

$i=1..10; Do {$i++;$i}Until ($i -le 10)

$headervals = @{'Referer'='http://www.zoo-infos.de/set.html?/zoos/($=1).html';'Content-Type'='application/x-www-form-urlencoded'}
$response = Invoke-Webrequest -Uri ("http://www.zoo-infos.de/zoos/($i=1).html") -Body $postParams -Method Get
$Seite = $response.Content

$seite -match "(e:<\/b>)(.*)( Tierbestand

"
$Groesse = $Matches[2].Trim()

$seite -match "(Eröffnungsjahr:<\/b> )([0-9]{4})"
$Jahr = $Matches[2].Trim()

$seite -match "(Tierbestand:<\/b>)(.*)( )"
$Arten = $Matches[2].Trim()

$seite -match "(<TITLE>)(.*)( bei)"
$Name = $Matches[2].Trim()

$seite -match "(Ort:<\/b> )(.*)( L)"
$Ort = $Matches[2].Trim()

$j=2..11; Do {$j++;$j}Until ($j -le 1)

$Worksheet.Range("A($j=2)").Value2 = $Name
$Worksheet.Range("B($j=2)").Value2 = $Ort
$Worksheet.Range("C($j=2)").Value2 = $Jahr
$Worksheet.Range("D($j=2)").Value2 = $Groesse
$Worksheet.Range("E($j=2)").Value2 = $Arten

[/src]

drfuture · 3 Feb. 2016

War doch an sich schon recht gut... ;D

Problem ist immer die Wahl der *richtigen* Schleife - wobei es natürlich IMMER Zig Wege zum Ziel gibt.
Ich persönlich finde die Do.while und Do.Until schlicht zu unverständlich in der Benutzung

PowerShell kennt darüber hinaus zwei Formen von nicht-abweisenden Schleifen, nämlich do-while und do-until. In beiden Fällen werden die Anweisungen des Schleifenkörpers mindestens einmal ausgeführt, weil die Bedingung erst am Ende der Schleife steht.

Syntaktisch sind do-while und do-until identisch, der Unterschied ist logischer Natur: do-while läuft, solange die Bedingung wahr ist und bricht ab, wenn sie nicht mehr erfüllt wird. Bei do-until ist es genau umgekehrt: Sie endet, wenn die Schleifenbedingung den Wert TRUE annimmt. Die

für Schleifen die sich selber Hochzählen, eben wie bei dir *mache von 1 bis 10) ist For () für mich die Beste (und verständlichste) Wahl.

[src=bash]$ExcelApp = New-Object -ComObject Excel.Application # ComObjekt erstellen
$ExcelApp.Visible = $True

$Workbook = $ExcelApp.Workbooks.Add()
$Worksheet = $ExcelApp.Worksheets.Item(1)
$Worksheet.Range("A1").Value2 = "Name"
$Worksheet.Range("B1").Value2 = "Ort"
$Worksheet.Range("C1").Value2 = "Jahr"
$Worksheet.Range("D1").Value2 = "Größe"
$Worksheet.Range("E1").Value2 = "Arten"

for ($i=1; $i -le 10; $i++) {

$headervals = @{'Referer'='http://www.zoo-infos.de/set.html?/zoos/$i.html';'Content-Type'='application/x-www-form-urlencoded'}
$response = Invoke-Webrequest -Uri ("http://www.zoo-infos.de/zoos/$i.html") -Body $postParams -Method GET
$Seite = $response.Content

$seite -match "(e:<\/b>)(.*)( Tierbestand

"
$Groesse = $Matches[2].Trim()

$seite -match "(Eröffnungsjahr:<\/b> )([0-9]{4})"
$Jahr = $Matches[2].Trim()

$seite -match "(Tierbestand:<\/b> )([0-9]* {1})(.*)"
$Arten = $Matches[2].Trim()

$seite -match "(<TITLE>)(.*)( bei)"
$Name = $Matches[2].Trim()

$seite -match "(Ort:<\/b> )(.*)( L)"
$Ort = $Matches[2].Trim()

$c = $i + 1
$Worksheet.Range("A$c").Value2 = $Name
$Worksheet.Range("B$c").Value2 = $Ort
$Worksheet.Range("C$c").Value2 = $Jahr
$Worksheet.Range("D$c").Value2 = $Groesse
$Worksheet.Range("E$c").Value2 = $Arten

}[/src]

HoneyBadger · 3 Feb. 2016

Mhmmm,... irgendwie steig ich da noch nicht durch. Also was die Schleife machen soll und wie das Grundpinzip funktioniert, habe ich langsam begriffen. Nur wie mache ich das denn, dass die Schleife diese Variable in der Zeile hochzählt? Im Moment motzt die PowerShell bei mir irgendwas mit, dass das mit dem Null-Array nicht geht. Nen Array ist ja irgendwie ne Reihe?

drfuture · 3 Feb. 2016

in welcher Zeile?
Ich hab das bei mir schon gestartet - und hatte die ersten 10 Zoos dann in der Excel stehen...
Die Zelle wird in meinem Beispiel schon hochgezählt, du hast in $i die Schleifenzahl, braucht $i +1 (durch die Überschrift) das schreibe ich in $c (ginge auch ohne neue Variable aber ist halt so übersichtlicher). - und $c steht nach dem range() Powershell ersetzt in "" Anführungszeichen Variablen *direkt* - insofern es sich um einfache String-Variablen handelt.

HoneyBadger · 3 Feb. 2016

Der Fehler.

[src=bash]Invoke-WebRequest : Fehlendes Argument für den Parameter "Method". Geben Sie einen Parameter vom Typ
"Microsoft.PowerShell.Commands.WebRequestMethod" an, und versuchen Sie es erneut.
In Zeile:15 Zeichen:99
+ ... ri ("http://www.zoo-infos.de/zoos/$i.html") -Body $postParams -Method
+ ~~~~~~~
+ CategoryInfo : InvalidArgument: (

[Invoke-WebRequest], ParameterBindingException
+ FullyQualifiedErrorId : MissingArgument,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

False
Es ist nicht möglich, einen Index auf ein NULL-Array anzuwenden.
In Zeile:19 Zeichen:5
+ $Groesse = $Matches[2].Trim()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (

[], RuntimeException
+ FullyQualifiedErrorId : NullArray

False
Es ist nicht möglich, einen Index auf ein NULL-Array anzuwenden.
In Zeile:22 Zeichen:5
+ $Jahr = $Matches[2].Trim()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (

[], RuntimeException
+ FullyQualifiedErrorId : NullArray

False
Es ist nicht möglich, einen Index auf ein NULL-Array anzuwenden.
In Zeile:25 Zeichen:5
+ $Arten = $Matches[2].Trim()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (

[], RuntimeException
+ FullyQualifiedErrorId : NullArray

False
Es ist nicht möglich, einen Index auf ein NULL-Array anzuwenden.
In Zeile:28 Zeichen:5
+ $Name = $Matches[2].Trim()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (

[], RuntimeException
+ FullyQualifiedErrorId : NullArray

False
Es ist nicht möglich, einen Index auf ein NULL-Array anzuwenden.
In Zeile:31 Zeichen:5
+ $Ort = $Matches[2].Trim()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (

[], RuntimeException
+ FullyQualifiedErrorId : NullArray

Invoke-WebRequest : Fehlendes Argument für den Parameter "Method". Geben Sie einen Parameter vom Typ
"Microsoft.PowerShell.Commands.WebRequestMethod" an, und versuchen Sie es erneut.
In Zeile:15 Zeichen:99
+ ... ri ("http://www.zoo-infos.de/zoos/$i.html") -Body $postParams -Method
+ ~~~~~~~
+ CategoryInfo : InvalidArgument: (