如何使用VBA读取IE表文本? [英] How to read a IE table text with VBA?

问题描述

我正在尝试编写vba代码以遵循此过程:

I am trying to write a vba code in order to follow this process:

1. 自动填写Web表单并提交(新网页打开，并显示答案查找新网页的地址(因为我需要阅读此内容)

   Find the address of the new webpage (because i will need to read this content)

   读取html表的特定单元格(公司名称)

   Read a specific cell of the html table (the name of the company)

   您可以尝试以FR(FRANCE)和27435044714的增值税号手动提交. 它将返回一个包含公司名称的页面.

   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.

   基本上，我正在努力提取公司名称.

   Basically I am struggling to extract the name of the company.

   第1步运作良好，并且与第2步& 3. 第2步和第3步在同一Sub上:

   Step 1 works very well and is independent from step 2 & 3. Step 2 and step 3 are on a same Sub:

   宏从第2步开始(查找打开的网页)

   The macro starts with the step 2 (finding the opened webpage)

   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:
   

   然后，我继续执行步骤3，以提取文本.

   Then, I proceed to the step 3 in order to extract the text.

   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
   

   我的问题:宏在行上停止(运行时错误91):

   My problem: the macro stops on the line (Runtime error 91):

   result = xobj.innerText

   result = xobj.innerText

   这似乎来自上一行

   设置xobj = xobj.getElementsByTagName("td").Item(0)

   Set 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 !

   推荐答案

   尝试一下

   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
   

   这篇关于如何使用VBA读取IE表文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！