Import from 5 Excel Workbooks into one Access Table

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:

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

I think you can import from hidden sheets in 2007...at least, I think I tried it and it worked fine. For my need, they have to be hidden for the users who use the sheet, but when it comes time to import I guess I could go in and unhide each of the five sheets manually (or programmatically). No biggie there.

Here is what I am using code wise to list the sheets found in a certain directory in a listbox on an access form, then I have a click that imports them....this is easy when they are simple .csv files (which I am also thinking, hmmm could I just do a programmatic save-as in excel and save the hidden sheet ONLY as a .csv file in this directory, because when that happens, ONLY the non-blank rows come in...perhaps .csv is the way to go?? Just brainstorming.)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now on import_click() I have the following for importing .csv files (this is all code from another database of mine I am implementing for this new database).

Please Login or Register  to view this content.

This will probably do everything I need - it works fine for my old dbase except:
1. It imports into separate tables
2. I need help with getting the worksheet from Excel into a .csv file or somehow just listing the workbooks and then importing the ONE hidden sheet without the blank lines (preferred).

Have you had a look here?

It seems you posted the same thing twice? I answered on your other thread - my suggestion will import into a single table. Does this do what you need?

Thanks Kyle123 ...

I didnt mean to post it twice in two different areas, it wasnt getting any looks in the access forum for awhile and I started to wonder if it wasnt more of an Excel question since the problem I am really having is in Excel.

http://www.excelforum.com/excel-prog...ess-table.html

To answer a question of yours, I am doing it in excel because the front end users are comfortable with excel and I want to make it as easy as possible for them. I love Access and would have made all in access, but with 5 different people all over the state trying to put information into it, access wouldnt work through our company intranet so I would have to go with SQL server and VB Front End which was way too much for the simple data we needed.

*I am looking at your code now, perhaps it will work!!! Fingers crossed, thanks so far to everyone taking the time to help me on this

No problem

Done the Excel/Access set up loads in the past it's handy and quick, though I always went down the route of having Excel insert directly into Access rather than pulling the data out with Access. In the end I moved everything to SQL server and ASP.Net and never looked back!!

Yeah just completed a project using SQL server and ASP.Net and it worked okay, the problem is speed issues and it with a slow infrastructure I am getting tired of complaints about refresh times on my AJAX updatepanels and having to jump through IT security hoops. So for this little excercise decided to keep it simple and stick with an excel sheet for each group in the state and since we only need to report once a year, I would just have an access dbase on my desktop that imported the data on a button click once a year.