How to create a HTTP GET in Excel VBA

Question

Good afternoon all,

I'm working on a project for work where I need to get vehicle values based on registration number and mileage and feed these into an Excel spreadsheet.

The registration number and mileage is stored in the spreadsheet but I'm stuck on where to start for getting started.

I created a rough VBA application last weekend which looked as follows

The registration number and mileage is stored in the spreadsheet but I'm stuck on where to start for getting started.

I created a rough VBA application last weekend which looked as follows:

Sub GetHTMLDocument()

Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim Email As MSHTML.IHTMLElement
Dim Password As MSHTML.IHTMLElement
Dim LoginButton As MSHTML.IHTMLElement
Dim REG As MSHTML.IHTMLElement
Dim Mileage As MSHTML.IHTMLElement
Dim CAPGo As MSHTML.IHTMLElement
Dim objEvent
Dim GetValue As MSHTML.IHTMLElement

'Show IE for testing purposes
IE.Visible = True
'Navigate to web page
IE.Navigate "https://valuationanywhere.cap.co.uk/LoginPage?ReturnUrl=%2f%3f__hstc%3d208265677.8bb2d3e6c872f15cd37070c17648ee29.1549763639794.1549763639794.1549763639794.1%26__hssc%3d208265677.1.1549763639794%26__hsfp%3d959865525&__hstc=208265677.8bb2d3e6c872f15cd37070c17648ee29.1549763639794.1549763639794.1549763639794.1&__hssc=208265677.1.1549763639794&__hsfp=959865525"

'Loop an empty loop until done
Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = IE.Document

'inputs email address
Set Email = HTMLDoc.getElementById("inputLoginEmail")
Email.Value = "email"
'inputs password
Set Password = HTMLDoc.getElementById("inputLoginPassword")
Password.Value = "password"
'Clicks login button
Set LoginButton = HTMLDoc.getElementById("btnLogin")
LoginButton.Click

'Wait 3 seconds for page to load
Application.Wait (Now + TimeValue("0:00:03"))

Set objEvent = IE.Document.createEvent("HTMLEvents")

'Input REG into text box
Set REG = HTMLDoc.getElementById("vrm")
REG.Value = "reg"
'Input mileage into text box
Set Mileage = HTMLDoc.getElementById("mileage")
Mileage.Value = "181000"

'Fakes data entry as no focus is given to the text box
objEvent.initEvent "change", False, True
REG.dispatchEvent objEvent
Mileage.dispatchEvent objEvent

'Clicks Go button
Set tags = IE.Document.getElementsByTagName("button")
For Each tagx In tags
If tagx.innerText = "Go" Then
    tagx.Click
    Exit For
End If
Next

'Wait 3 seconds for popup to load
Application.Wait (Now + TimeValue("0:00:03"))

Set tags = IE.Document.getElementsByTagName("button")
For Each tagx In tags
If tagx.innerText = "Create NEW Valuation" Then
    tagx.Click
    Exit For
End If
Next

This would navigate to the page, log me in and search for valuation. However we will eventually have a database of hundreds of cars we want to get valuations on and our CAP service has some plugins here - https://soap.cap.co.uk/vrm/capvrm.asmx?op=VRMValuation

Is there any way I can have VBA pick a reg and mileage from a sheet, and pull back the value?

I'm not expecting anyone to write the entire thing I would love to learn from this. But can anyone point me in the right direction?

Kindest regards, Craig

oh.... it is a SOAP request. You want to concatenate in the variables into your SOAP string. — QHarr
– QHarr, Commented Feb 14, 2019 at 21:00

QHarr · Accepted Answer · 2019-02-14 22:02:41Z

1

In essence you can read a 2 column range from Excel, containing column A reg and column B mileage, into a 2d array, then loop the dimension 1 of the array from lbound to ubound (i.e. the rows) and access the reg and mileage by indexing into the array. You can then concatenate those values into the body of the POST request . This is understandably very high level shown below. You would read the response into an xml document so you can parse out the info you want.

In terms of retrieving values we would need to see the relevant XML.

