I remember back in the 1990’s, the biggest question in the corporate IT scene was: Will the Internet ever be of value to businesses and penetrate the holy sanctuary of the LAN?
How things become absurd with time, just like the law stating that it’s illegal for a woman to drive a car in main street, unless her husband is standing in front of the car waving a red flag (still in effect today in Waynesboro VA, USA, by the way).
The Internet has become a fundamental pillar of any successful business. New industries emerged and have become dominant and influential, and Internet technologies were also adopted in-house. An Enterprise Portal is but one example.
As Excel VBA is a platform for developing business information systems, it is therefore essential to have a way to access the Internet, public or in-house, in your VBA programs.
Here are a few use cases in which I was required to access the Internet or use Internet technologies in projects for my customers:
I’m sure you can recognize other use cases in your setting and for your customers!
To keep it simple enough, I will describe the basic concepts required to effectively do most of what you will probably be required to do.
The fundamental concept to always keep in mind is the REQUEST-RESPONSE nature of Internet communications.
There’s always a Webserver component, listening for REQUESTS sent by a Client component. The Webserver will then send a RESPONSE to the Client.
The Client and Webserver “understand” each other as they both “speak” the same language over the HTTP protocol. HTTP stands for: Hypertext Transfer Protocol.
The webserver may do nothing else but facilitate the HTTP communications with Clients, while forwarding requests to other servers for business logic, data persistency (Databases) and other processes.
If a Webserver needs to cater for tens of thousands of requests hitting every second – that’s quite a responsibility already.
HTTP is synchronous, meaning the Client is waiting to receive a response before passing control to the program controller to proceed to the next statement.
An HTTP Request/Response is a “package” of data, comprising several sections.
The Header section holds meta-data on the request, including special instructions to the Webserver, Webserver location, content length of the Request, etc.
The Request Header also holds Post parameters – see more about that later.
By adding SSL/TSL layers on top of HTTP (marked HTTPS), we add 3 dimensions of security:
HTTPS has become the standard for most business websites, up to the point that some browsers are unfriendly to non-HTTPS websites.
An HTTP (or HTTPS) request string is comprised of the following sections:
As you can see, in this example the password parameter is part of the request string, and that is not a secure way to pass such sensitive data.
Passing parameters like that as part of the request string is a method called Get.
To hide the passed parameters from the eye (and other preying listeners) we may opt to use the Post method instead. With Post, the parameters are part of the request Header, which is not part of the visible request string, therefore much more secure.
The Client constructing the request is responsible to declare the method used, and of course, the listening Webserver must be set to accept and authorize the request in this way.
The response received by the Client from the Webserver packages several sections, most importantly:
As I thoroughly explain and demonstrate in my online course: Beyond Excel Boundaries with VBA: Office, Files and Internet, you can send any HTTP request over an Internet connection from VBA using the WinHttp service library. Here’s a code snippet to send a request to some URL:
Dim winHttpReq As Object
Dim myURL As String
Dim result As String
Dim lngStatus As Long
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
myURL = "https://some-api-web-service?p1=value1&p2=value2”
.Open "GET", myURL
.waitForResponse 4000 ‘4 seconds before timeout is raised
result = .responseText
lngStatus = .Status
If (lngStatus = 200) Then
‘Success – we have our returned value in result
MsgBox "Web service error", vbOKOnly + vbExclamation, "Process Error"
As you can see, all the important elements of the Request and Response are handled in this code snippet.
The WinHttp service is the gate to HTTP communications. It is part of Windows and readily available for us in VBA (More about calling external libraries from Excel VBA in my Blog post: Calling External Windows Functions (DLL) From Excel VBA).
winHttpReq variable is an object type local variable I bind to the WinHttp service, immediately gaining access to all of its properties and methods.
Useful Methods you expect to find include:
Useful Properties you expect to find include:
Set the waitForResponse property to ensure your Client will not wait forever if the connection is lost or something else happens. This is the Client-side timeout, not the Webserver-side timeout setting.
As you can see, I verify the request was handled without any errors by checking the status code returned is 200.
As simple as it seems, always remember that “it takes two to Tango”. The challenge is usually tailoring the structure of the messages between the Client and the Server.
The Webserver expects a request in a very specific format, especially with regard to the passed parameters, while the Client needs to be able to handle any response coming back, sometimes requiring further parsing and processing once received from the Webserver.
Keep in mind also that the Webserver needs to be listening and approving requests submitted. Always make sure permissions are in place to honor the Client requests.
Please share this Blog post with your colleagues – I know they want to know this too!
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.