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

 

[Marc L]

As you forgot to give an example of source data and expected result accordingly …​

​

As you can attach a sample worksheet with this forum tool XL2BB or link a sample workbook on a files host website like Dropbox for example …​

 

[Hgreen1601]

Engine Size Finder.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2			License Plate	Engine Size			Start Row	End Row
3			LD66TVE				3	10
4
5
6
7
8
9
10
11
Sheet1

Is this all you need or can I upload it with the module?

 

[Marc L]

​

Columns G & H are useless but what is the expected result for the cell C3 plate ?​

 

[Hgreen1601]

​

Columns G & H are useless but what is the expected result for the cell C3 plate ?​

I want it to be searched on https://vehicleenquiry.service.gov.uk and extract the cylinder capacity. I have column G & H because I want to be able to search multiple at once - is this not required then?

 

[Marc L]

​

As columns G:H are useless so I won't use them !​

​

From your attachment, what is the exact expected result in cell D3 according to the plate in cell C3 ?​

 

[Hgreen1601]

​

As columns G:H are useless so I won't use them !​

​

From your attachment, what is the exact expected result in cell D3 according to the plate in cell C3 ?​

1968 cc

 

[Marc L]

According to your attachment a VBA demonstration for starters :​

Sub DemoIE1()
  Const C = "complete", Y = "yes-vehicle-confirm"
    Dim V, R&, D As Date, oElt As Object
        V = [C1].CurrentRegion.Value2:  If UBound(V) < 3 Then Beep: Exit Sub
        On Error GoTo Fin
    With CreateObject("InternetExplorer.Application")
        For R = 3 To UBound(V)
            If Not IsEmpty(V(R, 1)) And IsEmpty(V(R, 2)) Then
                   .Navigate "https://vehicleenquiry.service.gov.uk/"
                    D = Now + 0.0001
                    On Error Resume Next
                Do
                    DoEvents
                    Set oElt = .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"
                Else
                        oElt.Value = V(R, 1)
                        oElt.form("submit_vrn_button").Click
                        Set oElt = Nothing
                        While .Document.ReadyState <> C:  DoEvents:  Wend
                    If IsObject(.Document.all(Y)) Then
                       .Document.all(Y).Click
                       .Document.all.capture_confirm_button.Click
                        While .Document.ReadyState <> C:  DoEvents:  Wend
                        Cells(R, 4).Value2 = .Document.querySelector("#engine_capacity>dd").innerText
                    Else
                        Cells(R, 4).Value2 = "¤ not found"
                    End If
                End If
            End If
        Next
Fin:
        If Err.Number <> -2147023706 Then .Quit
    End With
        If Err.Number Then Beep: Debug.Print Err.Number; " : "; Err.Description
End Sub

 

[Hgreen1601]

According to your attachment a VBA demonstration for starters :​

Sub DemoIE1()
  Const C = "complete", Y = "yes-vehicle-confirm"
    Dim V, R&, D As Date, oElt As Object
        V = [C1].CurrentRegion.Value2:  If UBound(V) < 3 Then Beep: Exit Sub
        On Error GoTo Fin
    With CreateObject("InternetExplorer.Application")
        For R = 3 To UBound(V)
            If Not IsEmpty(V(R, 1)) And IsEmpty(V(R, 2)) Then
                   .Navigate "https://vehicleenquiry.service.gov.uk/"
                    D = Now + 0.0001
                    On Error Resume Next
                Do
                    DoEvents
                    Set oElt = .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"
                Else
                        oElt.Value = V(R, 1)
                        oElt.form("submit_vrn_button").Click
                        Set oElt = Nothing
                        While .Document.ReadyState <> C:  DoEvents:  Wend
                    If IsObject(.Document.all(Y)) Then
                       .Document.all(Y).Click
                       .Document.all.capture_confirm_button.Click
                        While .Document.ReadyState <> C:  DoEvents:  Wend
                        Cells(R, 4).Value2 = .Document.querySelector("#engine_capacity>dd").innerText
                    Else
                        Cells(R, 4).Value2 = "¤ not found"
                    End If
                End If
            End If
        Next
Fin:
        If Err.Number <> -2147023706 Then .Quit
    End With
        If Err.Number Then Beep: Debug.Print Err.Number; " : "; Err.Description
End Sub

Great thank you, really appreciate the help.

If I was too add another variable to search at the same time would this prove much more difficult?

 

[Marc L]

​

Nope as once you get an element like the cylinder capacity that means the difficulties are bypassed …​