embed cell references in macros

[Guest]

I would like to create a macro that opens up a list of files in sequence and
copies data across for manipulation (could be as many as 100 spreadshhets to
open/copy/close. I would like the 'master' spreadsheet to contain a list of
filenames (plus path extensions) that the macro reads down opening in turn.
The user (third party) can then update the list of files as and when new
files are created. This requires being able to 'embed' cell related info into
the macro? This is a task you could do in Lotus but have not yet solved in
Excel.

Any help greatly appreciated

 

[John]

Create a named range in excel. Input your file names/paths in there,
then have your code go through the named range. I would say that's the
easiest way.

If your question is how to read the value of a cell from excel into
vba, there are tons of ways. Here are two of the simpler:

1)x= thisworkbook.worksheets(1).cells(1,1).value
2) x = activesheet.range("A1").value

 

[Guest]

Many thanks for this, I haven't tried it yet,but I do think it will work. The
second appears to be the more practicable in that the first would require the
third party user to potentially re-name the range periodically or
alternatively create a range in excess of the number of file paths required.

Thankyou