Links only updated when [check status] is clicked

[MINIMOKE]

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.

 

[GWteB]

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]

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]

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:

Private Sub Workbook_Open()
    CheckLinksAndUpdateAllValues
End Sub

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

This goes in a standard module:

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

 

[MINIMOKE]

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]

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:

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:

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

 

[MINIMOKE]

Many thanks GWteB,

I'll try those VBA solutions

 

[GWteB]

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.