Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connect Files
I put this post under Gerneral Questions. I didn't get any good response so
I thought I would ask the experts. I have 2 files, File2 gets data from File1. I used a formula in file2 =X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same computer, File2 updates automatically when data in File1 is changed. If I open File2 on another computer I get a window that asks to "Update File". I click OK and the file opens with the correct information, however it doesn't update if data on File1 is changed after File2 is open. Is there a way to have File2 update automatically when File1 is changed? Thanks for your help. Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connect Files
File1 has to be saved so that File2 can get the info.
And you have to tell excel to go and get those values. In xl2003 menus: Edit|Links|select the link|Update values If it's only one cell, you could just force a re-evaluation of that cell by: Select the cell, Hit the F2 key, then hit the enter key. ===== You can test this kind of stuff by opening two instances of excel and opening each file in those separate instances. Doug wrote: I put this post under Gerneral Questions. I didn't get any good response so I thought I would ask the experts. I have 2 files, File2 gets data from File1. I used a formula in file2 =X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same computer, File2 updates automatically when data in File1 is changed. If I open File2 on another computer I get a window that asks to "Update File". I click OK and the file opens with the correct information, however it doesn't update if data on File1 is changed after File2 is open. Is there a way to have File2 update automatically when File1 is changed? Thanks for your help. Doug -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connect Files
I have File1 set up so it saves automatically after each entry. I was hoping
to have File2 update without the user doing anything. In other words, they are just veiwing the screen. Can I write some code to update all the cells on a timer or if necessary with a command button on File2? Another option might be, if possible, when File1 saves could it trigger File2 to update? "Dave Peterson" wrote: File1 has to be saved so that File2 can get the info. And you have to tell excel to go and get those values. In xl2003 menus: Edit|Links|select the link|Update values If it's only one cell, you could just force a re-evaluation of that cell by: Select the cell, Hit the F2 key, then hit the enter key. ===== You can test this kind of stuff by opening two instances of excel and opening each file in those separate instances. Doug wrote: I put this post under Gerneral Questions. I didn't get any good response so I thought I would ask the experts. I have 2 files, File2 gets data from File1. I used a formula in file2 =X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same computer, File2 updates automatically when data in File1 is changed. If I open File2 on another computer I get a window that asks to "Update File". I click OK and the file opens with the correct information, however it doesn't update if data on File1 is changed after File2 is open. Is there a way to have File2 update automatically when File1 is changed? Thanks for your help. Doug -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connect Files
Hi Doug,
You could look to use VBA to do this. Possible using using the worksheet event procedures available to you: Private Sub Worksheet_Change(ByVal Target As Range) 'FORCE A RECALCULATION ON WORKSHEET2 End Sub "Doug" wrote: I have File1 set up so it saves automatically after each entry. I was hoping to have File2 update without the user doing anything. In other words, they are just veiwing the screen. Can I write some code to update all the cells on a timer or if necessary with a command button on File2? Another option might be, if possible, when File1 saves could it trigger File2 to update? "Dave Peterson" wrote: File1 has to be saved so that File2 can get the info. And you have to tell excel to go and get those values. In xl2003 menus: Edit|Links|select the link|Update values If it's only one cell, you could just force a re-evaluation of that cell by: Select the cell, Hit the F2 key, then hit the enter key. ===== You can test this kind of stuff by opening two instances of excel and opening each file in those separate instances. Doug wrote: I put this post under Gerneral Questions. I didn't get any good response so I thought I would ask the experts. I have 2 files, File2 gets data from File1. I used a formula in file2 =X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same computer, File2 updates automatically when data in File1 is changed. If I open File2 on another computer I get a window that asks to "Update File". I click OK and the file opens with the correct information, however it doesn't update if data on File1 is changed after File2 is open. Is there a way to have File2 update automatically when File1 is changed? Thanks for your help. Doug -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connect Files
Sorry, I'm a rank amature when it ccome to VB code. The second sheet is
actuall the first sheet in a separate file, it's not just another worksheet. What code would I use to activate a recalculation in the second file? Thanks again Doug "Dan" wrote: Hi Doug, You could look to use VBA to do this. Possible using using the worksheet event procedures available to you: Private Sub Worksheet_Change(ByVal Target As Range) 'FORCE A RECALCULATION ON WORKSHEET2 End Sub "Doug" wrote: I have File1 set up so it saves automatically after each entry. I was hoping to have File2 update without the user doing anything. In other words, they are just veiwing the screen. Can I write some code to update all the cells on a timer or if necessary with a command button on File2? Another option might be, if possible, when File1 saves could it trigger File2 to update? "Dave Peterson" wrote: File1 has to be saved so that File2 can get the info. And you have to tell excel to go and get those values. In xl2003 menus: Edit|Links|select the link|Update values If it's only one cell, you could just force a re-evaluation of that cell by: Select the cell, Hit the F2 key, then hit the enter key. ===== You can test this kind of stuff by opening two instances of excel and opening each file in those separate instances. Doug wrote: I put this post under Gerneral Questions. I didn't get any good response so I thought I would ask the experts. I have 2 files, File2 gets data from File1. I used a formula in file2 =X:\Folder\[File1.xls]Totals'!$D$2. If both files are open on the same computer, File2 updates automatically when data in File1 is changed. If I open File2 on another computer I get a window that asks to "Update File". I click OK and the file opens with the correct information, however it doesn't update if data on File1 is changed after File2 is open. Is there a way to have File2 update automatically when File1 is changed? Thanks for your help. Doug -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
connect to sql | Excel Programming | |||
From excel connect to SQl | Excel Programming | |||
connect to MS SQL | Excel Programming | |||
SQL Connect | Excel Programming | |||
VBA ODBC connect to DB2 | Excel Programming |