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]

Just in case it's really an issue from a poor web connection try this revamped procedure :​

Sub DemoIE1r()
    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
                        D = Now + 0.0001
                        On Error Resume Next
                    Do
                        DoEvents
                        Set oElt = .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 = "¤ not found"
                    Else
                        oElt.Click
                        Set oElt = Nothing
                       .Document.all.capture_confirm_button.Click
                        While .Document.ReadyState <> "complete":  DoEvents:  Wend
                        Cells(R, 4).Resize(, 2).Value2 = Array(.Document.querySelector("#engine_capacity>dd").innerText, _
                                                               .Document.querySelector("#fuel_type>dd").innerText)
                    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]

Yup this has fixed the issue, must of been poor connection!

However, with this procedure when I run it, sometimes nothing will happen or it will stop half way down my list of license plates - any idea why?

 

[Marc L]

No idea as all is fine on my side with my original demonstration without needing the revamped one …​

​

If you hear a 'beep' sound, see the message in the VBE Immediate window (Ctrl G).​

Without any sound you must check it out in debug mode via the step-by-step hitting F8 key …​

​

As maybe my list is too short to reproduce your issue so you should post your worksheet in order I can give it a try (later) …​

 

[Hgreen1601]

In the VBA immediate window I'm getting the error: 424: object required.

Book2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2			License Plate	Engine Size	Fuel Type	Vehicle Size			Engine Size	Vehicle Size
3			LB66MPF			#VALUE!			< 1700	Small
4			SB66XDZ			#VALUE!			1700 - 2000	Medium
5			BN08DAY			#VALUE!			> 2000	Large
6			VO69DPF			#VALUE!
7			MW17EFF			#VALUE!
8			BT17LDZ			#VALUE!
9			BU16AXB			#VALUE!
10			SB66XDZ			#VALUE!
11			ND69KEK			#VALUE!
12			BT16AYC			#VALUE!
13			BD16EYO			#VALUE!
14			BP17CNA			#VALUE!
15			MW17UAD			#VALUE!
16			YK18DJU			#VALUE!
17			PE18JVR			#VALUE!
18			BT16AYC			#VALUE!
19			KJ16OJM			#VALUE!
20			EU69WRZ			#VALUE!
21			EX66ORH			#VALUE!
22			MW17EFF			#VALUE!
23			BD16EYO			#VALUE!
24			KJ16OJM			#VALUE!
25			RA66DJF			#VALUE!
26			FE16FJF			#VALUE!
27			BT17LDZ			#VALUE!
28			RA66DJF			#VALUE!
29			VO69DPF			#VALUE!
30			BN08DAY			#VALUE!
31			EU69WRZ			#VALUE!
32			BT16AYC			#VALUE!
33			BV18AOA			#VALUE!
34			BV18AOA			#VALUE!
35			BN17WPV			#VALUE!
36			BD16EYO			#VALUE!
37
38
Sheet1

Cell Formulas
Range	Formula
F3:F36	F3	=IF(NUMBERVALUE(LEFT(D3,FIND(" ", D3)-1))>2000,"Large", IF(NUMBERVALUE(LEFT(D3,FIND(" ", D3)-1))>1700,"Medium","Small"))

 

[Marc L]

​

Before your last post, just after adding a new plate on my side I got the same issue '¤ not found' with the original demonstration !​

I checked my Internet speed connection : as fast as usual … So it's an issue from the website side, maybe it's slower when it receives too many requests …​

I launched again the demonstration : no more issue (tried several times).​

​

According to your last issue, do you have an idea with which plate the error occurs ?​

 

[Marc L]

​

Forget my previous question as according to the website slowdown it may occur whatever the plate. More testing with your post #24 attachment …​

 

[Hgreen1601]

Yes I've had a plate return "¤ not found", then next time I run the macro it can extract the information. Doesn't seem to be a pattern with it!

 

[Marc L]

​

Maybe I found a workaround on my side but I had so few time so need more testing …​

But sometimes it can return some '¤ error #' result so in this case you just need to run again the procedure and it will treat only those error plates …​

 

[Hgreen1601]

Great thanks, no rush.

When I rerun the procedure will it replace the '¤ error' or will I need to delete it from the cell?

 

[Marc L]

​

In the next version it should be possible but with the actual version you have to clear the cells in column D before to 'rerun' …​

​

I met two unusual errors on my side but since I can 'track' them they did not occur again …​

​