Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using another .xslx file as an external source (and how to update)
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using another .xslx file as an external source (and how to update)
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 he 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.) AprilSULeo wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External excel file for pulldown list source | Excel Worksheet Functions | |||
External workbook links don't update when source file changes | Excel Worksheet Functions | |||
Pivot Table Data Source external excel file | Excel Discussion (Misc queries) | |||
Update External Data source from Excel sheet | Excel Discussion (Misc queries) | |||
Automatic update of links in destination file when source file mo. | Excel Discussion (Misc queries) |