vba: how to insert value within html table?

Question

I can't find a way to pupulate html table clumn, that has a dynamic id - The id changes everytime I open a new session.

If I manualy populate column(1/10/2019) with "1"(hour) - in the next session the id for column(1/10/2019) becomes constant, and I can use "application.sendkeys ("7")" to change the value to "7"(hours) for example.

How can I populate a column within the html table, that I haven't inserted manualy any values before? What is the propper way to approach this task?

Sub Treport()

'Make the app work faster?
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'--------------------------------
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Report_Time") 'my data will be stored here
'--------------------------------
Dim LastRow As Long
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'range definition
'--------------------------------
Dim i As Long            'Will be used for a loop that navigate to different url
For i = 2 To LastRow     'variable range
Dim IE As Object         'Internet Explorer declaration
Set IE = CreateObject("InternetExplorer.Application") 'Opens browser
IE.Visible = True

IE.navigate sht.Range("A" & i).Value 'My url that I want to navigate to
While IE.readyState <> 4 Or IE.Busy: DoEvents: Wend 'wait until site fully loads

Dim Doc As New HTMLDocument 'Will be used for the main html page
Set Doc = IE.document

Doc.getElementById("imgLastWeek").Click 'Here I click to navigate within the timesheet to the desired time range
While IE.readyState <> 4 Or IE.Busy: DoEvents: Wend 'wait untill page loads again
Doc.getElementById("9da306a8-b813-46ff-b94f-45636f401ba8").Click 'need to click the column before inseting a value
Application.Wait Now + TimeValue("00:00:2")
Application.SendKeys ("7") 'here I change value from "1" to "7" in a clumn that I have manualy inserted number before, that is why the id is constant

Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

I expect to insert hours to columns according the Id of the task + the date of the column. If more of the html is needed to understand the table stracture please advise and I will copy more of the code.

