We help IT Professionals succeed at work.

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

693 Views
1 Endorsement
Last Modified: 2016-08-23
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

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

Thanks
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

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
Rob HensonFinance Analyst
CERTIFIED EXPERT

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

Author

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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Rob HensonFinance Analyst
CERTIFIED EXPERT

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

Author

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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

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
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Hi Rob,

Thanks for your valuable time and input.



Dear xtermie,

If possible, please provide a solution.

Thanks,

Alcuino

Author

Commented:
Hi Guys and Gals,

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

Thanks,

Alcuino
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That worked perfectly.

Thanks Rob for your help and most appreciated.

Alcuino.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

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

Author

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

Thanks.
Alcuino
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem. Happy to help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.