Import Access Data into Excel (via VB and ADO)

I had a lot of data in MySql in a rails application, but I moved to Mongo on a new application and wanted to give the client a nice archive of excel files for each client. It has been a long time since I’ve worked with visual basic, but it came back quickly.

I was amazed how clunky the interface and I wrote some pretty redundant code, but this was my Saturday and I tried to get this out the door quickly. My basic workflow was to load an office trial version on a virtual machine, then install SQL server on windows and set up an ODBC connection string which I used to import all data into access. In access, I just wrote a bunch of sql strings and used access to automate excel via VBA.

Leave a Reply