使用VBA Excel从嵌入式网页中提取数据

2 回答

• 0

  不确定我是否完全理解你的问题，但这里有一些代码可以获得感兴趣的表格后面的源代码 . 您可以使用“instr”和“mid”等功能提取感兴趣的数据

  ' open IE, navigate to the website of interest and loop until fully loaded
  Set ie = CreateObject("InternetExplorer.Application")
  
  With ie
      .Visible = True
      .navigate "http://support.automation.siemens.com/WW/view/en/7224052"
      .Top = 50
      .Left = 530
      .Height = 400
      .Width = 400
  
  Do Until Not ie.Busy And ie.ReadyState = 4
      DoEvents
  Loop
  
  End With
  
  ' Assign the source code behind the page to a variable
  my_var = ie.document.frames(3).document.DocumentElement.innerhtml
  
  ' Extract the url for the "Product life cycle" table
  pos_1 = InStr(1, my_var, "product life cycle", vbTextCompare)
  pos_2 = InStr(pos_1, my_var, "/WW/llisapi", vbTextCompare)
  pos_3 = InStr(pos_2, my_var, """><", vbTextCompare)
  pos_4 = InStr(pos_3, my_var, """/>", vbTextCompare)
  table_url = Mid(my_var, pos_2, pos_3 - pos_2)
  table_url = Replace(table_url, "amp;", "", 1, -1, vbTextCompare)
  table_url = "http://support.automation.siemens.com" & table_url
  
  ' navigate to the table url
  ie.navigate table_url
  
  Do Until Not ie.Busy And ie.ReadyState = 4
      DoEvents
  Loop
  
  ' assign the source code for this page to a variable and extract the desired information
  my_var2 = ie.document.body.innerhtml
  pos_1 = InStr(1, my_var2, "ET 200X, basic modules,", vbTextCompare)
  
  ' close ie
  ie.Quit
  

  回复于 2024-04-28T15:18:57+08:00
• 0

  我在让ron的代码工作时遇到了问题，我认为因为IE不能轻松使用帧 . 下面是一些代码，它们将从您提到的表中提取一些数据，到目前为止它还没有处理图表 .

  Sub FrameStrip()
  
  
      Dim oFrames As Object
      Dim tdelements As Object
      Dim tdElement As Object
      Dim oFrame As MSHTML.HTMLFrameElement
      Dim oElement As Object
      Dim sString As String
      Dim myVar As Variant
      Dim sLinks() As String
      Dim i As Integer
      Dim bfound As Boolean
      Dim url As String
      Dim oIE As InternetExplorer
  
  
      Set oIE = New InternetExplorer
  
      url = "http://support.automation.siemens.com/WW/view/en/7224052"
  
      'Set address for use with relative source names
      myVar = Split(url, "/")
      sString = myVar(0) & "//" & myVar(2)
  
      oIE.navigate url
      oIE.Visible = True
  
      Do Until (oIE.readyState = 4 And Not oIE.Busy)
          DoEvents
      Loop
  
      Set oFrames = oIE.document.getElementsByTagName("frame")
      ReDim sLinks(oFrames.Length)
  
      'Get the source locations for each frame
      i = 0
      For Each oFrame In oFrames
  
         sLinks(i) = sString & (oFrame.getAttribute("src"))
  
         i = i + 1
      Next oFrame
  
      'Go through each frame to find the table
      i = 0
      bfound = False
      Do While i < UBound(sLinks) And bfound = False
          oIE.navigate sLinks(i)
          Do Until (oIE.readyState = 4 And Not oIE.Busy)
              DoEvents
          Loop
  
          Set oElement = oIE.document.getElementById("produktangaben")
          bfound = IsSet(oElement)
          i = i + 1
      Loop
  
      Set tdelements = oElement.getElementsByTagName("td")
      'Display information about table
      sString = ""
      For Each tdElement In tdelements
         Debug.Print tdElement.innerText
         sString = sString & tdElement.innerText
      Next tdElement
  
  
  End Sub
  
  Function IsSet(ByRef oElement As Object) As Boolean
  
      Dim tdelements As Object
      Dim bSet As Boolean
  
      bSet = True
  
      On Error GoTo ErrorSet
      Set tdelements = oElement.getElementsByTagName("td")
      On Error GoTo 0
  
  
  Cleanup:
  
      On Error Resume Next
      Set tdelements = Nothing
      On Error GoTo 0
  
      IsSet = bSet
  
  Exit Function
  
  ErrorSet:
      bSet = False
      GoTo Cleanup:
  End Function
  

  回复于 2024-04-28T15:18:57+08:00