Option Explicit

Public Sub Test()
    'VBE > Tools > References > Add a reference to Microsoft HTML Object Library
    'other code

    Dim regAndMileage(), xmlDoc As Object
    Dim ws As Worksheet, r As Long, placeholderMileage As String, placeholderVR As String, body As String, response As String, html As HTMLDocument
    Const SUBSCRIBER_ID As Long = 123
    Const PASSWORD As String = "ABC"
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set html = New HTMLDocument
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    regAndMileage = ws.Range("A2:B4").Value      'Create the array. Reg is in col A and mileage in col B. Check datatypes when passed are as expected (int - though Long should work; and string)

    body = "<?xml version=""1.0"" encoding=""utf-8""?>"
    body = body & Chr$(10) & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
    body = body & Chr$(10) & "<soap:Body>"
    body = body & Chr$(10) & "<VRMValuation xmlns=""https://soap.cap.co.uk/vrm"">"
    body = body & Chr$(10) & "<SubscriberID>" & SUBSCRIBER_ID & " </SubscriberID>" 'int
    body = body & Chr$(10) & "<Password>" & PASSWORD & "</Password>" 'string
    body = body & Chr$(10) & "<VRM>placeholderVRM</VRM>" 'string
    body = body & Chr$(10) & "<Mileage>placeholderMileage</Mileage>" 'Mileage
    body = body & Chr$(10) & "<StandardEquipmentRequired>boolean</StandardEquipmentRequired>"
    body = body & Chr$(10) & "</VRMValuation>"
    body = body & Chr$(10) & "</soap:Body>"
    body = body & Chr$(10) & "</soap:Envelope>"

    With CreateObject("MSXML2.XMLHTTP")
        For r = LBound(regAndMileage, 1) To UBound(regAndMileage, 1)
            mileage = regAndMileage(r, 1)
            reg = regAndMileage(r, 2)
               'create your body here and concatentate in your mileage and reg variables
            .Open "POST", "protocol&domain/vrm/capvrm.asmx/VRMValuation", False
            .setRequestHeader "SOAPAction", "https://soap.cap.co.uk/vrm/VRMValuation"
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
            .send Replace$(Replace$(body, placeholderVRM, reg), placeholderMileage, mileage)
            response = .responseText
            With xmlDoc
                .validateOnParse = True
                .setProperty "SelectionLanguage", "XPath"
                .async = False
                If Not .LoadXML(sResponse) Then
                    Err.Raise .parseError.ErrorCode, , .parseError.reason
                End If
            End With
            'Do something to extract values
        Next
    End With
End Sub

See this for more info on Ranges and arrays.

Potentially you need to add Content Length and other information in the request.

SOAP requests

edited Feb 14, 2019 at 22:02

answered Feb 14, 2019 at 20:36

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Craig Cunningham Over a year ago

Hello QHarr, thank you for your quick reply. POST /vrm/capvrm.asmx/VRMValuation HTTP/1.1 Host: soap.cap.co.uk Content-Type: application/x-www-form-urlencoded Content-Length: length SubscriberID=string&Password=string&VRM=string&Mileage=string&StandardEquipmentRequired=string This is on the link above which I hope will be able to do all the work? I think just need to be able to plug the reg and mileage in to it for it to return a result? The code above was just a proof of concept and trying to get back into coding. My code was never going to be the solution.

QHarr Over a year ago

you would concatenate the values in. Can you post a screenshot of the network tab for that POST request (unless there is sensitive info shown)

Craig Cunningham Over a year ago

Hi QHarr, I'm ever so sorry for my ignorance but what do you mean by a screenshot of the network tab?

QHarr Over a year ago

where did you extract the above info from (in your comment)?

Craig Cunningham Over a year ago

The link in my original post shows various code to what I believe will drive the work. The code is from the bottom of that page under HTTP Post. There's also a section on HTTP GET. The only sensitive information would be the user credentials to test unfortunately. Regards

|

Collectives™ on Stack Overflow

How to create a HTTP GET in Excel VBA

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related