Using Visual Basic VBA with HTTPS and SSL

Using Visual Basic VBA with HTTPS and SSL Is it possible to access web pages with HTTPS and that require login/password using Excel? Well, yes and no. Heres the deal and why its not so straight forward. First, Lets  Define Terms HTTPS is by convention the identifier for what is called SSL (Secure Sockets Layer). That doesnt 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 isnt 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, lets shake hands over this encryption thing so that whatever we say from now on wont get intercepted by some bad guy. And when thats done, go ahead and send me the page addressed by the URL. The server will send back the key information for setting up an SSL connection. Its up to your computer to actually do something with it. Thats 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 wont do that without some additional programming. The servers 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, lets use the SSL connection that is used by Googles 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. Youre 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. Its 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 requestIf the server asks for a signed client certificate, resend the request after attaching the certificate contextIf 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 doesnt 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 servers 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 dont expect to write the code that does it in just a few minutes.