Generating worksheets from master worksheet

[Lomeos]

Hello,

I have this thing I am struggling with.

Situation is as follows:
- I have a folder full of xls files (source files). These files have the exact same structure, but different numbers.
- I have one master template with mark up and graphs, etc. All the data in this file must come from a source file.

I need to fill the master template with data from the source files and save a marked up document for each source file. The amount of source files vary and so does the names of the source files.

What must happen is this:
- automatically browse the folder and read all source file names that are there (one by one)
- update the links in the master template to mach the current filename
- For each source file, let the master template read the values into the master.
- Save the document under a new name (based on the data in the source file)

Any idea on how to do this? Can it be done?

on of my issues is this: can I put a filename in a cell and let all the other cells reference it?

for example: ='[Sourcefile1.xls]data'!$B$1
can I do something like: put the filename in a cell, ie. A1=Sourcefile1.xls and then read: ='[A1]data'!$B$1
Note: this doesn't wokr, but is just to get the idea.

Cheers,

Lomeos

 

[Lomeos]

Oke, I managed to solve part of the issue using VBA objects and the GetDetailsOf() method. Biggest issue is still the 'dynamic' links to other sheets.

on of my issues is this: can I put a filename in a cell and let all the other cells reference it?

for example: ='[Sourcefile1.xls]data'!$B$1
can I do something like: put the filename in a cell, ie. A1=Sourcefile1.xls and then read: ='[A1]data'!$B$1
Note: this doesn't wokr, but is just to get the idea.

Anyone can help me with that? Is there some sort of Evaluate function? Is it even possible?

Cheers,

Lomeos