Set cell value of table from IE field

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153
I have a range of cells formatted as an Excel Table with headers of "VRM" and "MOT Expiry". There is a list of registration numbers in the VRM column and presently the MOT Expiry column is empty.

I have the below code which checks registration numbers listed in the 'VRM' column using internet explorer for the MOT expiry date. The check is working fine but I have two issues:

1. I'm struggling to get the returned date in to the 'MOT Expiry' column on the same row as the checked VRM. Depending which option I use (both are in the below code), I either replace the registration number in the original column or put the returned date in every row on the 'MOT Expiry' column.

2. The date being returned with the .innerText command is preceded by "Expires: ", so looks like "Expires: 01/01/2022", is there a way to remove this so I just get the date?


VBA Code:
Sub MOT_Check3()

Dim rngColMOT As Range
Dim rngColVRM As Range
Dim cl As Range
Dim ie As InternetExplorer
Dim sURL As String
Dim Expiry As Variant

sURL = "https://www.checkcardetails.co.uk/"

Set ie = New InternetExplorer
Set rngColVRM = Sheet1.Range("Table1[VRM]")
Set rngColMOT = Sheet1.Range("Table1[MOT Expiry]")

    For Each cl In rngColVRM
        With ie
            .AddressBar = 0
            .StatusBar = 0
            .Toolbar = 0

            .Visible = True
            .Navigate sURL

        Do Until Not .Busy And .ReadyState = 4
        DoEvents
        Loop

'Puts VRM in VRM field on website
            Debug.Print cl.Value
            .Document.all("reg_num").Value = cl.Value
            Application.Wait Now + TimeSerial(0, 0, 2)

        End With

        Do Until Not ie.Busy
        DoEvents
        Loop
        
'Press 'Submit' button
        ie.Document.all("submit-button").Click

        Do Until Not ie.Busy
        DoEvents
        Loop

        Expiry = ie.Document.all("mot-expiry-text").innerText

'        cl.Value = Expiry                'This replaces VRM with MOT expiry date
        rngColMOT.Value = Expiry        'This fills 'MOT Expiry' column with MOT expiry date
    Next cl

End Sub


Thanks in advance.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153
VBA Code:
cl.Offset(, 1).Value = Expiry
Cheers John, I knew it would be something simple, I'll give it a try in the morning. Do you know if there is a way to reference it by its header name (MOT Expiry)?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
Instead of the For Each loop, use a For index loop and reference your table column ranges like this:
VBA Code:
    Dim r As Long
    For r = 1 To Sheet1.Range("Table1").Rows.Count
        HTMLelement.Value = rngColVRM.Cells(r).Value   'write HTML element

        rngColMOT.Cells(r).Value = HTMLelement.Value   'read HTML element
    Next
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153

ADVERTISEMENT

Instead of the For Each loop, use a For index loop and reference your table column ranges like this:
VBA Code:
    Dim r As Long
    For r = 1 To Sheet1.Range("Table1").Rows.Count
        HTMLelement.Value = rngColVRM.Cells(r).Value   'write HTML element

        rngColMOT.Cells(r).Value = HTMLelement.Value   'read HTML element
    Next
Thanks John,

The .offset solution you gave works but I can't get it to work using the headers name option. I have added the above code (where I think it should be) but when I try to run it, I get a Compile Error of "Variable not defined" on the htmlelement.Value = rngColVRM.Cells(r).Value line.

Glynn
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
HTMLelement is just a generic name, not a variable. Substitute HTMLelement with your HTML element, e.g. ie.Document.all("mot-expiry-text")

My loop replaces your For Each ... Next loop.
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153

ADVERTISEMENT

Hi John,

I'm not sure if I'm putting things in the right places with this. I have tried to encorporate the HMTLElement part into my previous code but without the 'for each'. With the below code it no longer puts the registration number from my VRM column into the website and when it returnes the result it is just putting "mot-expiry-text" into the cell which I think makes sense with how I have the code but this is obviously not what I need.

This is what I currently have:

VBA Code:
Sub MOT_Check4()

Dim rngColMOT As Range
Dim rngColVRM As Range
Dim cl As Range
Dim ie As InternetExplorer
Dim sURL As String
Dim Expiry As Variant
Dim r As Long

sURL = "https://www.checkcardetails.co.uk/"

Set ie = New InternetExplorer
Set rngColVRM = Sheet1.Range("Table1[VRM]")
Set rngColMOT = Sheet1.Range("Table1[MOT Expiry]")

'    For Each cl In rngColVRM
    For r = 1 To Sheet1.Range("Table1").Rows.Count
        With ie

            .AddressBar = 0
            .StatusBar = 0
            .Toolbar = 0
            .Visible = True
            .Navigate sURL

        Do Until Not .Busy And .ReadyState = 4
        DoEvents
        Loop

'Puts VRM in VRM field on website
'            Debug.Print cl.Value
'            .Document.all("reg_num").Value = cl.Value
            rngColVRM.Value = rngColVRM.Cells(r).Value    'Write HTML element
            Application.Wait Now + TimeSerial(0, 0, 2)

        End With

        Do Until Not ie.Busy
        DoEvents
        Loop
        
'Press 'Submit' button
        ie.Document.all("submit-button").Click

        Do Until Not ie.Busy
        DoEvents
        Loop

'Put returned date in correct column
'        Expiry = ie.Document.all("mot-expiry-text").innerText
'        cl.Value = Expiry
'        rngColMOT.Value = Expiry
        rngColMOT.Cells(r).Value = ("mot-expiry-text")    'Read HTML element    ' "mot-expiry-text" is named ranged on website
    Next

End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
Put VRM cell value in web page field:
VBA Code:
            .Document.all("reg_num").Value = rngColVRM.Cells(r).Value
Read result from web page and put in MOT cell:
VBA Code:
        Expiry = ie.Document.all("mot-expiry-text").innerText
    rngColMOT.Cells(r).Value = Expiry
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
153
Put VRM cell value in web page field:
VBA Code:
            .Document.all("reg_num").Value = rngColVRM.Cells(r).Value
Read result from web page and put in MOT cell:
VBA Code:
        Expiry = ie.Document.all("mot-expiry-text").innerText
    rngColMOT.Cells(r).Value = Expiry
Perfect! Thank you.

Don't suppose you know how I can remove the "Expiry: " bit from the start of the returned date result before putting it into the cell so I only get the date do you?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,297
Messages
5,595,301
Members
413,985
Latest member
daniel1969

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top