Import 5 excel workbooks into 1 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).

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:

Please Login or Register  to view this content.

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

Currently have the following on button click in form:

Please Login or Register  to view this content.

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!!

[Kyle123]

Hi,

Having read through your other thread, I'm not sure why you didn't do the whole project in Access?

I'm also not sure what you mean by blank rows, do these need excluding or including? If someone deletes a row from the input sheet this would create a blank row in your summary sheet - Do these need excluding or are you preventing this from happening?

If so, to just get the cells with data in them you could set up a named range as follows and import this:

Please Login or Register  to view this content.

If you have blank rows, you would probably need to do some modification first.

The way I'd look at doing this from access is as below - saves fiddling in Excel and gives you a bit more control, also takes care of the blanks problem!

Let me know if you want me to talk you through any of this

Please Login or Register  to view this content.

[pjkeady]

Ofcourse, SQL import from Access...that is another way to look at this, now to just iterate through the 5 excel workbooks using my other code and perhaps this will work?? I will get to work and see if this will work with what I have so far. Like putting puzzle pieces together isn't it?

Thanks.

[Kyle123]

Its handy doing it like this because it's easier to write a query to get rid of the pesky blanks!