Accessing a Secure Web Site Using VBA

Is it possible to access web pages with HTTPS and that require login/password using Excel? Well, yes and no. Here's the deal and why it's not so straight forward.

First, Let's Define Terms

HTTPS is by convention the identifier for what is called SSL (Secure Sockets Layer). That doesn't really have anything to do with passwords or logins as such. What SSL does is set up an encrypted connection between a web client and server so that no information is sent between the two "in the clear" -- using unencrypted transmissions. If the information includes login and password information, encrypting the transmission protects them from prying eyes...but encrypting passwords isn't a requirement. I used the phrase "by convention" because the real security technology is SSL. HTTPS only signals to the server that the client plans on using that protocol. SSL can be used in a variety of other ways.

So...if your computer sends a URL to a server that uses SSL and that URL starts with HTTPS, your computer is saying to the server:

"Hey Mr. Server, let's shake hands over this encryption thing so that whatever we say from now on won't get intercepted by some bad guy. And when that's done, go ahead and send me the page addressed by the URL."

Read More

Why Use a Secure HTTPS Website

By Jennifer Kyrnin

The server will send back the key information for setting up an SSL connection. It's up to your computer to actually do something with it.

That's 'key' (pun...well, sorta intended) to understanding the role of VBA in Excel. The programming in VBA would have to actually take the next step and implement the SSL on the client side.

'Real' web browsers do that automatically and show you a little lock symbol in the status line to show you that it has been done. But if the VBA just opens the web page as a file and reads the information in it into cells in a spreadsheet (a very common example), Excel won't do that without some additional programming. The server's gracious offer to shake hands and set up secure SSL communication just gets ignored by Excel.

But You Can Read the Page You Requested in Exactly the Same Way

To prove it, let's use the SSL connection that is used by Google's Gmail service (which starts with "https") and code a call to open that connection just like it was a file.

This reads the web page like it was a simple file. Since recent versions of Excel will import HTML automatically, after the Open statement is executed, the Gmail page (minus the Dynamic HTML objects) is imported into a spreadsheet. The goal of SSL connections is to exchange information, not just read a web page, so this is normally not going to get you very far.

To do more, you have to have some way, in your Excel VBA program, to support both the SSL protocol and maybe to support DHTML too. You're probably better off starting with the full Visual Basic rather than Excel VBA. Then use controls like the Internet Transfer API WinInet and call Excel objects as needed. But it is possible to use WinInet directly from an Excel VBA program.

WinInet is an API - Application Programming Interface - to WinInet.dll. It's mainly used as one of the major components of Internet Explorer, but you can use it directly from your code as well and you can use it for HTTPS. Writing the code to use WinInet is at least a medium difficulty task. In general, the steps involved are:

• Connect to the HTTPS server and send an HTTPS request
• If the server asks for a signed client certificate, resend the request after attaching the certificate context
• If the server is satisfied, the session is authenticated

There are two major differences in writing the WinInet code to use https rather than the regular HTTP:

You should also keep in mind that the function of exchanging a login/password is logically independent of encrypting the session using https and SSL. You can do one or the other, or both. In many cases, they do go together, but not always. And implementing the WinInet requirements doesn't do anything to respond automatically to a login/password request. If, for example, the login and password are part of a web form, then you might have to figure out the names of the fields and update the fields from Excel VBA before "posting" the login string to the server. Responding correctly to a web server's security is a big part of what a web browser does. On the other hand, if SSL authentication is required, you might consider using the InternetExplorer object to log in from within VBA...

The bottom line is that using https and logging into a server from an Excel VBA program is possible, but don't expect to write the code that does it in just a few minutes.