Using another .xslx file as an external source (and how to update)

[AprilSULeo]

I have two workbooks that I want open, each in a separate instance of Excel
2007. One workbooks is the "Master.xslx". The other is the
"Destination.xslx". I'm using formulas to gather data from the Master and
pulling into the Destination.

This is simplified, but my formulas in Destination look something like this:
=SUM('C:\Documents and Settings\user\My Documents\[Master.xlsx]Sheet1'!B1:B2)

While I'm editing Master, I want Destination to update. The only way I've
found to do this is using the Edit Links dialog box in Destination and
manually updating the links. I can't seem to make Destination update
automatically. I want Destination to automatically update every minute if
possible.

Does anyone know how to do this? I've tried setting up .odc to no avail. I
would love for clicking the Refresh button to work, but it doesn't seem that
I've found a way for that to happen yet.

 

[Dave Peterson]

If you used a single instance of excel, then your changes in master.xls would be
reflected immediately in the destination.xls workbook.

With two instances, you'll have to save the changes made to master.xls every
minute (destination.xls picks up the values from the file on disk--not what's
open in a separate instance). (Maybe with an OnTime macro???)

And then you could use another Ontime macro to refresh those links in
destination.xls.

(You may want to reconsider the decision to use separate instances.)

Chip Pearson explains application.ontime at:
http://www.cpearson.com/excel/OnTime.aspx

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

I have two workbooks that I want open, each in a separate instance of Excel
2007. One workbooks is the "Master.xslx". The other is the
"Destination.xslx". I'm using formulas to gather data from the Master and
pulling into the Destination.

This is simplified, but my formulas in Destination look something like this:
=SUM('C:\Documents and Settings\user\My Documents\[Master.xlsx]Sheet1'!B1:B2)

While I'm editing Master, I want Destination to update. The only way I've
found to do this is using the Edit Links dialog box in Destination and
manually updating the links. I can't seem to make Destination update
automatically. I want Destination to automatically update every minute if
possible.

Does anyone know how to do this? I've tried setting up .odc to no avail. I
would love for clicking the Refresh button to work, but it doesn't seem that
I've found a way for that to happen yet.