Set cell value of table from IE field

[K1600]

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?

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.

 

[John_w]

cl.Offset(, 1).Value = Expiry

 

[K1600]

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]

Instead of the For Each loop, use a For index loop and reference your table column ranges like this:

    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]

Instead of the For Each loop, use a For index loop and reference your table column ranges like this:

    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]

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]

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:

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]

Put VRM cell value in web page field:

            .Document.all("reg_num").Value = rngColVRM.Cells(r).Value

Read result from web page and put in MOT cell:

        Expiry = ie.Document.all("mot-expiry-text").innerText
    rngColMOT.Cells(r).Value = Expiry

 

[K1600]

Put VRM cell value in web page field:

            .Document.all("reg_num").Value = rngColVRM.Cells(r).Value

Read result from web page and put in MOT cell:

        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?

 

[John_w]

rngColMOT.Cells(r).Value = Split(Expiry, "Expires: ")(1)