Set cell value of table from IE field

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,128,109
Messages
5,628,758
Members
416,337
Latest member
tl3phd

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