excel - 使用 VBA 和 IE 从 Internet HTML 页面解析数据

Question

披露:我只是一名办公室文员，对 VBA 和 HTML 非常陌生。希望你对我有耐心。我非常感谢任何指导和帮助。希望我正确格式化它。

昨天我花了一整天时间尝试从 Intranet 网页导入信息以自动复制和粘贴路由。从长远来看，这确实会有所帮助。

由于 power-query 似乎没有看到我需要的表，我认为唯一的选择是使用 VBA。 MsServer 工具完美抓取页面，但令我失望的是页面出现错误，因为它需要先授权才能访问它。

我认为使用 IE 应该可以工作，因为 IE 在 cookie 中有登录信息。

我到这里为止。

 Sub ExtractFromEndeca() Dim ie As InternetExplorer Dim html As
 IHTMLDocument Set ie = CreateObject("InternetExplorer.Application")
 ie.Visible = False 
 ie.Navigate "intranet address"
 While ie.Busy
     DoEvents Wend While ie.ReadyState < 4
     DoEvents Wend
     Set Doc = CreateObject("htmlfile")
     Set Doc = ie.document
     Set Data = Doc.getElementById("findSimilarOptions2")
     Sheet1.Cells(1, 1) = Data
     ie.Quit Set ie = Nothing

 ThisWorkbook.Sheets(1).Cells(1, 1) = Data

 End Sub

结果是 Cell A1 中的 [object] ，仅此而已，我不明白我是否通过登录。

这是我要抓取的页面片段。理想情况下，这些数据将作为表格输出。

   <td valign="top" id="findSimilarOptions2">
<div class="subtitle">Part Attributes</div>
    <input type="checkbox" id="n_200012" value="-19192896" NAME="n_200012">
    <b>
    ASSY TYPE</b>&nbsp;>
    Component<br>

    <input type="checkbox" id="n_200013" value="-18148519" NAME="n_200013">
    <b>
    PARAMETER I NEED(1)</b>&nbsp;>
    VALUE I NEED(1)<br>

    <input type="checkbox" id="n_200006" value="-20823731" NAME="n_200006">
    <b>
    PARAMETER I NEED(2)</b>&nbsp;>
    VALUE I NEED(2)<br>

    <input type="checkbox" id="n_200006" value="-20823618" NAME="n_200006">
    <b>
    PARAMETER I NEED(3)</b>&nbsp;>
    VALUE I NEED(3)<br>

    <input type="checkbox" id="n_200006" value="-20823586" NAME="n_200006">
    <b>
    PARAMETER I NEED(4)</b>&nbsp;>
    VALUE I NEED(4)<br>
    ...

Answer 1

请阅读我在以下代码中的评论:

'Use the following line in every module head
'It forces you to define all variables
Option Explicit

Sub ExtractFromEndeca()

Dim ie As InternetExplorer
Dim doc As IHTMLDocument 'You don't use html in your code, but doc
Dim data As HTMLHtmlElement 'You should define all variables
Dim singleData As HTMLHtmlElement 'New variable
Dim row As Long 'New variable

  row = 1 'First row for output in Excel table

  'Set ie = CreateObject("InternetExplorer.Application") 'This could be problematic on the intranet due to security guidelines
  Set ie = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}") 'Try this instead to initialize the IE
  ie.Visible = True 'This property should be True while development
  ie.Navigate "intranet address"
  'While ie.Busy: DoEvents: Wend 'You don't need this line
  While ie.ReadyState <> 4: DoEvents: Wend
  'Set Doc = CreateObject("htmlfile") 'You don't need this line
  Set doc = ie.document
  Set data = doc.getElementById("findSimilarOptions2").getElementsByTagName("input")

  'Data is only a reference to an object
  'You want the text information which lies in the value attributes of each input tag
  For Each singleData In data
    Sheet1.Cells(row, 1) = data.Value
    row = row + 1
  Next singleData

  'Clean up
  '(Automatic after development has finished)
  'ie.Quit
  'Set ie = Nothing
End Sub