Import multiple excel Worksheet / workbook to a single table in Access 2010

[tunna991]

This is a situation I recently encountered. My department has over 500 excel files with multiple tabs. We want to import the data from specific tabs into one excel database. These tabs will contain the same columns. I am not good with VBA and could not find a solution after much googling.

Also each excel file has an information tab from which I want to extract data from and populate with the data I am importing. This information file lists the date and location in separate cells.

I hope I am making sense here.

 

[SkipVought]

The thing that is not working are the three extra columns I need to capture from the information tab.

In the output table in the Master sheet you need three extra columns named From:, To: and Agent:

My procedure searches for those three values (in sINFO() array) in each workbook’s Information sheet and captures the values in the adjacent column that finally gets put and propagates in the last three columns of the output table.

I’ll upload the workbook I’m using. I also used your source workbook in my test folder.

Regarding helping you modify your code, I’ll answer general questions, but I’m not going to dig through your code to fix it. I gave you code that does what you requested and I can help you figure out what you need to do to make it work in your environment.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tunna991]

Thanks for your help thus far. I would appreciate seeing the workbook you are using.

 

[SkipVought]

Here's my workbook upload.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tunna991]

Thank you that really helped. I was able to make it work. Basically I was placing the names of the three columns in the wrong place.

One more thing before I can close this thing off, is that is there a way to have another column with the Tab name the record is being pulled from?

For example if the data is being copied from the 'Medicines' tab, I would like to add in an extra column 'Category type' and populate it with 'Medicine'

 

[SkipVought]

So instead of three extra columns, you’d like four.

I’d put them in this order: Category type, From:, To:, Agent:

Tab name is Worksheet.Name. In the code, the Worksheet is in the For Each ws loop.

Assign the ws.Name after the data has been pasted into the output table, inside the With wsOUT. Use the technique like 'put the values in the right-hand columns, only this column is iCOL - 3.

Then use 'copy the values down to the last row in the output table as a template to propagate the value down to the last row of the table.

Let me know how that works for you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]