Import multiple Excel Sheets to one new table

[kvar]

I have an Excel workbook with multiple sheets, all the sheets have the same headers and are formatted the same. Problem 1 is I need some sort of loop so that all sheets will be imported, the names will vary so I can't use specific names to import. Problem 2 is that I need to create a new TempTable based on the format of these sheets and have them all import to that one table.
I need to do some cleaning up and updating of the data before I run append queries to have it moved to a couple of permanent tables.
I know I've written the VBA to have a new table created on import and later deleted after all my queries run, I just can't for the life of me remember how I did it!
Appreciate any help or suggestions!!

 

[Frothingslosh]

TransferSpreadsheet allows you to designate the destination table. If that table exists, it appends records. If it doesn't exist, it creates the table, THEN appends records.

As to going through the sheets without using names, you can use a For Each...In...Next loop to cycle through all the sheets in the workbook.

 

[kvar]

I got it to loop through the sheets and create a TempTable. However there is a type conversion error on one of the fields, which I think is odd since it's creating the table. It happens to be a field that I don't need anyway so I'm not really worried about that part, and I can write the VBA to delete the Import Errors table when I'm done. BUT it creates an Import Errors table for every worksheet in the workbook. There are about 20 of them and the sheet names will vary so I'm not sure how to programmatically delete those. Is there some code to just prevent them from being saved in the first place?

 

[marlan]

Hi,
You can maybe link to the excel instead of Importing it to a temp table. You avoid the 'Import Errors' tables, and can cast the data to your needs (Cint(), Cdate(), Cstr() etc...)

 

[Frothingslosh]

In that case, I'd recommend a permanent staging table in the back-end, or creating a staging table in a disposable database (side-end). Make every field either text or, if you need more room, memo.

The drawback is that if you put it in the back-end, you'll need to compact regularly, while using a side-end involves more coding and you'll need to delete the side-end once you're done.

The majority of the time, the type conversion errors you're talking about come from importing text data into numeric fields. Remember, when TransferSpreadsheet creates a new table, it determines the data type by looking at the first ten records. If the first ten entries in a field are dollar amounts, it creates a currency field. If, for record 11, someone entered 'None', instant type conversion error.

Edit: If you really get desperate, you can try using my import procedures over in the Code Repository, but be advised they are both a more complex routine than you probably need, don't really allow for skipping of rows, and work very slowly, as they import a spreadsheet via individual cell reads rather than TransferSpreadsheet. They do, however, 'clean' data a bit on import.