Import from 5 Excel Workbooks into one Access Table

[pjkeady]

Hello again all...

I have 5 excel workbooks (all identical) that have a hidden worksheet called MTS_datatable. This worksheet has formulas instead of just plain values.

On a Form Button click in Access I want to iterate through the 5 workbooks and import the entire "MTS_datatable!" sheet values (they have headers) into one Access table called DataTable (this table has all the same fields except it has an ID field where it assigns each imported row a unique ID).

Also, importing Columns A:J and only down to the last row that has actual values in it - it is currently importing 100 rows where only 16 have values - because the rest have formulas, so I get 16 data rows in access table and 84 blank rows. I even tried putting in a NEW sheet called datatbl and doing an ON CLOSE event where it would do the following:

Sheets("MTS_Datatable").Range("A1:j2000").Copy

Sheets("DATATBL").Range("A1:j2000").PasteSpecial Paste:=xlValues

Now I have values only, but it still imports the full 100 rows??

Currently have the following on button click in form:

DoCmd.TransferSpreadsheet acImport, 8, "DataTable", "I:\Devprojects\MTS\mts\MTS_ENTRY1.xlsm", True, "MTS_datatable!"

I have great code that looks in "I:\Devprojects\MTS\mts" folder and lists the five excel sheets. Now I just need to figure out the next step, which is a button click that imports the worksheets in the found workbooks into the one master DataTable in access while ignoring all blanks (not really blanks they have Transition Navigation Keys (') in them).

The attached is an example of what I am importing (see the sheet "datatbl!")

See this post if you need more background on this sheet http://www.excelforum.com/excel-prog...t-on-save.html

Thanks!!