<table id="tblTimeSheet_tblMain" style="width: 704px; padding-bottom: 0px;" cellspacing="0" s="">
  <colgroup id="tblTimeSheet_cgMn">
    <col style="width: 40px;" span="1" u="px" orgwidth="40">
    <col style="width: 40px;" span="1" u="px" orgwidth="40">
    <col style="width: 93px;" span="1" u="px" orgwidth="93">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 35px;" span="1" u="px" orgwidth="35">
    <col style="width: 40px;" span="1" u="px" orgwidth="40">
  </colgroup>
  <tbody>
    <tr id="0af6368f-79e0-49f8-bbca-15808c556655" style="height: 25px;" s="" pid="1f0305b4-8683-4c61-b85e-3b0b885b6e30">
      <td align="left" class="r" ct="" s="" dp="2" ah="0" d="0" dt="0" cv="" ae="0" timetype="RemainHrs">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="left" class="r" ct="" s="" dp="2" ah="0" d="0" dt="0" cv="" ae="0" timetype="PerComp">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="center" class="r" ct="" s="" dp="2" ah="0" d="0" dt="4" cv="" ae="0" timetype="ForecastFinish">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="9ab38f91-cd6b-432e-83b2-0033796f7d54" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/4/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="a7f1f9d2-b9c5-48c8-bc19-7e1d0041d1aa" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/5/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="edf44c3c-b10e-4cf3-ad91-b91c08662229" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/6/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="b9cd0b45-f864-4003-ac47-5701489c8c3f" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/7/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="00ed8598-26bb-4a10-8481-64ea40149765" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/8/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td title="Description: &#10;&#13;OT hours: 0.00&#10;&#13;" align="right" class="r" id="806751c3-c2e5-40e2-b269-a0cc99ab18fe" style="color: rgb(128, 128, 144);" ct="1.00" s="" dp="2" ah="0" d="0" dt="3" cv="1.00" ae="0" celldate="1/9/2019" controltype="etbl"
        timetype="reg" hasdatarec="1">
        <nobr>1.00</nobr>
      </td>
      <td align="right" id="26557391-224c-48e5-87a3-bf002793f4e0" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/10/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" ct="1.00" s="" dp="2" ah="0" d="0" dt="0" cv="1.00" timetype="total" othours="0.00" reghours="1.00">
        <nobr>1.00</nobr>
      </td>
    </tr>
    <tr id="718f6602-c9fe-4cb1-a309-e8342c34a0f2" style="height: 25px;" s="" pid="d9aca3f0-04ae-44ee-94f0-c2ed14da8e24">
      <td align="left" ct="10.00" s="" dp="2" ah="0" d="0" dt="0" cv="10.00" timetype="RemainHrs">
        <nobr>10.00</nobr>
      </td>
      <td align="left" ct="0.00" s="" dp="2" ah="0" d="0" dt="0" cv="0.00" timetype="PerComp">
        <nobr>0.00</nobr>
      </td>
      <td title="1/7/2019" align="center" ct="1/7/2019" s="" dp="2" ah="0" d="0" dt="4" cv="1/7/2019" timetype="ForecastFinish">
        <nobr>1/7/2019</nobr>
      </td>
      <td align="right" id="57e0de52-1114-4532-8088-5902d01005e2" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/4/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="ca52dd3e-52a3-4139-80be-ef7cbdd533dc" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/5/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="62d73cbd-ede0-4043-b8ae-4d43cc65c8f4" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/6/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td title="Description: &#10;&#13;OT hours: 0.00&#10;&#13;Is work performed Onsite?: &#10;&#13;Technical Activity Bucket: &#10;&#13;Is this Travel time?: &#10;&#13;" align="right" class="r" id="4db5269d-1512-4236-a2f2-6fb51c5a3892" style="color: rgb(0, 128, 0);"
        ct="10.00" s="" dp="2" ah="0" d="0" dt="3" cv="10.00" ae="0" celldate="1/7/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>10.00</nobr>
      </td>
      <td title="Task for Karin Goren" align="right" id="3c124199-04fb-4f04-841b-cef8f6c7fece" ct="4.00" s="" dp="2" ah="0" d="0" dt="3" cv="4.00" ae="1" celldate="1/8/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>4.00</nobr>
      </td>
      <td title="Task for Karin Goren" align="right" id="ee7f0b68-98d1-440c-a801-98d432a3d322" ct="5.00" s="" dp="2" ah="0" d="0" dt="3" cv="5.00" ae="1" celldate="1/9/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>5.00</nobr>
      </td>
      <td align="right" id="784f3cf3-b9ad-43ca-9ea8-99e7384f78de" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/10/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" ct="19.00" s="" dp="2" ah="0" d="0" dt="0" cv="19.00" timetype="total" othours="0.00" reghours="19.00">
        <nobr>19.00</nobr>
      </td>
    </tr>
    <tr id="e2437d6b-db12-4ec2-9724-d21e84171c18" style="height: 25px;" s="" pid="d9aca3f0-04ae-44ee-94f0-c2ed14da8e24">
      <td align="left" ct="10.00" s="" dp="2" ah="0" d="0" dt="0" cv="10.00" timetype="RemainHrs">
        <nobr>10.00</nobr>
      </td>
      <td align="left" ct="0.00" s="" dp="2" ah="0" d="0" dt="0" cv="0.00" timetype="PerComp">
        <nobr>0.00</nobr>
      </td>
      <td title="1/7/2019" align="center" ct="1/7/2019" s="" dp="2" ah="0" d="0" dt="4" cv="1/7/2019" timetype="ForecastFinish">
        <nobr>1/7/2019</nobr>
      </td>
      <td align="right" id="ed31f3c4-a5c6-4d79-ad8a-9d5debdd20dc" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/4/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="4e26eafe-f2fb-4005-9ff5-190b436ce5e4" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/5/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="a3cc1439-c20c-4568-afd5-62b407379da8" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/6/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="442c6007-7ed3-43ef-a29f-a659a3fa82bf" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/7/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="63972eb3-9de2-4a1b-9b41-4d0a7ff6bf28" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/8/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td title="Task for Karin Goren" align="right" id="6e87f65e-6f14-4992-9e78-ec1785188c23" ct="3.00" s="" dp="2" ah="0" d="0" dt="3" cv="3.00" ae="1" celldate="1/9/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>3.00</nobr>
      </td>
      <td align="right" id="93041572-96b4-43aa-a261-6ba53bccb519" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/10/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" ct="3.00" s="" dp="2" ah="0" d="0" dt="0" cv="3.00" timetype="total" othours="0.00" reghours="3.00">
        <nobr>3.00</nobr>
      </td>
    </tr>
    <tr id="f6b76a38-f33f-4ecc-8c33-99e061877560" style="height: 25px;" s="" pid="bd3bedb4-dbba-4132-a61c-7357765eb80d">
      <td align="left" ct="20.00" s="" dp="2" ah="0" d="0" dt="0" cv="20.00" timetype="RemainHrs">
        <nobr>20.00</nobr>
      </td>
      <td align="left" ct="0.00" s="" dp="2" ah="0" d="0" dt="0" cv="0.00" timetype="PerComp">
        <nobr>0.00</nobr>
      </td>
      <td title="1/7/2019" align="center" ct="1/7/2019" s="" dp="2" ah="0" d="0" dt="4" cv="1/7/2019" timetype="ForecastFinish">
        <nobr>1/7/2019</nobr>
      </td>
      <td align="right" id="5161894a-6047-442c-9896-e81f90230238" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/4/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="0f7cb1c2-67ae-4c75-b094-dd083911d1c9" style="background-color: rgb(238, 238, 238);" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/5/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="d11cc9dd-6f21-4a43-acbd-de48224df366" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/6/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td align="right" id="ce4ef478-1b4d-436a-97dd-66fcffca6611" ct="" s="" dp="2" ah="0" d="0" dt="3" cv="" ae="1" celldate="1/7/2019" controltype="etbl" timetype="reg" hasdatarec="0">
        <nobr>&nbsp;</nobr>
      </td>
      <td title="Description: &#10;&#13;OT hours: 0.00&#10;&#13;Is work performed Onsite?: &#10;&#13;Technical Activity Bucket: &#10;&#13;Is this Travel time?: &#10;&#13;" align="right" class="r" id="d0151d04-d2f1-4413-b6bb-d3560f95c0f5" style="color: rgb(128, 128, 144);"
        ct="1.00" s="" dp="2" ah="0" d="0" dt="3" cv="1.00" ae="0" celldate="1/8/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>1.00</nobr>
      </td>
      <td title="Description: &#10;&#13;OT hours: 0.00&#10;&#13;Is work performed Onsite?: &#10;&#13;Technical Activity Bucket: &#10;&#13;Is this Travel time?: &#10;&#13;" align="right" class="r" id="aba659f1-57dc-4dbd-b7ef-263a0b020da7" style="color: rgb(128, 128, 144);"
        ct="2.00" s="" dp="2" ah="0" d="0" dt="3" cv="2.00" ae="0" celldate="1/9/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>2.00</nobr>
      </td>
      <td title="Test task overlapping hours" align="right" id="9da306a8-b813-46ff-b94f-45636f401ba8" ct="1.00" s="" dp="2" ah="0" d="0" dt="3" cv="1.00" ae="1" celldate="1/10/2019" controltype="etbl" timetype="reg" hasdatarec="1">
        <nobr>1.00</nobr>
      </td>
      <td align="right" ct="4.00" s="" dp="2" ah="0" d="0" dt="0" cv="4.00" timetype="total" othours="0.00" reghours="4.00">
        <nobr>4.00</nobr>
      </td>
    </tr>
  </tbody>
