Combining multiple files into one work sheet

[fgwiii]

Good afternoon,

I have multiple files single worksheet files (30), each in it's own
directory that I would like to have combined into a single workbook. Each
file name is unique, however the midships of each file contains the date the
file was created "C:\XVG001\XVG001_20090713_Reviewed.xls".

Is there any way I can create a function or macro that will pull these files
from their respective directories and combine these sheets automatically into
one workbook?

Thanks

 

[Ron de Bruin]

Try this add-in
http://www.rondebruin.nl/merge.htm

 

[fgwiii]

For some reason when I attempted to add this to the library folder I received
an error concerning that the folder was locked for security issues. So I
ended up running from the desktop.

It worked but it combined them into one worksheet. What I need is for each
file an individual worksheet would be added to the workbook.

Thanks

 

[Ron de Bruin]

Do you want to copy the whole sheet
or only a range ?

If you want to copy the whole sheet see
http://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[fgwiii]

Ron,

The first couple of sheets seem to work perfectly and then I started seeing
data that did not appear to be correct. Some of the files may have been
opened by another user, and some are set to autofilter (although I don't
think that should matter when copying the worksheet).

Another ongoing issue I have had is with one of the columns in the reports I
am accessing. This problem has nothing to do with your programming. One of
the fields I access has a numbers in it such as "063740285007037" but when
they are copied and pasted into another sheet "6.37403E+13" is displayed.

Any thoughts?

Thank you for your help.

Fred