UK License Plate Searcher

[Hgreen1601]

I have been trying to make a macro where I enter a license plate and it searches the gov.uk website (https://vehicleenquiry.service.gov.uk) and returns the cylinder capacity.

Relatively new to macro writing so any explanation would be appreciated, currently have numerous errors.

Sub EngineSearch()

    Dim Tool      As Object
    Dim Var     As Variant
    Dim element As Variant
    Dim WrkBk   As Workbook
    Dim WrkSht  As Worksheet
   
    Set WrkBk = ThisWorkbook
    Set WrkSht = WrkBk.ActiveSheet

    For i = WrkSht.Range("E3").Value To WrkSht.Range("F3").Value
    'Have put the cells of my data range within these values
   
   
    Set Tool = CreateObject("InternetExplorer.Application")
    Tool.navigate "[URL]https://vehicleenquiry.service.gov.uk/[/URL]"

    Tool.Visible = False
   
    Application.Wait (Now + TimeValue("0:00:01"))

    Set frm = Tool.document.getelementbyid("wizard_vehicle_enquiry_capture_vrn_vrn")

    frm.Value = WrkSht.Cells(i, 2).Value
       
    Tool.document.getelementbyid("submit_vrn_button").Click
   
    Application.Wait (Now + TimeValue("0:00:01"))

    Tool.document.getelementbyid("yes-vehicle-confirm").Click
    Tool.document.getelementbyid("capture_confirm_button").Click
   
    Application.Wait (Now + TimeValue("0:00:01"))

    Set HtmlType = ie.document.getelementbyid("cylinder_capacity")
    WrkSht.Range("C" & i) = Right(HtmlType.innerText, 7)
  

    Tool.Quit


    Next i
   
Exit Sub

 

[Hgreen1601]

Yeah no worries, clearing the cells is not a priority.

 

[Marc L]

• First remove the duplicates plates as it's a non sense to request any duplicate plate …
  
  
• For one of the unusual errors I decided to leave the late binding for an early binding of the IE object
  so you must activate the reference Microsoft Internet Controls on VBE side via its Tools menu.

​

Sub Wait4Ready(oIE As InternetExplorer)
         While oIE.Busy Or oIE.ReadyState < 4:  DoEvents:  Wend
         On Error Resume Next
    Do
         Err.Clear
         While oIE.Document.ReadyState <> "complete":  DoEvents:  Wend
    Loop While Err.Number
End Sub

Sub DemoIE2()
    Dim V, S&, N&, oIE As InternetExplorer, R&, D As Date, oElt As Object
        V = [C1].CurrentRegion.Value2:  If UBound(V) < 3 Then Beep: Exit Sub
        ActiveWindow.ScrollRow = 1
        S = ActiveWindow.VisibleRange.Rows.Count - 2
        N = S + 1
        On Error GoTo Fin
    For R = 3 To UBound(V)
        If Not IsEmpty(V(R, 1)) And (IsEmpty(V(R, 2)) Or V(R, 2) Like "¤ e*") Then
            Set oIE = New InternetExplorer
                oIE.Navigate "https://vehicleenquiry.service.gov.uk/"
                Cells(R, 3).Interior.Color = vbYellow
            If R > N Then
                If N + S >= UBound(V) Then N = UBound(V) + 1: ActiveWindow.ScrollRow = N - S _
                                      Else ActiveWindow.ScrollRow = N: N = N + S
            End If
                Wait4Ready oIE
                D = Now + 0.0001
                On Error Resume Next
            Do
                DoEvents
                Set oElt = oIE.Document.forms(0)("wizard_vehicle_enquiry_capture_vrn_vrn")
            Loop While D > Now And oElt Is Nothing
                On Error GoTo Fin
            If oElt Is Nothing Then
                Cells(R, 4).Value2 = "¤ error 1"
            Else
                    oElt.Value = V(R, 1)
                    oElt.form("submit_vrn_button").Click
                    Set oElt = Nothing
                    Wait4Ready oIE
                If IsObject(oIE.Document.querySelector("h1.govuk-heading-l.info-panel")) Then
                    Cells(R, 4).Value2 = "not found"
                Else
                        D = Now + 0.0001
                        On Error Resume Next
                    Do
                        DoEvents
                        Set oElt = oIE.Document.all("yes-vehicle-confirm")
                    Loop While D > Now And oElt Is Nothing
                        On Error GoTo Fin
                    If oElt Is Nothing Then
                        Cells(R, 4).Value2 = "¤ error 2"
                    Else
                            oElt.Click
                            Set oElt = Nothing
                            oIE.Document.all.capture_confirm_button.Click
                            Wait4Ready oIE
                            D = Now + 0.0001
                            On Error Resume Next
                        Do
                            DoEvents
                            Set oElt = oIE.Document.querySelector("#fuel_type>dd")
                        Loop While D > Now And oElt Is Nothing
                            On Error GoTo Fin
                        If oElt Is Nothing Then
                            Cells(R, 4).Value2 = "¤ error 3"
                        Else
                            Cells(R, 4).Resize(, 2).Value2 = Array(oIE.Document.querySelector("#engine_capacity>dd").innerText, oElt.innerText)
                            Set oElt = Nothing
                        End If
                    End If
                End If
            End If
                Cells(R, 3).Interior.ColorIndex = xlNone
        End If
Fin:
            If Err.Number <> -2147023706 And Not oIE Is Nothing Then oIE.Quit
            Set oIE = Nothing
        If Err.Number Then
            Debug.Print Err.Number; " : "; Err.Description:  Beep
            If R Then Cells(R, 3).Interior.ColorIndex = xlNone: Cells(R, 4).Value2 = "¤ err #" & Err.Number
            Exit For
        End If
    Next
End Sub

 

[Hgreen1601]

Tested and runs very well.

Thank you Marc, appreciate the effort in this!

 

[Marc L]

​

In your real worksheet, what could be the maximum number of plates within your list ?​

 

[Hgreen1601]

I would expect no more than 100 at one time

 

[Marc L]

​

As it depends on the free memory (RAM) of your computer I may have a Windows variation to achieve it a little bit faster if necessary …​

It's a trick for web requesting - should inspect again this way if I could success - but as here it's for piloting Internet Explorer​

it should need more free RAM as it may hang / crash your system.​

So if interested post a list of 50 unique (non duplicate) plates as my tests slow old laptop with its few RAM can not handle more …​

 

[Hgreen1601]

Currently have 16GB of RAM so don't think this should be an issue!

Again, thanks for your help!

 

[Marc L]

​

As it depends on the free memory so the more applications opened the less free memory the more chances Windows hangs or even crashes …​

​

And for speed performance if some others applications use some web access the VBA procedure execution may last more …​