Solved

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

Posted on 2016-08-10
23
92 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
1
Comment
Question by:Member_2_7971159
  • 9
  • 7
  • 5
  • +1
23 Comments
 
LVL 17

Expert Comment

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

Thanks
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750126
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
 

Author Comment

by:Member_2_7971159
ID: 41750136
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750150
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
 

Author Comment

by:Member_2_7971159
ID: 41750162
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750174
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750184
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750190
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
 

Author Comment

by:Member_2_7971159
ID: 41750197
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750214
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
 

Author Comment

by:Member_2_7971159
ID: 41750242
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 41750297
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
 

Author Comment

by:Member_2_7971159
ID: 41750309
Hi Rob,

Thanks for your valuable time and input.



Dear xtermie,

If possible, please provide a solution.

Thanks,

Alcuino
0
 

Author Comment

by:Member_2_7971159
ID: 41751642
Hi Guys and Gals,

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

Thanks,

Alcuino
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41757448
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
 

Author Comment

by:Member_2_7971159
ID: 41757918
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 41758580
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
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 41758640
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
 

Author Comment

by:Member_2_7971159
ID: 41759244
That worked perfectly.

Thanks Rob for your help and most appreciated.

Alcuino.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41760182
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
 

Author Closing Comment

by:Member_2_7971159
ID: 41766576
RobSampson.
Your solution worked like magic.
Appreciate your help.

Thanks.
Alcuino
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41766670
No problem. Happy to help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now