Trying to get tables from a piece of code

[JudahRaion]

Hi everyone,

I need your help to figure how can I take from a piece of code an table to excel.

I find where table is and I create a variable to put the table in, but when I try to use that piece of code like html document, vba shows me this message: "Run-time error '424': Object required".

Sub Tester()

Dim tbls, tbl, trs, tr, tds, td, r, c
Dim IE As New InternetExplorerMedium


IE.navigate "URL"
IE.Visible = False


Application.Wait Now + TimeSerial(0, 0, 4)


allHTML = IE.document.body.innerHTML


''Carteira
If InStr(allHTML, "Carteira de Títulos") > 0 Then
    leng = Len("Carteira de Títulos")
    carteira = Mid(allHTML, InStr(allHTML, "Carteira de Títulos") + leng, Len(allHTML))
    leng = Len("    carteira = Mid(carteira, InStr(carteira, "<table") +="" leng,="" instr(carteira,="" "<="" table="">") + leng)
    'Range("A3") = carteira
End If

'error shows here
Set tbls = carteira.getElementsByTagName("table")
For r = 0 To tbls.Length - 1
    Debug.Print r, tbls(r).Rows.Length
Next r


Set tbl = carteira.getElementsByTagName("table")(0)
Set trs = tbl.getElementsByTagName("tr")


For r = 0 To trs.Length - 1
    Set tds = trs(r).getElementsByTagName("td")
    'if no  then look for 
    If tds.Length = 0 Then Set tds = trs(r).getElementsByTagName("th")


    For c = 0 To tds.Length - 1
        ActiveSheet.Range("B4").Offset(r, c).Value = tds(c).innerText
    Next c
Next r



End Sub

Thank you all

</table")>

 

[Norie]

carteira is a string, it has no properties.

Why don't you work with the actual HTML document for the page?

Set tbls = IE.doument.getElementsByTagName("table")
For r = 0 To tbls.Length - 1
    Debug.Print r, tbls(r).Rows.Length
Next r

 

[JudahRaion]

Why don't you work with the actual HTML document for the page?

Set tbls = IE.doument.getElementsByTagName("table")
For r = 0 To tbls.Length - 1
    Debug.Print r, tbls(r).Rows.Length
Next r

Hi, I don't use that because it will take all tables from website and I want a specific one.
Thank you for helping me.

 

[Norie]

What are you trying to do here?

'error shows here
Set tbls = carteira.getElementsByTagName("table")
For r = 0 To tbls.Length - 1
    Debug.Print r, tbls(r).Rows.Length
Next r

You can't use carteira to access the table elements on the page, it's just a string containing the HTML from the page.

 

[JudahRaion]

What I'm trying to do is what you're telling me that is not possible...

I want that specific table, but I don't know how can I reach it. The table don't have a ID or name, so I can't go by there... And the only way I found to choose the table is with the string thing.

Can I change that string information to a HTML so I can use it?

Is possible that I can open IE with only that portion of code?

Thank you

 

[Norie]

I'm not saying it's impossible, I'm saying it's not possible the way you are trying to do it.

Does cartieras contain only the HTML for the table you want?

 

[Norie]

You can create a new HTML document from an HTML string like this.

Set doc = New HTMLDocument
doc.body.innerHTML = carteira

You could then use that document to access the table you want.

 

[JudahRaion]

It worked!! Thank you! :D

Do you know any book with all of VBA information or course? Something.

Thank you again :D