Export Access Tables into Excel Worksheets/Tabs

[skydivetom]

Hello experts:

I need some assistance with exporting all tables from an Access DB into a single XLSX spreadsheet (with separate worksheets for each Access table).

Attached are 2 DB where DB_01 includes 2 tables + VBA module; and DB_02 includes 3 additional tables (to be copied into DB_01 for 2nd execution/test).

Process #1:
1. Open "DB_01" (which includes 2 tables).
2. Open module 'M01_TransferTablesIntoExcel'... and run/execute the sub routine which shall create the XLS in the same directory.
3. Open spreadsheet "REF_Tables.xls"... which should include 2 worksheets "TBL_FIRSTNAME" and "TBL_ITEMS".

... so far so good!



Process #2:
1. In "DB_01", delete the 2 tables.
2. Open "DB_02" and copy all 3 tables into "DB_01".
3. Open module 'M01_TransferTablesIntoExcel'... and run/execute the sub routine.
4. Open the XLS again.

What should have happened:
- XLS should show the 3 tables from "DB_02".

However, what did happen:
- While the 3 DB_02 tables were exported into the XLS, it also included the 2 *deleted* tables from original DB_01.
- While testing the VBA multiple times, the original DB_01 tables sometimes also displayed worksheet names equal to, e.g., "~TMPCLP169361" or "~TMPCLP169521"

My question:
- How should the VBA export routine be modified so that only the existing tables (not hidden and/or deleted ones) are exported into the XLS/XLSX?
- Also, I tried using "acSpreadsheetTypeExcel12Xml (vs. acSpreadsheetTypeExcel9) to get the export into current format "XLSX". That threw an error though. What's the correct syntax for exporting into XLSX format as well?