integrate output from various workbooks in one workbook

[john.novotny]

I have a few different models in seperate excel workbooks ... one for
cars, one for houses, one for retirement accounts.. I recently had the

idea to take outputs from each of these models and plug them into a
single workbook something like life.xls. What I'm wondering is if there

is a way to wrap them (the house and car models) with an interface
(VBA?) which I can pass parameters and can call from a macro in
life.xls to retrieve the output from. Essentially I'd like each of the

workbooks (house,..cars) to be like a callable function into which I
can pass parameters, and which returns a value (in this case probably
an array of values). Is this possible? Am I trying to push excel too
far? Would it be simpler to just bite the bullet and abandon excel and

try and code this up in C++?

Maybe one step which might simplify the problem is if I make the
seperate models just sets of worksheets in the life.xls workbook. I'm
resisting this just because it's nice to have the models seperate for
organizational purposes.


Also if this seems doable are there any good examples out there for
reference?

 

[NickHK]

John,
To me, you could create your own object model of "Life", with various
classes (Car, House, RetireAccount, etc) with a collection of each if
required.
Each class controls its own data presentation, calculation etc.
Depending how you want to construct it, you can enforce "Life" as the
starting point or allow individual instances of lesser objects.

NickHK

 

[paul.robinson]

Hi
This is perfectly doable.
Start by trying to get the info out of one workbook into life.xls. Once
you can do that, the others will be similar.

From life.xls you will probably need to

1. open the target workbook
2. Capture the data you need
3. Close the target workbook
4. Process and output the data within life.xls

1 and 3 are standard bits of code you can find in this newsgroup by
googling. You should be able to find code which allows you to browse to
the target file and save it after closing with a new name/location if
required. Part 2 might be as simple as copying a sheet or sheets to
life.xls, which you can delete after step 4.
Step 4 is the hard work and is particular to your problem, so examples
may not be much help.
See if the steps 1 to 4 are a strategy that suits your problem. If they
are, then try and ask questions in this group which are contained
within each step. The more specific and well defined the question, the
more help you will get.

regards
Paul