Importing Excel multiple workbooks with multiple tabs into a single Access 2010 db

[alansidman]

That line of code says to import an .xlsx spreadsheet. If you are trying to import something else. then we need to change the 10 to the appropriate value.



If you are importing an .xls file in 2000 format, then you will want the 10 to be an 8

Alan

[captdkl02]

Alan,

I am importing an .xlsx file. I am attached debug window with variables with values at time of runtime error.

[alansidman]

Is the Excel Spreadsheet from 2007 or 2010. If from 2007, change the 10 to 9.

http://ss64.com/access/acspreadsheettype.html

[captdkl02]

Alan,

I am running Excel 2010 and Access2010. I was able to pass a test with an excel workbook with 4 tabs with 4 rows by 6 columns with integers on each tab. My excel workbook is 15 tabs with a typical tab having 35 columns by 15 to 20 rows. Some cells have large amount of text in them. Most of the cells are text or dollars data types.

Any hints??

Thank you.

Dave

[alansidman]

I really don't have a clue at this point. I suggest you start a new thread showing the code you are using, explaining that it works on a test case of a workbook with 4 tabs, 4 rows and 6 columns of integers but fails on larger workbooks. Hopefully, one of the really smart folks on this site will jump onto a new thread and have an answer. Additionally, you may want to upload one of your workbooks that it fails on. Make sure to dummy up any confidential information that is in the workbook. Also be sure to tell them that you are using Access 2010 and Excel 2010.

Good luck
Alan

[captdkl02]

Alan,

I have been doing further tests. I copy my entire workbook tabs into a new workbook and it works without any password and there are no links to other workbooks. I took the new working workbook and added a password to the copied workbook using file->info->Protect Workbook->Encrypt with password. From the debugger I see it makes to the DoCmd.TransferSpreadsheet loop and bombs on the first spreadsheet it is adding to access table. I believe it trying to update links that is causing it to bomb.

[alansidman]

Your workbooks have links to other workbooks? And you think this may be your problem? I have no experience with this type issue and I am not sure what would cause them to bomb out. As I suggested earlier, create a new posting so as to get others who may have experienced this dilemma may get involved.

[JAttiya]

Thank you for the Import Template. It worked perfectly for me using Access 2010.