CODEWITHSUNDEEP
excelms-accessvba
AbhiRoczz...
AbhiRoczz...

Reputation: 252

I need a VBA script to run on excel to copy all my access tables columns in Excel sheet

I have about 10 *.mdb Access files of office version 2003. I am working on client machines on which I have only Excel 2003 installed but not Access.

Each database file has around 50-100 tables in it and each tables has around 20-30 columns. I need to run a VBA script or any import on Excel so that I could copy all the Access tables and its columns into my Excel sheet. I do not require data of Access tables only the table and its column names is what I require.

Upvotes: 0

Views: 1076

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

The plan:

For Each MDB In YourMdbs (file in folder, hard-coded list, ...)
    For Each TABLE In MDB (filtered, no MSys tables, ...)
        XLS = FSpec to non-existing .xls corresponding to MDB
        Execute on ADO connection to XLS:
          SELECT * INTO <TABLE> FROM <TABLE> IN "<FSpec to MDB>" WHERE 1=0

based on: IN CLAUSE

This approach will export the .mdbs table schemata to .xls sheets without any fuss with ADOX schema.

WRT comments:

  1. The program from VBS Express documents the table and the field names, my strategy copies the tables (without data because of the WHERE clause) to the sheets
  2. ADO magic works on computers without MS Office installed just fine

Upvotes: 2

Related Questions