CODEWITHSUNDEEP
vbams-access
Bess Hardwick
Bess Hardwick

Reputation: 19

How to import all sheets of excel workbook to Access with VBA.

I have been given an excel file with 1429 sheets. I need to get all the data into Access 2013, either as one table or several, and am looking for a VBA method (or anything else that will work).

The code given in an answer here does not work for me though it seems to have been working for others a few years ago:

"Run-time error '91': Object variable or With block variable not set"

In reference to line "For lngCount = 1 To objWorkbook.Worksheets.Count"

Upvotes: 0

Views: 4388

Answers (1)

Bess Hardwick
Bess Hardwick

Reputation: 19

I solved it in the end and it turned out to be something really dumb: the code worked after I converted the source spreadsheet to .xlsx format. I ended up using the second code posted in the same answer I linked to before which imports all the spreadsheets to a single table.

Thank you for all your help, I learned a lot here!

Upvotes: 1

Related Questions