xlAWS – Excel VBA Code for accessing Amazon’s S3 and SimpleDB

I’ve been using Amazon’s S3 service from within Excel for sometime now and as there are no libraries or examples for calling AWS services from VBA (or VB6) I had to roll my own. As with most things Excel, getting the job done always triumphs over elegance and industrial strength implementations, in other words it was all a bit of a “dog’s dinner”. To remedy this and to share my experience of using S3 from within a VBA/VB6 environment, I decided to re-factor the code and to assemble it into a more re-usable form; the end result is xlAWS.

It was going to be called xlS3, but while doing the exercise SimpleDB appeared on the scene, so I decided to try accessing it from Excel, particularly as both products have a lot in common; both “simple”, both “schema-less” data stores. Like the S3Helper code, the simpleDBHelper module is less of comprehensive library, more a collection of useful functions which (hopefully) make working with AWS a bit easier.

To use this code library, you’ll need to have a good grasp of the S3 and SimpleDB APIs and be reasonably proficient with VBA. This is not an end-user tool, it’s for VBA (or VB6) developers. There’s a README and some basic examples within the Excel VBA project to help you get started. Code is released “in the spirit” of LGPL, you can use it how you wish, but if you add something new to the “library” (or find/fix a bug) do let the rest of us know.

As I’ve not been able to find a pure VBA implementation of the HMAC-SHA1 hash algorithm (and I couldn’t see an implementation within the standard “Microsoft Enhanced Cryptographic Provider” ) I’ve wrapped the open source XySSL SHA1 HMAC C code in a VBA friendly DLL. This DLL (and the source, under LGPL) is included in the zip file as AWS authentication requires SHA1 HMAC signatures.

You’ll also obviously require an AWS account. Credentials are stored within the workbook’s custom properties and can be encrypted via a “key file” if required. If you intend to use this code within VB6 (or Proto) you’ll need to provide your own implementation of the AWSKeyData class in order to use a non-Excel persistence store.

You can download the project ZIP file from here.

Have fun.

UPDATE

The simpleDB signature code no longer works as it used Signature Version 1 (which it turns out was insecure), you must now use Signature Version 2. It should be simple enough to change, but as I no longer use SimpleDB I’ve not done so. Also, see below for the many non-DLL methods for calculating HMACSHA1 hashes.

An alternative for calculating HMAC-SHA1 signatures in VBA/VB6 is a Google Checkout supplied COM DLL see http://bit.ly/9CIKtM

There’s the bones of a pure VBA HMAC-SHA1 implementation here http://www.eggheadcafe.com/software/aspnet/32187540/hmac-sha1-challenge.aspx

>>>>>>UPDATE: 17-April -1011

Here’s pure VB6 HMAC-SHA256 implementation it should be easily changed to provide and it now includes a HMAC-SHA1 facility (see comment below)  http://www.vbforums.com/showthread.php?t=635398

<<<<<<<<<

File this under: “Ya learn something new everyday”:

It seems you can access a significant subset of .NET libraries from VB6/VBA/VbScript (including System.Security.Cryptography.HMACSHA1) see this and dotNet in VBScript.

Advertisements

15 responses to “xlAWS – Excel VBA Code for accessing Amazon’s S3 and SimpleDB

  1. Heya:

    Would you mind posting this in the community code section of the resource center for Amazon SimpleDB?

    http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=114

    Simply click on the ‘add document’ link on the right.

    You might also want to post an announcement in the simpledb forum.

    cheers,

    Charlie
    SimpleDB, product manager

  2. Pingback: Java - at the eye of a perfect storm « Gobán Saor

  3. Pingback: xlAWS - 100,000 downloads? « Gobán Saor

  4. Bob Hamilton

    This stuff is a bit over my head but it’s very interesting! Does anyone have a simple VB6 example up and running that I could take a look at?
    Thanks!

    • Bob,

      Sorry I obviously missed this, but better late than never. I don’t have a VB6 version of this, but the essence should be the same. The SimpleDB code will no longer work in VBA or VB6 however as it was coded using a now discontinued signing method (security problems it seems), needs to use Signature Version 2.

      Also see the many alternatives for n0n-DLL HMAC-SHA1 hash calculations.

      Tom

  5. Pingback: Steam Powered PowerPivot | Gobán Saor

  6. Pingback: S3 as an Excel hub | Gobán Saor

  7. Bob Riemersma

    In case anyone is interested a slight update to the VB6 HMAC-SHA-256 code plus a conversion to HMAC-SHA-1 (with RFC 2202 Test Vectors for validation) is now available at http://www.vbforums.com/showthread.php?t=635398

  8. Thanks Bob,

    I’ve updated the post to reflect this.

    Tom

  9. Why aren’t you using S3 anymore?
    I’ve been thinking about using it with classic ASP.

    • What gave you the impression I don’t use S3 any more? I, and my clients, use it every day! I don’t use SimpleDB, but that’s because its use-case is as a back-end database to a Web App (such as ASP) but I mainly deal with client-side “detached” datasets, hence my preference for SQLite/CSV/JSON transported via S3.

      Tom

  10. Tom,

    Thanks for replying. I’ve been thinking of storing my data (SQL Server) onto S3 to spare some memory from my own server.

    I use Classic ASP and would love to connect it. Any advice? Have you ever found a simply application with Classic ASP that uses S3?

    • Not really familiar with Classic ASP, but VBA and VBScript are very similar (I regularly use VBScript example code I’ve found on the web in VBA modules), Therefore the S3 code to be found in the code above and in my Excel microETL add-in should work in ASP.

  11. Bob Riemersma

    Just a note: I recently imported my VB6 HS256 class into Excel to be sure and it worked there with no changes. The HS1 class should be just as clean. Thus in case it isn’t clear, this does provide a VBA implementation. This was only tested by me using Excel 2003 (11) VBA 6.5.