Merging multiple excel files into a single doc

  • Thread starter --------------------
  • Start date
?

--------------------

I've got about 40 excel documents. Each contains the same number of
columns, and 25 lines worth of data.

Rather then have 40 files, I'd like to have a single file with the data from
each of the indvidual files all on this single file.

Up until now, I've opened each doc, done a copy paste from the original doc
into a master doc.

This was ok for a while, but it appears this may be a regular thing I'll
need to do. Is there any way to automate this process. The files will all
be the same, in number of lines of data and number of columns.

Thank you
 
R

Ron Coderre

I like this way because my users are more comfortable breaking SQL code than
VBA code. :)

You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):

My example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each wkbk must be named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query
Databases: Excel Files
Click to expand...

Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.

At The last screen select The View data/Edit The Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT *
FROM `C:\Inventories\Dept1111`.rng1111Data
union all
SELECT *
FROM `C:\Inventories\Dept2222`.rng2222Data
union all
SELECT *
FROM `C:\Inventories\Dept3333`.rng3333Data
union all
SELECT *
FROM `C:\Inventories\Dept5555`.rng4444Data
union all
SELECT *
FROM `C:\Inventories\Dept5555`.rng5555Data

Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.

You can edit the query at any time to add/remove data sources and/or fields.

Note: The apostrophes used in the SQL code are located on the same key that
has the tilde (~) on it.

First, try it on just a couple workbooks. If successfull, add the rest to
the SQL code.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top