将HTML表数据拉入Excel工作表 [英] Pull HTML Table Data into Excel Sheet

问题描述

需要一些帮助。已经能够使用下面的代码打开并从网页获取所需的所有数据，但将其全部放入一个单元格中，我似乎没有任何运气可以将数据分开。我想能够将所有的表数据都整合成excel，就好像它被复制和粘贴在一起，或者将几个关键的小数提取到特定的单元格中。

Need some help. Have been able to use below code to open and grab all the data I need from a webpage but its putting it all into one cell and I can't seem to have any luck getting the data to separate. I want to be able to get all the table data into excel as though it was copy and pasted basically or extract several key peices into specific cells.

 Dim objIe As Object


        Set objIe = CreateObject("InternetExplorer.Application")
    objIe.Visible = True


    objIe.navigate "http://cctools/reports2/main.php"

    While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend

     objIe.document.getElementById("submit").Click

    Set xobj = objIe.document.getElementById("reportOut")
             Sheet2.Range("A1") = xobj.innerText



    Set xobj = Nothing

    objIe.Quit
    Set objIe = Nothing
End Sub

我试过：

 Sub Sample()
    Dim objIe As Object
    Dim TDelements As IHTMLElementCollection
Dim TDelement As HTMLTableCell

        Set objIe = CreateObject("InternetExplorer.Application")
    objIe.Visible = True

    objIe.navigate "http://cctools/reports2/main.php"

    While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend

     objIe.document.getElementById("submit").Click

    Set xobj = objIe.document.getElementById("reportOut")
             Sheet2.Range("A1") = xobj.innerText



      r = 0
        For Each TDelement In TDelements
             'Look for required TD elements - this check is specific to VBA Express forum - modify as required
            If TDelement.className = "data data3" Then
            Sheet2.Range("A1").Offset(r, 0).Value = TDelement.innerText
            r = r + 1
            End If
        Next

    Set xobj = Nothing

    objIe.Quit
    Set objIe = Nothing
End Sub

并获得一个

    Run Time error '424' object required 

我添加了：

Set TDelements = xobj.innerText

并仍然得到同样的424错误。

and still get same 424 error.

以下是我要拉的数据的HTML代码

Here is the HTML code from the data I'm trying to pull

<div id="reportOut">

    <script type="text/javascript"></script>
    <h2></h2>
    <div class="bold font_2 large black"></div>
    <table class="data data3" cellspacing="0">
        <tbody>
            <tr class="altrow"></tr>
            <tr>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td>

                    81.52%

我试图拉所有表数据提取81.52％

I'm trying to pull all table data or just extract the 81.52%

推荐答案

Excel可以导入自己的数据，无需您的帮助。数据菜单 - 导入外部数据 - 新的Web查询（Alt + D，D，W）。导入你想要的地方（或只是你想要的桌子）。设置刷新选项。您的数据现在位于单元格中。

Excel can import it's own data without your help. Data menu - Import External Data - New Web Query (Alt + D, D, W). Import where you want (or just the table you want). Set refresh options. Your data is now in cells.

记住：EXCEL是非程序员做编程的工具。在编程之前，您应该学会使用Excel。

这篇关于将HTML表数据拉入Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！