使用VBA从网页中获取数据 [英] Get data out of a webpage with VBA

问题描述

我正在尝试从此网页中获取一些数据:

I'm trying to get some data out of this Webpage:

http://www.vodafone.de/privat/handys-tablets-tarife/smartphone-tarife.html

我想在每个智能手机和每种合同类型的正确位置上显示整个表格.

I want to have the whole table on the right site for every Smartphone and every contract type.

一次性付款:Anschlusspreis

one time payment for Setting up: Anschlusspreis

一次性付款:智能手机

总金额:Gesamt

total amount: Gesamt

每月按合同付款:Basispreis

Monthly payment for the contract: Basispreis

每月按手机付款:智能手机-祖扎伦

Monthly payment for the mobile phone: Smartphone-Zuzahlung

所有内容都存储在JavaScript部分中，这是一个很大的字母.

This is all stored in the JavaScript part which is a huge amout of letters.

我正在尝试使用Excel VBA:

I´m trying to use Excel VBA:

Sub Button1_Click()
  'On Error GoTo Errorhandler
  Dim ie As Object, doc As Object, rng As Object, ticker As String, quote As String

  Set ie = CreateObject("InternetExplorer.Application")
  i = 1

  'Application.StatusBar = "Your request is loading. Please wait..."

  ie.navigate "http://www.vodafone.de/privat/handys-tablets-tarife/smartphone-tarife.html"
  'ie.navigate ticker

  Do
    DoEvents
  Loop Until ie.readyState = READYSTATE_COMPLETE

  Set doc = ie.document

  quote = doc.getElementsByID("connectionFeeVal").innerText
  Cells(3, 3).Value = quote

  MsgBox ("done")

'Errorhandler:
  'If Err = 91 Then MsgBox "Error Message"

  ie.Application.Quit
End Sub

但是它不断在"DoEvents"处循环播放.

But it is continuously Looping at "DoEvents".

有人知道为什么以及如何解决这个问题，也许还有另一个想法如何将所有这些数据从此页面中取出.

Does someone have an idea why and how I can solve this and maybe another idea how to get all this data out of this page.

谢谢.

推荐答案

除了使用IE自动化之外，您还可以使用http请求对象:

instead of using the IE automation you can also use a http request object:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "GET", "http://www.vodafone.de/privat/handys-tablets-tarife/smartphone-tarife.html"
oRequest.Send
MsgBox oRequest.ResponseText

速度更快，并且不需要IE解决方案那么多的资源

it's faster and doesn't need as many ressources as the solution with the IE

如果您位于代理服务器的后面，则可以使用以下内容:

if you are behind a proxy server you can use something like this:

Const HTTPREQUEST_PROXYSETTING_PROXY = 2
Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.setProxy HTTPREQUEST_PROXYSETTING_PROXY, "http://proxy.intern:8080"
oRequest.Open "GET", "http://www.vodafone.de/privat/handys-tablets-tarife/smartphone-tarife.html"
oRequest.Send
MsgBox oRequest.ResponseText

当然，您必须将代理调整为您的值.

of course you have to adjust the proxy to your values.

如果您对德语页面有兴趣，这里还会提供德语简短说明:

As you are interessted in a german page here also a short explanation in german language: http://cboden.de/softwareentwicklung/vba/tipps-tricks/27-webseite-per-vba-ansprechen There is also explained how to pass values of a form to the webserver which might also be helpfull for you.

这篇关于使用VBA从网页中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！