使用VBA将特定表输入excel [英] get specific table into excel using VBA

问题描述

我正在寻找一种使用VBA将表格插入excel的方法.出于特定原因，不适合使用网络查询.

I am looking for a way to get a table into excel using VBA. Using a web query is not suitable for specific reasons.

到目前为止，我已经知道了

So far i have got this

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMUTop"
ie.Visible = True
While ie.Busy
DoEvents
Wend
ie.document.getelementbyid("param5").Value = "2014-04-12"
ie.document.getelementbyid("param6").Value = "8"
ie.document.getelementbyid("go_button").Click

打开的网页中有一个名为最大导出限制"的表格-位于页面的一半下方，位于左侧.

The web page that opens has a table within it called Maximum Export Limit - half way down the page and on the left hand side.

我找到了负责表的html代码部分，并设法将内部html手动复制到网页(只需复制和粘贴)，但是我该如何通过VBA做到这一点>

I found the part of the html code that is responsible for the table and manged to copy the inner html to the web page manually (just copy and pasting) however how do i do this via VBA>

@罗恩 到目前为止，我已经全部拥有了

@Ron So far i have this in all

Sub DataStuff()
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMUTop"
ie.Visible = True
While ie.Busy
DoEvents
Wend
ie.Document.getelementbyid("param5").Value = "2014-04-12"
ie.Document.getelementbyid("param6").Value = "8"
ie.Document.getelementbyid("go_button").Click

Set objShell = CreateObject("Shell.Application")
IE_count = objShell.Windows.Count
For x = 0 To (IE_count - 1)
    On Error Resume Next    ' sometimes more web pages are counted than are open
    my_url = objShell.Windows(x).Document.Location
    my_title = objShell.Windows(x).Document.Title

    If my_url Like "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet" Then
        Set ie = objShell.Windows(x)

table_html = ie.Document.getElementsByTagName("tbody")(2).innerhtml

table_html = ie.Document.getElementsByTagName("tbody")(2).innerhtml

Worksheets("Sheet1").Activate
Range("A1").Select
ActiveCell = table_html
        Exit For
    Else
    End If









Next





End Sub

推荐答案

当您单击"go_button"时，将打开一个新网页，但是您的宏仍然集中在原始网页上.在点击"行之后插入以下内容.这段代码将找到新的网页，并将重点放在该网页上

When you click on the "go_button" a new web page is opened, but your macro remains focused on the original web page. Insert the following after your "click" line. This code will find the new web page and place focus on it

Sub DataStuff()
    Set ie = CreateObject("InternetExplorer.Application")
        ie.Navigate "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMUTop"
        ie.Visible = True

    Do Until Not ie.Busy And ie.readyState = 4
        DoEvents
    Loop

    ie.Document.getelementbyid("param5").Value = "2014-04-12"
    ie.Document.getelementbyid("param6").Value = "8"
    ie.Document.getelementbyid("go_button").Click

    Set objShell = CreateObject("Shell.Application")
    IE_count = objShell.Windows.Count
    For x = 0 To (IE_count - 1)
        On Error Resume Next    ' sometimes more web pages are counted than are open
        my_url = objShell.Windows(x).Document.Location
        my_title = objShell.Windows(x).Document.Title

        If my_url Like "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet" Then
            Set ie = objShell.Windows(x)
            Exit For
        Else
        End If
    Next

    table_html = ie.Document.getElementsByTagName("tbody")(2).innerhtml
    html_lines = Split(table_html, Chr(10), -1, vbTextCompare)

    Worksheets("Sheet1").Activate
    Range("A1").Select

    For x = 0 To UBound(html_lines)
        ActiveCell = html_lines(x)
        ActiveCell.Offset(1, 0).Select
    Next
End Sub

这篇关于使用VBA将特定表输入excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！