Excel VBA and the Internet

Have you ever needed to get data over the Internet into your VBA program? * Maybe your automation process require that your VBA application update another system using Internet protocols? * Today we explore and demonstrate how to integrate Internet-based systems with your VBA program

Why Internet with Excel VBA?

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.

Business Use Cases

Here are a few use cases in which I was required to access the Internet or use Internet technologies in projects for my customers:

• Integration with Google Sheets collecting data using Google Forms from clients
• Software licensing management for an Excel-based software product
• Recording usage information (Log) on a webserver
• Web-scrapping products catalogs in Websites
• Consuming currency exchange rates from a banking web-service

I’m sure you can recognize other use cases in your setting and for your customers!

Understanding the Basics of the Internet technology

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.

The browser you use to visit a Web page is a Client sending HTTP requests to a Webserver hosting your destination Web page. The response received is typically a bundle of HTML + CSS + JavaScript that the browser interprets and renders for you to see and work with. In turn, you generate subsequent HTTP requests (by clicking a link, or submitting a form, for example).

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:

• Authentication: acknowledge you’re connecting to the correct server
• Encryption: protects the data in its transport
• Data Integrity: ensures data sent is the data received

HTTPS has become the standard for most business websites, up to the point that some browsers are unfriendly to non-HTTPS websites.

Sending HTTP Requests

An HTTP (or HTTPS) request string is comprised of the following sections:

http://example.com:80/articles/January/43156.htm?name=mor&pass=1234

• Scheme: protocol identifier
• Host: Webserver name
• Port: a “channel” number through which connection to the host is requested
• Path: location and data requested from the host
• Query: additional information (parameters) for the host to use while processing the request

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.

Receiving HTTP Responses

The response received by the Client from the Webserver packages several sections, most importantly:

• Status Code: a numeric code indicating the status of the request handling. Some popular status codes are:
  • 200 OK. indicates a successful processing of the request
  • 401 Unauthorized
  • 404 Page not Found
  • 408 Timeout
  • 500 Internal Server Error
• Header: Metadata on the request process
• Body: the requested data, if requested.

Sending an HTTP Request from Excel VBA

As I thoroughly explain and demonstrate in my online course: Business Automation with VBA: Accessing the 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”

        With winHttpReq

            .Open "GET", myURL

            .Send

            .waitForResponse 4000 ‘4 seconds before timeout is raised

            result = .responseText

            lngStatus = .Status

        End With

        If (lngStatus = 200) Then

            ‘Success – we have our returned value in result

        Else

                MsgBox "Web service error", vbOKOnly + vbExclamation, "Process Error"

        End If

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:

• Open: opens the connection using the provided Request string and Get/Post method
• Send: sends the Request data package (all sections)

Useful Properties you expect to find include:

• Status: the numeric status code returned from the Webserver
• responseText: the body (data) of the message you requested (e.g. customer information from the Database)

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.

Final Comments

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.

Working effectively with Web pages requires understanding the HTML markup language, CSS styling, specific files formats such as XML, JSON and possibly JavaScript.

Apart from JavaScript, all required technologies with hands-on code and in-depth explanations are covered in my online course: Business Automation with VBA: Accessing the Internet.

Please share this Blog post with your colleagues – I know they want to know this too!