web query / web scrapping to use with MS Excel or Libre Office Calc

Could someone please help me with the following:

We have lots of vehicles and need to keep the "Road Tax Due Date" and "MOT Expiry Date" updated.

This information is available from the site https://www.vehicleenquiry.service.gov.uk/.

The parameters required are Vehicle Registration Number and Vehicle Make. These details are there in my excel worksheet.

How can I automate the updating of the "Road Tax due date" and the "MOT expiry date" to the respective vehicle in my excel list instead of manually browsing to the website, updating the form and copying and pasting the results to my excel sheet.

Automating this will save me a load of time and make it highly accurate.

Thanks
Alcuino
Member_2_7971159Asked:
Who is Participating?
 
RobSampsonCommented:
Here is a rewrite that will work directly as a macro, when run from the worksheet that contains the data.

Regards,

Rob.

Sub RegoCheck()
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.Navigate "about:blank"
    While objIE.ReadyState <> 4
        WScript.Sleep 100
    Wend
    
    Set objSheet = ActiveSheet
     
    For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row
        strURL = "https://www.vehicleenquiry.service.gov.uk/"
        objIE.Navigate strURL
        While objIE.busy = True
            DoEvents
        Wend
        While objIE.ReadyState <> 4
            DoEvents
        Wend
        objIE.document.all("ctl00$MainContent$txtSearchVrm").Value = objSheet.Cells(intRow, "B").Value
        objIE.document.all("ctl00$MainContent$MakeTextBox").Value = objSheet.Cells(intRow, "C").Value
        objIE.document.all("ctl00$MainContent$butSearch").Click
        While objIE.busy = True
            DoEvents
        Wend
        While objIE.ReadyState <> 4
            DoEvents
        Wend
        ' Tax
        strTax = ""
        intPos = InStr(objIE.document.body.innerHTML, "Tax due:")
        If intPos > 0 Then
            strTax = Mid(objIE.document.body.innerHTML, intPos)
            strTax = Left(strTax, InStr(strTax, "</p>") - 1)
            strTax = Mid(strTax, 10)
        Else
            strTax = "Unknown"
        End If
        ' MOT
        strMOT = ""
        intPos = InStr(objIE.document.body.innerHTML, "Expires:")
        If intPos > 0 Then
            strMOT = Mid(objIE.document.body.innerHTML, intPos)
            strMOT = Left(strMOT, InStr(strMOT, "</p>") - 1)
            strMOT = Mid(strMOT, 10)
        Else
            strMOT = "Unknown"
        End If
        objSheet.Cells(intRow, "E").Value = strTax
        objSheet.Cells(intRow, "D").Value = strMOT
    Next
    MsgBox "Done"
End Sub

Open in new window

1
 
xtermieCommented:
Could we get a sample of the excel file with at least one valid car, so we can try this?

Thanks
0
 
Rob HensonFinance AnalystCommented:
I would suggest contacting the webmasters quoted on the page https://dvladigital.blog.gov.uk/ to see if there is a link to the source data available.

If there is, you might be able to do a download of the data table via a web connection and then do lookups.

I notice on the page that this is a Beta service and there is a link for giving Feedback, maybe suggesting a multiple check would be worthwhile.

Thanks
Rob H
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Member_2_7971159Author Commented:
Hi xtermie,

Have attached an excel file of 6 sample vehicles.

Rob,

Thanks for the suggestion to request DVLA for multiple checks.
We have over 150 vehicles and new ones get added

Thanks

Alcuino
Sample-Vehicles.xlsx
0
 
Rob HensonFinance AnalystCommented:
Being "Devil's Advocate" and no offence intended by the following comment but...

I suspect DVLA are aiming this service at individuals where they have only one or maybe a few vehicles in a family on which they want to check their vehicle expiry dates.

A Fleet Manager with many vehicles to keep track of ought to have this information kept up to date anyway. I am sure there is some DVLA inspection office that could do routine checks on a company with multiple vehicles and would expect the Fleet Manager to be able to lay their hands on MOT Certificates and Tax receipts without having to refer to the web.

Just saying...
0
 
Member_2_7971159Author Commented:
Hi Rob,
Thanks for taking the time for your valuable suggestion.
You are right that this DVLA service is currently targeted for individuals / family car owners.

We do keep our Fleet Management database updated and keep the supporting records.

The weak link is the wrongly updated dates in the database which would result in the system not providing the alert and so could be exposed to fine. Need to do a periodic review of these critical dates and the web-query automation would plug the weak link.

Additional, the same solution can be modified and used by us for other similar requirement.

Thanks,

Alcuino
0
 
Rob HensonFinance AnalystCommented:
I understand, indeed this would be a good way of checking that the correct dates are maintained rather than being the means by which records are kept.

I am aware that MoT Test stations/garages have access to the MoT History for any vehicle; I recall when I sold my car last year I was able to get a list from my garage of when MoTs were carried out, the most recent on the list being the current test. That would therefore suggest that the data is available in a database style format.
0
 
Rob HensonFinance AnalystCommented:
Just done searching on DVLA website and come up with this link:

https://www.gov.uk/government/news/benefits-of-joining-dvlas-fleet-scheme

Of particular interest might be:

access to the View Vehicle Record (VVR) enquiry service

Thanks
Rob
0
 
Rob HensonFinance AnalystCommented:
Further info on this link:

https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/534682/inf269-dvla-fleet-scheme.pdf

