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
Another alternative for calculating HMAC-SHA1 signatures in VBA/VB6 is a Google Checkout supplied COM DLL see http://code.google.com/apis/checkout/samples/Google_Checkout_Sample_Code_ASP_InstallTxt.html
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
Will do Charlie.
[...] for many tasks, Excel, backed by web-service aware VBA will continue to be an option, my xlAWS library and xLite codebase will continue to be useful. Obviously, Excel is a natural front-end to Palo [...]
[...] from my www2.gobansaor.com bucket. This is the S3 bucket in which I store the xlAWS zip file, xlAWS being a “library-of-sorts” of VBA/VB6 helper code for accessing Amazon S3 and [...]