Consolidate multiple workbooks (...and adding new files in the masterworkbook on the fly)

[sokratez]

Hi There,

I am looking for a solution that can combine multiple work books in to
one workbook and adding additional files in the master workbook on the
fly.

We are a marketing team that are working with a budget file per
product. The number of products are increasing throughout the year,
Therefore I would like the master file to gather all the data from a
specified file and I would like the file to look in to the folder to
see if new files have been added and if so add the data to the master
file,

The data is always placed in the same way in the document.
FILENAME.xlsx --> Budgets --> A20:E35. I would also like to have the
file name as well to identify the data in the master sheet.

My excel wizard colleague tells me that this isn't possible in excel,
but it must be with some kind of VBA solution? I know some very basic
VBA but I would never be good enough to write the code snippet my
self.

Any words of advice? Solutions?

/S

 

[sokratez]

I guess that didn't really come out as clear as I thought

So I want a masterfile that gathers data from a bunch of files all
gathered in one folder. My challenge is that the number of files in
the folder increases throughout the year, so I need to add that data
in the master file as well. As it is a rather large number of files
then I would like the data to be added automatically.

/S

 

[JLatham]

Contact me directly via email about this. Remove spaces for valid email:
HelpFrom @ JLatham Site .com
Remind me of what you need in the email (or provide a link to your original
posting).

This is not that difficult to do with VBA, IF enough information is
extracted from the outlying workbooks to be able to tell later on whether the
information has been imported before or not. I do this type of thing right
now for a customer that produces a variety of food products in keeping up
with ingredient requirements that may change for existing products and for
new products added to the line. You've already made one part of it fairly
easy: having those outlying files in a single folder.

 

[Ron de Bruin]

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

Or use the code examples from my site
See bottom of the page