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

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

Thanks

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

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

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...

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

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.

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

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.

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

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

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

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

Hi Rob,

Thanks for your valuable time and input.



Dear xtermie,

If possible, please provide a solution.

Thanks,

Alcuino

Hi Guys and Gals,

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

Thanks,

Alcuino

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"

Select allOpen in new window

Regards,

Rob.

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

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.

That worked perfectly.

Thanks Rob for your help and most appreciated.

Alcuino.

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.

RobSampson.
Your solution worked like magic.
Appreciate your help.

Thanks.
Alcuino

No problem. Happy to help.