I don’t have much hope for a solution to this peculiar situation, but here goes anyway. This involves two separate Excel files, both of which are located on a network server, but are open at the same time on different computers.
One of them is kept open on a public computer that’s situated in a meeting room. Has to be that computer and no other, because my file is linked to a constantly updating external data source that can only be accessed that particular computer. Let’s call that “Computer A”.
In order to work on newest data on that file, I have another file opened on my own computer (call it “Computer B”) that has links to cells in the first file.
The setup works fine, with one caveat: In order to capture the most recent data, I have to walk over to Computer A and press “save” on the Excel file, then walk back to Computer B, close the Excel file open there and then reopen it (easiest way to refresh the links as there are several hundred cells to update).
The problem is that I can’t always go over to Computer A because it’s located in a meeting room that’s frequently occupied by other people.
So is there any way I could sort of remotely command Computer A’s Excel file to update itself? Or is there another way I haven’t thought of?
Much appreciated!
Will it work to set computer A to automatically save the file every x minutes (see AutoRecover settings)? Then you wouldn’t have to go to computer A to do a manual save.
And you could also set computer B to automatically refresh, too – then you wouldn’t have to close & reopen on it. (External Data ==> Data Range Properties ==> Refresh Every x minutes.)
That’s probably going to be your easiest solution. Another way would involve using a remote desktop application so that you can control the computer remotely. That raises security issues, though and your IT department probably won’t like it.
Thanks for the suggestions – I was indeed looking into setting up some kind of automatic save, but when I looked into it before, board discussions seemed to indicate that Excel 2007 doesn’t offer that option any more?
I haven’t used NetDDE in years since it was phased out, but there are applications that still support it on Windows. In fact, looking in my services list, I still have Network DDE Services available. This is an old article, but should still be valid if you can get Network DDE running…
http://support.microsoft.com/kb/128491
Then again, you know, installing something like http://www.uvnc.com/ would just let you access the remote computer directly, which is probably easier. I use VNC on all of my plant floor computers so I can deal with them from my desk.
Excel 2007 offers AutoSave but the AutoSave feature in Excel doesn’t actually save the file, it saves recovery information to be able to recover the file in case of a crash.
If you don’t want to mess with remote-control solutions, this can be done using VBA to automatically save the file periodically. The code is ever so slightly tricky, but I can look it up and provide it if you’re interested. You would have to enable macros to run when opening the file.
Here is a VBA solution, easier than I remembered. Put this code in the ThisWorkbook module.
This code schedules an event when the file is opened. The event saves the file and then reschedules itself. You can make the time interval whatever you want; in this example it is 5 minutes.
Option Explicit
Dim TimeInterval As Variant
Public Sub Workbook_Open()
TimeInterval = TimeValue("00:05:00")
Application.OnTime earliesttime:=Now + TimeInterval, procedure:="SaveMe"
End Sub
Public Sub SaveMe()
ThisWorkbook.Save
Application.OnTime earliesttime:=Now + TimeInterval, procedure:="SaveMe"
End Sub
One more note on this: You have to do things a little differently if you find that the code has a to cancel a scheduled event, but that doesn’t seem to apply in this situation.
Here is a link with a good illustration of the method:
There is one error. The Sub SaveMe needs to be moved from the ThisWorkbook module to a new plain old Module.