Call Web Services From VBA

Created a class to call web services from VBA.

In the past the only thing available for calling web services was Microsoft’s Soap Toolkit. In order to use this component, you would have to redistribute the OCX. Unfortunately, the toolkit hasn’t been updated since 2003 and is no longer supported. IMHO it was cumbersome to use anyway.

The class I wrote uses MSXML’s XMLHTTP to do all the heavy lifting. For testing, I created a simple web service with six methods :

HelloWorld returns the string “Hello World”.
SayHello takes first name and last name parameters and returns a greeting.
GetAFile takes an integer image index and returns an image file byte array.
GetXml returns a demonstration xml string.
GetDataTable takes number of  data table rows to return.
SendAFile receives a file byte array and returns byte size string.

The class auto-detects the response data type – string, xml, data table or file data. Web services return byte arrays as base 64 encoded strings which I decode into a byte array.

Initially I used MSXML for iterating data table xml but found it to be too slow. Switching to XSLT and parsing the xml as a string was much faster. In the attached workbook I retrieve and write 30,000 cells worth of data table data in under 1 second. Luckily I created a page that assists transforming XML 😉

Extract VBA From XLSM and XLAM Excel Workbooks

I created a C# .NET class to extract VBA from XLSM and XLAM Excel workbooks – even if password protected. I used Mirosoft’s Open XML SDK 2.5 to read a workbook’s VbaProjectPart stream. From the bin stream I extract the VBA source code using Structured Storage API’s.

Microsoft’s Office VBA File Format Structure Reference

I have even been able to retrieve VBA from corrupted workbooks that won’t even open in Excel. See the class in action here.

Extract VBA From XLSM and XLAM Excel Workbooks
Extract VBA Source Code From XLSM and XLAM Excel Workbooks

Generating String and File Hashes Using VBA

I created a class for generating string and file hashes using VBA and the cryptographic api.

MD2, MD4, MD5, SHA1, SHA256, SHA384, and SHA512 hash algorithms are supported. The MD5 algorithm is used by default. The class supports both 32 and 64-bit versions of Office.

Example Usage :

    Dim cH As clsHash
    Dim sHash As String

    Set cH = New clsHash
    sHash = cH.HashString("foobar")