Executing a macro in another spreadsheet?

[ph8]

Greetings, I have once again achieved the state of 'being stumped' so
made my way over to this forum for a little assistance. Any help on
the below would be greatly appreciated!

I have a set of spreadsheets organized in a directory hierarchy. Each
spreadsheet already contains the names for the spreadsheets below in
the hierarchy. My problem, is at every 'level' of this spreadsheet, I
need to include a function which can 'push down' information to all
spreadsheets below it. This is what the hierarchy resembles:

Tier 1
--Tier 2
----Tier 3
----Tier 3
----Tier 3
--Tier 2
----Tier 3
----Tier 3
----Tier 3
--Tier 2
----Tier 3
----Tier 3
----Tier 3
etc...

The hierarchy itself is actually 4 tiers deep (soon to be expanded to
6). What I want to happen is if a certain cell is updated at Tier 1,
all of the spreadsheets below it need to be updated with that same
information (tier 2 and 3). Similarly, if a cell is updated at Tier 2,
all of the spreadsheets (at pre determined cells) below need to be
updated with the same information (tier 3).

What makes this tricky, is I don't want to just have the path of all
the below spreadsheets with manuel cell updates and whatnot. I would
like for this to be all automated. I have already found a way to have
the spreadsheets 'find' all the spreadsheets one tier below them, and
that is what I want to use to create this 'push down' effect.

If the spreadsheet from Tier 1 has the key cell updated (it will update
with a macro, so there is no problem with 'starting' the push down
function), the Tier 1 spreadsheet will update the information in the
Tier 2 spreadsheet, then call the 'push down' function from the Tier 2
spreadsheets, which will update the information to the Tier 3
spreadsheets... and so on.

Please provide any assitance you can. I am very much so out of
options.

Also, if there is an easyer yet different way to accomplish the same
effect, PLEASE let me know. I want to learn as much as I can. Thanks
in advanced!

Respectfully,
Eddie

 

[DM Unseen]

No worries
Application.Run is your friend(see XL help).
Be shure that the the macro workbook is open first, though.

Dm Unseen

 

[ph8]

Thank you DM Unseen.

One quick confirmation if thats ok. There is no way to do this without
opening the subsequent worksheet is there?

Or at least, can the data in individual cells be changed without having
to actually open other worksheets?

Thanks again for your help!

 

[Tom Ogilvy]

you can have the lower tier sheets link to existing data in upper tier
sheets using linking formulas. However, almost any other way of updating
the sheets would require opening them and applying the change - and likewise
that would trigger code within those workbooks that would open the next
lower tier and implement the change. (if the information added is new
information, then linking formulas would have to either preexist or be
created so that might not be an appoach). Also, if a change is made at tier
1 and a workbook is opened at tier3 before the tier2 workbook is opened, the
tier3 workbook would not have the new information.

I don't know what you are doing, but this seems very prone to get out of
sync. A common database accessed by all the sheets might be a sounder
solution - but I don't know what added value is provided by rippling through
the sheets.

 

[DM Unseen]

No, changing closed excel files I would not recommend, although it is
possible using ADO!

You need to code ADO with VBA to actually change a value in a closed
workbook.
But this means you will be accesing your workbook as a database and as
Tom states, you might have less worries if you did everything in a
real database.

DM Unseen