</table>

the html for the entire table would help inserted using the snippet tool via edit. Not as an image. — QHarr
– QHarr, Commented Jan 13, 2019 at 17:43
Hi Qharr, Had troubles to add the html, eather it was to small or to big. I hope what I managed to add is enought, if not please let me know and I will try again. — Slava Dolkart
– Slava Dolkart, Commented Jan 13, 2019 at 19:02

QHarr · Accepted Answer · 2019-01-14 12:35:54Z

1

As you are using IE browser you have access to a wider range of selectors including some pseudo class css selectors e.g. nth-of-type (and at the end I touch on :last-child). This is as opposed to what can be applied, using the .querySelector method of HTMLDocument object, via MSHTML.

So, for your HTML shown you can start by grabbing the row of interest (the one shown with red and blue rectangles) using the nth-of-type selector to select row by tr tag nth occurrence with the parent table identified by id (# is an id selector).

Let's see what that row looks like when matched in terms of returned elements:

Which should resemble (bearing in mind empty td elements won't show until I add td element selector - shown in next step):

Now, let's break this down to include children td (table cell) elements within that row using a descendant combinator and td element selector:

In the above you can now see all the table cells within that row and use the attributes you see, or position, or combination of both to target elements of interest (Table cells).

For example, if you know the date in advance for your red Monday 7th you can use the celldate attribute to target and add that into your selector chain:

#tblTimeSheet_tblMain tr:nth-of-type(4) td[celldate='1/7/2019']

If the date is not known you can use position again and use the nth-of-type index for the td cell with that tr row (note this is base-1 not 0):

#tblTimeSheet_tblMain tr:nth-of-type(4) td:nth-of-type(7)

This is much like referring by x row y column syntax.

The css selectors shown above are applied by .querySelector method of ie.document e.g.

ie.document.querySelector("#tblTimeSheet_tblMain tr:nth-of-type(4) td:nth-of-type(7)").value = "xyx"

or

ie.document.querySelector("#tblTimeSheet_tblMain tr:nth-of-type(4) td:nth-of-type(7)").innerText = "xyx"

I can't test this with a browser and vba libraries but I know nth-of-type is supported as well as attribute = value selectors and descendant combinators.

It's worth knowing, in this case, that the last-child selector is also supported, and as you are referencing the last tr row you can also use:

#tblTimeSheet_tblMain tr:last-child td:nth-of-type(7)

Other points to note:

The longer the selector the slower it will be. Though with modern browsers you are talking tiny performance differences.
Also, the right most is the key selector and should be as selective as possible e.g. id selector if possible, otherwise class selector... the order to select in is documented. All should be selective as possible but it is important to remember the right to left reading.
This #tblTimeSheet_tblMain tr:nth-of-type(4) td:nth-of-type(7) should be akin to chaining as follows: ie.document.getElementById("tblTimeSheet_tblMain").getElementsByTagName("tr")(4).getElementsByTagName("td")(7) but more performant.

More info on performance and selector choice here. Excerpt:

edited Jan 14, 2019 at 12:35

answered Jan 13, 2019 at 19:38

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Slava Dolkart Over a year ago

Dear QHarr, thank you very much for your answer, again, your suggestion rocks! First of all I will try to understand your suggested matirial, but most likely it will give me another reason take a plunge and learn the basisc firsts. Seocnd thing, is the same issue from my last question about how to make a dynamic value within the queryselector(). - I think I will open a seperate topic(also what you advised me last time and I haven't done it yet) Third thing, The

Slava Dolkart Over a year ago

continue: 3rd thing - ".value = "xyx" doesn't work - it brings up an error [run-time error 438: object doesnt support this propert or method"] ; My solution to that was: IE.document.querySelector("#tblTimeSheet_tblMain tr:nth-of-type(4) td:nth-of-type(7)").innerHTML = "Value". I will update with my progress, and perhaps will edit the first post then. Thanks again for your effort.

QHarr Over a year ago

To make a dynamic value with querySelector you usually concantenate in the value with a string if picking up the string from somewhere. If the value isn't known then you look for something constant e.g. in the above I assume the position in the table is a constant so I match on position rather than a specific value. Any further questions let me know.

QHarr Over a year ago

you can always ping me with @qharr (you need the @) in the dawghaus if you want to go over the code or have other questions. If the question is significant then I may advise opening a new question.

QHarr Over a year ago

If .value not allowed try .innerText = "xyz"

Collectives™ on Stack Overflow

vba: how to insert value within html table?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related