VBA related to entering information into table online

[KevinM3u]

Hi all,

Long time browser of this site and others. Been slowly picking up some vba skills by reading others questions online. After over a year of doing this, I've finally ran into a situation where I can't get something to work after many hours looking online and trying many things. Hoping I'm just missing a couple key lines that will get me where i need to be.

My goal:
My job requires us to track our billable hours. This is done on a website. I have already developed a macro to collect data throughout the day and print off a timesheet that is ready for entry into the website. My next step is to try and automate this process. The below code runs on my system without errors. It opens the page (it is firm intranet, so I have removed the URL) and tries to cylce through fields on the page and print the contents. Problem is that is returns nothing. Ultimately, once I can get it to actually find the fields and manipulate their contents, I can write the code myself to put the correct information in the fields.

I have also provided a snippet of how the data is presented in the html of the website, in hopes that my methodology is wrong with regards to accessing it.

The core of my question is this: How can I access each tr item and potentially manipulate its contents?

Option Explicit

 Sub ParseTable()
    Dim i As Integer
    Dim j As Integer
    Dim IE As InternetExplorer
    Dim HTMLdoc As MSHTML.IHTMLDocument 'Document object
    Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags
    Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags
    Dim eleRow As MSHTML.IHTMLElement 'Row elements
    Dim eleCol As MSHTML.IHTMLElement 'Column elements
    Dim ieURL As String 'URL
    Dim td As MSHTML.IHTMLElementCollection
    Dim tr As MSHTML.IHTMLElementCollection
    Dim trObj As MSHTML.HTMLGenericElement
    Dim tdObj As MSHTML.HTMLGenericElement
    Dim x As String
    
    'Open InternetExplorer
    Set IE = New InternetExplorerMedium
    IE.Visible = True
     
    'Navigate to webpage
    ieURL = "URL HERE"
    IE.Navigate ieURL
     'Wait
    Do While IE.Busy Or IE.ReadyState <> 4
       DoEvents
    Loop
     
    Set HTMLdoc = IE.Document 'Document webpage


Set tr = HTMLdoc.getElementsByTagName("TR")
For Each trObj In tr
    Set td = trObj.getElementsByTagName("TD")
    For Each tdObj In td
        'do something with each td object'
        x = tdObj.innerText
    Next
Next


End Sub

HTML sample of stuff I'm trying to get at. Look near the far right for the exact stuff I'm trying to pull (STUFF I WANT). This is only the top bit of the table but hopefully this is a good enough snippet to see what method to use.

<tbody style="height:100%"><tr id='ctl00xcontentPHxgrdTimesheet_r_0' style='height:1px;'><td iDV="False" class="ig_c482690d_r1 gridViewOddRow BorderGray" style="color:#339933;"><nobr><input type='checkbox' onpropertychange='igtbl_chkBoxChange(event,"ctl00xcontentPHxgrdTimesheet");' tabindex='-1' /></nobr></td><td class="ig_c482690d_r1 gridViewOddRow BorderGray" style="color:#339933;display:none;"><nobr> </nobr></td><td class="ig_c482690d_r1 gridViewOddRow BorderGray ig_c482690d_rc13" style="color:#339933;">
                                 
                                <img id="imgSearch" ***********="this.style.cursor='hand'" *******="return beginEditTemplate2();"
                                    **********="this.style.cursor='default'" src="../NS/Images/Lookup.gif" />
                            </td><td class="ig_c482690d_r1 gridViewOddRow BorderGray ig_c482690d_rc14" style="color:#339933;display:none;"><nobr>Regular Time</nobr></td><td class="ig_c482690d_r1 gridViewOddRow BorderGray ig_c482690d_rc15" style="color:#339933;"><nobr>STUFF I WANT</nobr></td><td class="ig_c482690d_r1 gridViewOddRow BorderGray ig_c482690d_rc16" style="color:#339933;"><nobr>STUFF I WANT</nobr></td>

 

[John_w]

This is the usual way of looping through table rows and columns (cells):

    Dim table As HTMLTable, tRow As HTMLTableRow, tCell As HTMLTableCell

    Set table = HTMLdoc.getElementById("theTableId")   
    'Or
    Set table = HTMLdoc.getElementsByTagName("TABLE")(0)   '0 = the first table
    
    For Each tRow In table.Rows
        For Each tCell In tRow.Cells
            Debug.Print tCell.innerText; " ";
        Next
        Debug.Print
    Next

