如何使用 VBA 读取 IE 表格文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21688478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read a IE table text with VBA?
提问by user3294649
I am trying to write a vba code in order to follow this process:
我正在尝试编写 vba 代码以遵循此过程：
Automatic Web form fill in and submitting (a new web page opens with the answer http://ec.europa.eu/taxation_customs/vies/?locale=en)
Find the address of the new webpage (because i will need to read this content)
Read a specific cell of the html table (the name of the company)
自动填写和提交 Web 表单（一个新网页打开，答案为http://ec.europa.eu/taxation_customs/vies/?locale=en）
找到新网页的地址（因为我需要阅读这个内容）
读取 html 表格的特定单元格（公司名称）
You can try to manualy submit the for with the VAT number FR(FRANCE) and 27435044714. It will return a page including the name of the company.
您可以尝试手动提交增值税号为 FR(FRANCE) 和 27435044714 的 for。它将返回一个包含公司名称的页面。
Basically I am struggling to extract the name of the company.
基本上我正在努力提取公司的名称。
Step 1 works very well and is independent from step 2 & 3. Step 2 and step 3 are on a same Sub:
第 1 步运行良好，独立于第 2 步和第 3 步。第 2 步和第 3 步在同一个 Sub 上：
The macro starts with the step 2 (finding the opened webpage)
宏从第2步开始（找到打开的网页）
Sub recup()
Dim SWs As SHDocVw.ShellWindows, IE As SHDocVw.InternetExplorer
'Establish link to IE application
Set SWs = New SHDocVw.ShellWindows
For Each IE In SWs
If Left(IE.LocationURL, 4) <> "http" Then
GoTo autre
End If
Address = IE.LocationURL
GoTo vabene 'avoid explorer windows/etc this way
autre:
Next
vabene:
Then, I proceed to the step 3 in order to extract the text.
然后，我继续执行步骤 3 以提取文本。
IE.Visible = True
Dim xobj
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
Set xobj =IE.Document.getElementById("vatResponseFormTable").getElementsByClassName("labelStyle").Item(3)
Set xobj = xobj.getElementsByTagName("td").Item(0)
result = xobj.innerText
Set xobj = Nothing
IE.Quit
Set IE = Nothing
End Sub
My problem: the macro stops on the line (Runtime error 91):
我的问题：宏停止在线（运行时错误 91）：
result = xobj.innerText
结果 = xobj.innerText
This seems to come from the previous line
这似乎来自上一行
Set xobj = xobj.getElementsByTagName("td").Item(0)
设置 xobj = xobj.getElementsByTagName("td").Item(0)
I search a lot into the web and this forum (it helped me until this step). If you can help me that would save my week !
我在网上和这个论坛上搜索了很多（它帮助了我直到这一步）。如果你能帮助我，那将节省我的一周！
回答by Santosh
Try this
尝试这个
Sub getData()
'~~~~Variable declaration~~~~'
Dim IE As Object
Dim country As Object
Dim num As Object
Dim btn As Object
Dim tlb As Object, td As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.navigate "http://ec.europa.eu/taxation_customs/vies/?locale=en"
'Wait till page is loaded
Do While IE.readystate <> 4
DoEvents
Loop
Set country = IE.document.getelementbyid("countryCombobox")
country.Value = "FR" 'set the value for Member state
'Pause the code for 5 sec
Application.Wait Now + TimeSerial(0, 0, 5)
'
Set num = IE.document.getelementbyid("number")
num.Value = "27435044714" 'set the Vat number
Application.Wait Now + TimeSerial(0, 0, 5)
Set btn = IE.document.getelementbyid("submit")
btn.Click ' click the verify button
'Wait till page is loaded
Do While IE.readystate <> 4: DoEvents: Loop
'Pause the code for 5 sec
Application.Wait Now + TimeSerial(0, 0, 10)
Set tbl = IE.document.getelementbyid("vatResponseFormTable")
For Each td In tbl.getelementsbytagname("td")
If td.innerText = "Name" Then
MsgBox "Name : " & td.NextSibling.innerText
ElseIf td.innerText = "Address" Then
MsgBox "Address : " & td.NextSibling.innerText
ElseIf td.innerText = "Consultation Number" Then
MsgBox "Consultation Number : " & td.NextSibling.innerText
End If
Next
IE.Quit
Set IE = Nothing
End Sub