Go to Section 4, page 14 for detail of the View Vehicle Record Enquiry Service.

From what I can see the service is Free for Fleets of over 50 vehicles.
0
 
Member_2_7971159Author Commented:
Hi Rob,

That was a lot of help and availability of the details from the DVLA in excel format in the fleet scheme may actually solve my current Tax and MOT dates database discrepancy problem.

But we do have similar requirement in other areas as-well.

Thanks a lot Rob.
Alcuino
0
 
Rob HensonFinance AnalystCommented:
Glad to help, new information for me as well. Don't think I will ever need DVLA Fleet Scheme but you never know what is expected of accountants these days!!

Might be better to start a new question for your other requirements.

Thanks
Rob
0
 
Member_2_7971159Author Commented:
Hi Rob,

If possible, would wish to have a web-query solution to this problem so that I can modify it to apply it in the other areas where a web-query automation would be required.

Thanks,

Alcuino
0
 
Rob HensonFinance AnalystCommented:
Ideally, for a web-query to work the data on the web page has to be held in a table format such that the Web Query wizard can recognise a table of data and link to it for updating.

For example, where I am working we have a hot desk policy (nightmare!!) whereby we have to book our desks up to 4 weeks in advance. The web site through which we book desks shows our current bookings in a table format so I am able to link a spreadsheet to it such that I can keep track of the bookings offline in an Excel spreadsheet alongside a time-sheet for keeping track of my hours.

I am sure that using a form based website such as the DVLA enquiry with data from an Excel sheet is possible. I would imagine the routine copies the data from Excel and pastes it into the web page and then copies the retrieved data back to the spreadsheet but that sort of routine is beyond me; routines within Excel I am fine with but when it comes to cross-application routines I am out of my depth. Finding the time to learn would be great but not exactly high on the list of priorities.

Regards
Rob
0
 
Member_2_7971159Author Commented:
Hi Rob,

Thanks for your valuable time and input.



Dear xtermie,

If possible, please provide a solution.

Thanks,

Alcuino
0
 
Member_2_7971159Author Commented:
Hi Guys and Gals,

Could someone please help me with developing a web query as per my first request ?

Thanks,

Alcuino
0
 
RobSampsonCommented:
Hi, here is a script that can be used in a VBS file (not in an Excel macro) that will read the cells from your sample file, query the web site, and get the data.

Just change the path to the XLSX file, and that should be it.

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "about:blank"
While objIE.ReadyState <> 4
	WScript.Sleep 100
Wend

strExcelFile = "C:\Temp\RegoCheck.xlsx"

Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
objExcel.Visible = True
Set objWB = objExcel.Workbooks.Open(strExcelFile, False, False)
Set objSheet = objWB.Sheets(1)
 
For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row
	strURL = "https://www.vehicleenquiry.service.gov.uk/"
	objIE.Navigate strURL
	While objIE.busy = True
		WScript.Sleep 100
	Wend
	While objIE.ReadyState <> 4
		WScript.Sleep 100
	Wend
	objIE.document.all("ctl00$MainContent$txtSearchVrm").Value = objSheet.Cells(intRow, "B").Value
	objIE.document.all("ctl00$MainContent$MakeTextBox").Value = objSheet.Cells(intRow, "C").Value
	objIE.document.all("ctl00$MainContent$butSearch").Click
	While objIE.busy = True
		WScript.Sleep 100
	Wend
	While objIE.ReadyState <> 4
		WScript.Sleep 100
	Wend
	' Tax
	strTax = ""
	intPos = InStr(objIE.document.body.innerHTML, "Tax due:")
	If intPos > 0 Then
		strTax = Mid(objIE.document.body.innerHTML, intPos)
		strTax = Left(strTax, InStr(strTax, "</p>") - 1)
		strTax = Mid(strTax, 10)
	Else
		strTax = "Unknown"
	End If
	' MOT
	strMOT = ""
	intPos = InStr(objIE.document.body.innerHTML, "Expires:")
	If intPos > 0 Then
		strMOT = Mid(objIE.document.body.innerHTML, intPos)
		strMOT = Left(strMOT, InStr(strMOT, "</p>") - 1)
		strMOT = Mid(strMOT, 10)
	Else
		strMOT = "Unknown"
	End If
	objSheet.Cells(intRow, "E").Value = strTax
	objSheet.Cells(intRow, "D").Value = strMOT
Next 
MsgBox "Done"

Open in new window


Regards,

Rob.
0
 
Member_2_7971159Author Commented:
Hi Rob,
thanks for your help
and apology for my ignorance.
could you please advice me on how to use this in excel ?

Thanks,

Alcuino
0
 
RobSampsonCommented:
It needs a slight rewrite to work in Excel as a macro, but I can do that. If you want to try the VBS, save the code in notepad as a file called RegoCheck.vbs

Change the path to the XLSX file to suit, and double click the VBS to run it.

I'll rewrite it a bit later in the day.

Rob.
0
 
Member_2_7971159Author Commented:
That worked perfectly.

Thanks Rob for your help and most appreciated.

Alcuino.
0
 
RobSampsonCommented:
No problem. You mentioned you wanted to use the same technique for other purposes. If you need some of the code explained, let me know.

Rob.
0
 
Member_2_7971159Author Commented:
RobSampson.
Your solution worked like magic.
Appreciate your help.

Thanks.
Alcuino
0
 
RobSampsonCommented:
No problem. Happy to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.