I was recently told I had to train one of my companies clients to access our API’s using Excel. This ended up being a miscommunication, and the client wanted to use C#. However, I spent a day re-learning Excel VBA before I got the correct information. Relearning involved writing a simple app with out API and doing a lot of googling to fill in the large gaps in my knowledge.
I decided to post the list of useful links I found here, mostly for my own reference. If I ever have to touch Excel again, I’ll add to this list, and perhaps curate it a little better.
- Working with Excel Tables Excel 2007 introduced a great feature called tables, Unlike lists you can’t query them through the Excel ODBC or OleDb drivers. However, from VBA you can.
- Creating a GUID (as a string) Guids are nice because they’re guaranteed to be random.Excel doesn’t store guids natively, but for my purposes, this was good enough.
- Determining if a worksheet already exists this is just one of those things I found hard to figure out.
Perhaps I might be inspired to rewrite the Excel Reverse DNS macro I wrote many years ago.