Unable to parse a table

[shahin]

Hi there! I tried a lot to parse a table from a webpage but inability seized me and finally I ended up with nothing. Hope there somebody helps me figure out what exactly I am missing in my code. Thanks. This is the site: "http://www.tenh.com/available-freight-national-flatbed-carrier/"

Sub TableData()
Dim htmlas As Object, tRow As Object, tCel As Object
x = 2
With CreateObject("MSXML2.serverXMLHTTP")
    .Open "GET", "http://www.tenh.com/available-freight-national-flatbed-carrier/", False
    .send
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = .responseText
End With
Set htmlas = html.getElementsByTagName("table")(0)
    For Each tRow In htmlas.Rows
        For Each tCel In tRow.Cells
            y = y + 1
            Cells(x, y) = tCel.innerText
        Next tCel
        y = 0
        x = x + 1
    Next tRow
Set htmlas = Nothing
End Sub

 

[Marc L]

Hi !

As again you do not check basics
(exactly the same as thread Unable to parse a table !)
just pilot IE instead & more chances with element ID !
​

 

[shahin]

@Marc L
Thanks Marc L, you are always a good foreseer. Piloting IE was a nice decision in this case and it is working as I expected. It is still unknown to me why other browsers can't do the magic. Here is the working code. Anyways, don't you think that this code is a bit messy in it's appearance? Btw, I think something is wrong with my PC cause without declaring any variables the code is working fine.

Sub TableData()
Dim IE As New InternetExplorer
x = 2
With IE
    .Visible = False
    .navigate "http://www.tenh.com/available-freight-national-flatbed-carrier/"
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
End With
Set obj = IE.document.getElementsByTagName("table")
    For Each topic In obj
        For Each posts In topic.getElementsByTagName("tr")
            For Each post In posts.getElementsByTagName("td")
            y = y + 1
            Cells(x, y) = post.innerText
            Next post
            y = 0
            x = x + 1
        Next posts
    Next topic
End Sub

 

[shahin]

OMG!! It is working both ways now. Here is the one I got from sir Chihiro.

Sub TableData()
Dim IE As New InternetExplorer
x = 2
With IE
    .Visible = False
    .navigate "http://www.tenh.com/available-freight-national-flatbed-carrier/"
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
End With
Set obj = IE.document.getElementsByTagName("table")(0)
    For Each posts In obj.Rows
        For Each post In posts.Cells
            y = y + 1
            Cells(x, y) = post.innerText
        Next post
    y = 0
    x = x + 1
    Next posts
End Sub

 

[Marc L]

For an HTML TABLE object I never had to use TR & TD
as within TABLE object exists rows collection
as you can inspect within Locals window (again the basics !)
and for each row exists cells collection.

Another way is to use clipboardData object to copy TABLE object
as shown in one of your past threads …
​

 

[shahin]

@Marc L
I saw and collected your code written using clipboardData object. However, I kept that for special occasion and that code was not that easy to understand as well. Btw, something is missing from my above codes which is IE.quit or something but can't understand where should i put that line as it is my first time to use internet explorer. Thanks.

 

[Marc L]

Once you do not need anymore, to close it : IE.Quit …

And clear all remaining object variables.
​

 

[shahin]

But I didn't declare any object variables. I used them though! Is it necessary to clear them even if I haven't declared?

 

[Marc L]

All variable types different than text and number can be objects
as for example your IE variable !
And exception is for undeclared variables, Variant type
as they can be mostly everything !

So place a break point on last codeline aka End Sub, execute procedure
and once stopped at the breakpoint open Locals window :
all variables which value different of a string or a number and
not Nothing have to be cleared as for example your IE and obj variables !

It is the only way to release objects & free memory
and avoid a future crash, not common but can arise …
​

 

[shahin]

Thanks Marc L, for your priceless guideline. I must abide by from now on what you said.