Merge several workbooks with something like a query

[gandreso]

Last year I got helped from the user MickG with a macro that would make a unique list depending on the first column. Here is the thread; (http://www.excelforum.com/showthread.php?t=1164541).
Now im trying to implement this in another of my projects but I have to change the code a bit. Or maybe there is a better way to solve my problem. First of all I have a excelfile with 21 columns (StartDoc) with data in a couple thousands of rows. Then there is another file with 3 columns with data where the first column is a match of a column in my StartDoc. In the same way there is a third file with two columns where the first is a match to another in my StartDoc. Maybe you see how this is working? In short terms I want to merge several excelfiles with each other since there is link to my StartDoc. I know its possible to do this in Microsoft Access with a query but I would like to exploit the possibility to do this in excel. Anyways I recently thought about maybe its possible to adjust the code below to my cause. I tried to adjust the code to the correct columns and is working but the problem is that the data is not unique in the same way as my previous problem. For example lets assume that one of my files is a phonelist where column 1 is a name and column 2 is a phonenumber. In my StartDoc I then have only the names but I need the phonenumbers from the other file. The problem with the code below is that it only accept one unique name but a name in my StartDoc can appear in hundreds of rows. Anyone got any ideas how I can work around this?

Please Login or Register  to view this content.

[dflak]

Take a look at this article: http://www.excelforum.com/showthread.php?t=1168850. You can read worksheets in via MS-Query. I would recommend importing three separate tables and then using VLOOKUP or Match / Index to populate the main table from the other tables.

MS-Query returns data as Excel Tables, so any formulas you put in columns to the right of the returned data will be "remembered" when the data are refreshed. I suggest you go to Data -> Connections and in the properties for each connection, turn off Background Refresh.

It may be possible to open all three workbooks in the same MS-Query. I've not tried it. The issue is that the query builder in MS-Query is poor - I generally advise people to develop the query elsewhere and copy the SQL to the command string.