To loop through all rows regardless of which table they are in:

    For Each tRow In HTMLdoc.getElementsByTagName("TR")
        For Each tCell In tRow.Cells
            Debug.Print tCell.innerText; " ";
        Next
        Debug.Print
    Next

 

[KevinM3u]

Thanks for the response John!

I tried both methods, as well as both set table methods in the first blip of code and keep getting "Run-time error 91: Object variable or With block variable not set" at the set table lines or at the For Each tRow line in the second blip.

Any ideas why this might be?

 

[KevinM3u]

Bump to see if anyone on their lunch break has any insight!

 

[John_w]

The error sounds like the HTMLdoc object has not been set. Is the Set HTMLdoc = IE.document line still there and executed before the code I posted?

 

[KevinM3u]

Thanks for the follow-up John. Unfortunately I do have that line. This is the code I'm running. It doesn't trigger any errors, but the For code doesn't loop; it just runs through once printing nothing.

Sub ParseTable22()
    Dim i As Integer
    Dim j As Integer
    Dim IE As InternetExplorer
    Dim HTMLdoc As MSHTML.IHTMLDocument 'Document object
    Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags
    Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags
    Dim eleRow As MSHTML.IHTMLElement 'Row elements
    Dim eleCol As MSHTML.IHTMLElement 'Column elements
    Dim ieURL As String 'URL
    Dim td As MSHTML.IHTMLElementCollection
    Dim tr As MSHTML.IHTMLElementCollection
    Dim trObj As MSHTML.HTMLGenericElement
    Dim tdObj As MSHTML.HTMLGenericElement
    Dim x As String
    
    'Open InternetExplorer
    Set IE = New InternetExplorerMedium
    IE.Visible = True
     
    'Navigate to webpage
    ieURL = "URL"
    IE.Navigate ieURL
     'Wait
    Do While IE.Busy Or IE.ReadyState <> 4
       DoEvents
    Loop
     
    Dim table As HTMLTable, tRow As HTMLTableRow, tCell As HTMLTableCell
    Set HTMLdoc = IE.Document
    
'    Set table = HTMLdoc.getElementById("ct100xcontentPHxgrdTimesheet_main") 'table id
'    'Or
    Set table = HTMLdoc.getElementsByTagName("table")(0)   '0 = the first table

    For Each tRow In table.Rows
        For Each tCell In tRow.Cells
            Debug.Print tCell.innerText; " ";
        Next
        Debug.Print
    Next
'    For Each tRow In HTMLdoc.getElementsByTagName("TR")
'        For Each tCell In tRow.Cells
'            Debug.Print tCell.innerText; " ";
'        Next
'        Debug.Print
'    Next

End Sub

 

[KevinM3u]

Bump to see if anyone might be able to help.

 

[John_w]

Thanks for the follow-up John. Unfortunately I do have that line. This is the code I'm running. It doesn't trigger any errors, but the For code doesn't loop; it just runs through once printing nothing.

That doesn't make sense: you seem to be saying that the For code doesn't loop, but it runs through once.

Have you got the correct table? Try different table indexes and output the whole table text to see if you're referencing the correct table:

Set table = HTMLdoc.getElementsByTagName("table")(1)
Debug.Print table.innerText
Set table = HTMLdoc.getElementsByTagName("table")(2)
Debug.Print table.innerText

But note that tables can be nested so the innerText displayed may be part of 2 or more tables, which means you should increase the (1) or (2) index number until it references the required inner-most table. Of course, that's why it's better to use getElementsById to reference the correct table by its id. Or does the page use frames, in which case you will have to reference the document within the correct frame first.

 

[KevinM3u]

Thanks John.

When I said the For doesnt loop I meant that it runs, but only one iteration, i.e. it doesn't cycle at all as you would expect for printing multiple cells of a table.

I tried the debug print as you discussed above, and table (0) will run without printing anything and (1) errors me out, so I would think that implies there is not a second table on the page.

I suspect the page uses frames, as if i click to the far left and view the source it is materially different than when i view the source when clicking in the table.

how would i dictate which frame my document object references?

 

[John_w]

Search for "HTMLdocument frame" and there should be example code on this forum which shows how to reference the frame document.

Something like:

Dim HTMLdoc As HTMLdocument
Set HTMLdoc = IE.document.frames("frameId").document

Or you might need getElementsByTagName("FRAME")(0), or another method. It all depends on the website.