CODEWITHSUNDEEP
htmlexcelvba
Tyger Guzman
Tyger Guzman

Reputation: 758

Pull HTML Table Data into Excel Sheet

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

I've tried : 

 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

And get a 

    Run Time error '424' object required

I added in: 

Set TDelements = xobj.innerText

and still get same 424 error.

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%

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

Upvotes: 1

Views: 3876

Answers (2)

QHarr
QHarr

Reputation: 84465

CSS selector:

Grab the table with a CSS selector of .data.data3

"." means class. As you can't have compound classNames in querySelector the full className has the " " replaced with "."

VBA:

Dim hTable as HTMLTable
Set hTable = objIe.document.querySelector(".data.data3")

You can then loop the rows of the table and the tables cells within each row. You use .getElementsByTagName("tr") and .getElementsByTagName("td") to get these respectively. Use the .innerText property to print out the associated values of the table cells.

CSS query

Upvotes: 1

Noodles
Noodles

Reputation: 2011

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.

Remember: EXCEL is a tool for non programmers to do programming stuff. You should learn to use Excel before programming it.

Upvotes: 0

Related Questions