Consolidate data from 4 Workbooks...

[Matty]

Hi,

I have 4 Workbooks saved on a shared drive that each contain a Worksheet with the same name. These 4 Worksheets contain the same table but different data is populated in them.

I am looking to create a new Workbook that will bring the data from the aforementioned 4 Worksheets together into a single Worksheet. The link needs to be dynamic, i.e. if anything in the 4 Worksheets changes, the data in the consolidated Worksheet should update.

I've done this kind of thing in the past using MS Query (via a union query), but I find MS Query a little flaky. So I'm looking for advice about how others would go about doing this.

Thanks in advance for any support offered.

Matty

 

[alansidman]

If you are familiar with MS Query and you have MS Access available, you could link the four worksheets in Access and do a Union Query there. Writing a SQL statement for a union query in Access is not cumbersome at all so long as all the fields are arranged the same for each table. You can then export the information back to Excel as necessary.
Alan

 

[Matty]

Hi Alan,

Thanks for responding.

Indeed, I have used Access for this kind of thing in the past too, but as the data sets are small, it seems like I'd be using a sledge hammer to crack a nut.

I'm curious to know whether there's a VBA approach for this of thing. Anyone?

Cheers,

Matty

 

[xenou]

VBA doesn't offer the equivalent of a SQL Union operation, unless you write the algorithm yourself to combine the data sets and remove duplicates. Though Excel 2007/2010 does have a remove duplicates option now. And "how to remove duplicates" is a common topic if you start searching, so it may be an option to first merge all the data, then remove duplicates. You can use DAO or ADO in place of MSQuery (less flaky, but requires more coding by hand, and would need to be in macro-enabled workbooks).

 

[Matty]

Hi xenou,

Duplicates will never be an issue.

I'm interested in the ADO solution. Could you show me how I would go about implementing this option for this particular issue?

I've played with ADO briefly before but never got a real handle of it.

Thanks,

Matty

 

[xenou]

There are examples here:
http://www.erlandsendata.no/english/index.php?t=envbadac

I'm not sure what you mean by "dynamic" and updating immediately whenever changes are made - that would require more code and more rules about data management. Why not one workbook instead of four. You break up the data into four worksheets only to have to put it back together again (?). Or put it into access which handles multiple users with ease.

 

[xenou]

BTW, if you don't have duplicates you don't need a UNION query. Just query each sheet in turn and append it to the master sheet. That should be a simple job for ADO, DAO, or MSQuery - pick your poison. I find MSQuery flaky too but it should be able to handle something straightforward like this.

 

[Matty]

Thanks xenou. I'll have a play...

Matty