Share your coding samples or ask questions

Question Pull HTML Table Data into excel

Tyger0951
Topic Author
Visitor

9 years 8 months ago - 9 years 8 months ago #418 by Tyger0951

Pull HTML Table Data into excel was created by Tyger0951

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 the table in text form into one cell instead of breaking into each cell like a table 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.

Code:

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 :

Code:

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 possiblycause code doesn'tlike the classname method being used.

I added in:

Code:

Set TDelements = xobj.innerText

and still get same 424 error.

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

Code:

<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%

Last edit: 9 years 8 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

roller
Offline
Administrator

9 years 8 months ago #419 by roller

Replied by roller on topic Pull HTML Table Data into excel

A bit hard for me to work out what is going wrong without having the the Excel file in front of me but can I suggest a different way to go about this - it might be simpler and saves you some VBA coding.

Go to the Excel Ribbon>Data and from the Get external data section first from the left choose From Web, enter your URL and check out the Options menu there are few things you can do like getting full HTML or Rich Text, etc...

Try this method see if it is easier. Maybe also record a macro while setting this up so you can use part of it in your VBA.

try that and see if this helps.

Please Log in or Create an account to join the conversation.

Tyger0951
Topic Author
Visitor

9 years 8 months ago - 9 years 8 months ago #420 by Tyger0951

Replied by Tyger0951 on topic Pull HTML Table Data into excel

Normally would do that method but the url is not dynamic at all. So have to navigate then input parameters click run then data appears. Website doesn't allow &"text"= type inputs for url. Its not cool. :-(

. So this is only other option than copy and pasting whole site then pasting into excel. And really want to get the html way to work

Code:

appIE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        appIE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

    appIE.Quit

    Range("A1").Select
            ActiveSheet.Paste

Works but annoying cause have to manipulate the data more in the sheets. And if something gets added to header and not the table could mess up cell reference.

Last edit: 9 years 8 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

roller
Offline
Administrator

9 years 8 months ago #421 by roller

Replied by roller on topic Pull HTML Table Data into excel

OK I am driving blind here but try addgin the " While (objIe.Busy Or objIe.readyState <> 4): DoEvents: Wend" after each click event. Maybe the error related to the Class ID is there before the document is not READY yet and have not rendered the full HTML before your VBA line start looking for Set xobj = objIe.document.getElementById("reportOut")

Also it is worth noting that innerText property doesn't not work it all browsers, I think it works in IE though.

If still no luck send me the files and URLs if possible by your work and I will check out on Monday.

Please Log in or Create an account to join the conversation.

Tyger0951
Topic Author
Visitor

9 years 8 months ago - 9 years 8 months ago #422 by Tyger0951

Replied by Tyger0951 on topic Pull HTML Table Data into excel

Set xobj = objIe.document.getElementById("reportOut")

Grabs everything. Cause

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

works to paste everything into one cell. Just the one cell looks like as though i pasted it into a txt file. All info jammed in one cell. Want that table being pasted broken into cells like a normal table.

Last edit: 9 years 8 months ago by Tyger0951.

Please Log in or Create an account to join the conversation.

roller
Offline
Administrator

9 years 8 months ago #423 by roller

Replied by roller on topic Pull HTML Table Data into excel

OK I get you, can you paste in here the full HTML that you are gettting all in on cells, let's see if there is a pattern that we can use the Len function to pick the value you need. I am sure there are more than one way but let's try this. Paste all the cell content in a post and tell me which value you want to pull out.

Please Log in or Create an account to join the conversation.

Time to create page: 0.515 seconds

Question Pull HTML Table Data into excel

Login Form