Exporting MS Access to Excel using VBA from within Excel – MS Access dB table stacker

Posted on by

Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker.

In short, open up the SpecTables table of each, export those tables that we find to Excel sheets, then use my stacker and stack them all on top of one another. Since the stacker removes any need to worry about column order – AND since it tells you what columns are “lost” we can run it many times until everything is under one umbrella.

Here are the links that helped me export from MS Access to Excel sheets but using Excel as the engine which holds the VBA that makes this all work

  • http://stackoverflow.com/questions/1849580/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory
    • here was the heart of the macro tip from this guy

[vb]

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName

[/vb]

  • http://www.mrexcel.com/forum/excel-questions/643188-excel-run-access-docmd-transferspreadsheet-into-activeworkbook.html

I had to add the MS Access reference (I am using both version 14 or MS Office 2010) and the reference for DAO 3.6 through the “Tools-Add Reference” menu in Excel’s VBA editor.