Links only updated when [check status] is clicked

MINIMOKE

New Member
Joined
Aug 28, 2008
Messages
17
Hi,

I have one destination workbook open on PC#1 (Windows 10)

The workbook has links to two source workbooks open on two networked (wifi) laptops (Windows 10) PC#2 and PC#3.

I'm using the current version of Excel 365 (on all three computers).

For the cells of the destination workbook to be updated, I have to do the following:
1. Save the source workbook (on PC#2/PC#3)
2. Visit the [Edit Links] window of the destination workbook (PC#1). Links are initially shown as "unknown". To have the destination cells get the updated values I need to click [check status]

I'm not that worried by step 1. (it's actually a bit of an advantage, but I'm curious why the file has to be saved; I was expecting the links to be updated whenever a cell value changes.

Having to manually click [check status] is the real problem I'm having. I have tried to make the links operate as automatic; all the obvious settings appear to be correct.

The workbooks are located in the public folders of all 3 machines.

All three machines are in the same workgroup.

If I copy all three workgroups to the same machine, the links work automatically as expected.

Any ideas on where to start troubleshooting would be most appreciated.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
This behavior is inherent to the situation, so I'm afraid you have to live with it.

Windows10 WorkGroups belong to the most basic of network infrastructures. They're not managed by a domain controller server. Rather, no computers in the workgroup have control over the others!! They may share resources like disk folders and printers. That's it.
This explicitly means that Excel #1 cannot have any awareness of the existence of a running Excel 2# (or #3) on another computer within the same WorkGroup. This implies there's also no way for Excel #1 to find out whether Excel #2 has opened a workbook or not.

Links to workbooks are initialy links to files on disk. When changes take place in the computers memory, user has to "save" in order to update the contents of the file on disk.

If I copy all three workgroups to the same machine, the links work automatically as expected.

If there are multiple workbooks (I'm pretty sure you meant those instead of workgroups ...) in the same memory of the same computer within control of the same instance of Excel, well .... then a live update is possible since Excel can keep track of all changes of all open workbooks.
 

MINIMOKE

New Member
Joined
Aug 28, 2008
Messages
17
Hi GWteB,

Your great explanation explains the 1st part of my issue; thank you for that.

My main issue is that I have to manually click [Check Status] in the destination workbook's [Link Edit] page to update the links to the (saved) source workbooks. When I 1st open this edit page, the links are always shown as unknown; they change to OK and the cells in the (destination) workbook are updated when i click [Check Status].

Do you have any idea why this would be the case?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
You are welcome! Despite my explanation you have a (rather plausible) question. Let's take a closer look.

Assume a scenario that the 2nd computer within the Workgroup has been turned off or is simply out of range of the wireless network. That means the remote workbook is not available. Excel takes this possibility into account and leaves every decision to the user. Excel could have done a continuously check at a certain interval whether the remote workbook is available or not, and if so, suck the required data from the remote workbook. Excel's designers chose not to. In short, there is no default plug-and-play in this regard.

However, if we use VBA we come very close. Save your destination workbook as a Macro Enabled Workbook (*.xlsm) and open the VBE (ALT F11). The left hand pane belongs to the Project Explorer. If there isn't any press CTRL R. Now click on Menu > Insert > Module. Paste the appropriate code block into the right hand pane. Now double click in the Project Explorer's pane on ThisWorkbook to open its code-behind module. Paste the other code block into that module. Close the VBE and save your workbook again.

Both on opening and on switching worksheets within your destination workbook, the values coming from the remote workbooks will be updated if possible. If a continuously check is required, we have to do some more coding, so let me know.

This goes in the module of ThisWorkbook:
VBA Code:
Private Sub Workbook_Open()
    CheckLinksAndUpdateAllValues
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    CheckLinksAndUpdateAllValues
End Sub


This goes in a standard module:
VBA Code:
Public Sub CheckLinksAndUpdateAllValues()

    Dim FSO         As Object
    Dim FullName    As Variant

    Set FSO = VBA.CreateObject("Scripting.FileSystemObject")

    With ThisWorkbook
         For Each FullName In .LinkSources
            If FSO.FileExists(FullName) Then
                .UpdateLink Name:=FullName, Type:=xlExcelLinks
            End If
         Next FullName
    End With
End Sub
 
Solution

MINIMOKE

New Member
Joined
Aug 28, 2008
Messages
17
Thanks again GWteB,

That was very well explained and it makes sense.

Maybe you're curious what I'm using the 3 workbooks for? It's part of a results system for a motorsport event called motorkhana. There are two "test areas" where cars' elapsed times are saved to workbooks on PC#1 and PC#2. On the destination laptop (PC#1) there is a workbook that formulates the results from the elapsed times and displays them on a worksheet.

Rather than have someone at PC#1 manually updating the results worksheet values or swapping between worksheets, I'd prefer the results to be updated automatically.

All 3 workbooks are already macro enabled.

The "continuous check" you mention; would this be a periodic (time interval, e.g. every 5 minutes) check, or is there anyway the destination workbook can detect whether the source workbooks have been saved?

Perhaps another solution would be to use another networking method rather than a workgroup? If I was to set up a domain, would that help?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
Perhaps another solution would be to use another networking method rather than a workgroup? If I was to set up a domain, would that help?
No, that wouldn't help, because of [see explanation in post #2]

is there anyway the destination workbook can detect whether the source workbooks have been saved?
Only by writing a VBA for that ourselves, which should then also be executed at a certain interval. We might as well apply an interval to update our destination workbook right away.
On opening the updater runs every 5 minutes. This interval can easily be changed if wanted, within the ScheduleUpdater procedure.


This goes in the module of ThisWorkbook:
VBA Code:
Option Explicit

Private Sub Workbook_Open()
    ScheduleUpdater
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CancelUpdater
End Sub


This goes in a standard module:
VBA Code:
Option Explicit

Public ScheduledTime As Date

Public Sub CheckLinksAndUpdateAllValues()

    Dim FSO         As Object
    Dim FullName    As Variant

    Set FSO = VBA.CreateObject("Scripting.FileSystemObject")

    With ThisWorkbook
         For Each FullName In .LinkSources
            If FSO.FileExists(FullName) Then
                .UpdateLink Name:=FullName, Type:=xlExcelLinks
            End If
         Next FullName
    End With
    ScheduleUpdater
End Sub

Public Sub ScheduleUpdater()
    ScheduledTime = Now + TimeValue("00:05:00")
    Application.OnTime EarliestTime:=ScheduledTime, Procedure:="CheckLinksAndUpdateAllValues"
End Sub

Public Sub CancelUpdater()
    Application.OnTime EarliestTime:=ScheduledTime, Procedure:="CheckLinksAndUpdateAllValues", Schedule:=False
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
Please note that my eye just caught something within my code. IMO it's not necessary to let the interval time expire after opening the destination workbook before updating for the first time. If you would like to replace the invocation of ScheduleUpdater with CheckLinksAndUpdateAllValues in the Workbook_Open event handler, then this minor imperfection has also been corrected.
 

Forum statistics

Threads
1,181,774
Messages
5,931,963
Members
436,811
Latest member
OscarBoots_23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top