none
Automatic Updating of Links between Excel Workbooks on a Network RRS feed

  • Question

  • Hi all,

    Hoping you could help me with this one.

    I want to have a number of Excel Workbooks open all day long on different computers with links to update to one ‘Reporting Excel Workbook’ that no one is
    working on but is open on a screen to identify the work being carried out throughout the day. 

    I have shared the workbook and checked the box for automatic updates and selected 5 minutes, but it will not update across the network onto another
    computer.

    If the Excel Workbooks are open on the same computer then the worksheet updates, but because it is on another computer on the network then it doesn’t
    update until it is closed and reopened which cannot happen every 5 minutes all day long.

    Thanks!

    Monday, February 27, 2012 8:01 AM

Answers

  • Hi Rod,

    Unfortunately we have tried this previously and it doesn't work. 

    Here's the solution I have found:

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 300 ' five minutes
    Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
    Sub StartTimer()
        RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
            Schedule:=True
    End Sub
     
    Sub TheSub()
     
      ActiveWorkbook.UpdateLink Name:="\\rexzhang-pc\d\test_dde_.xlsx", Type:= _
            xlExcelLinks
        StartTimer  ' Reschedule the procedure
    End Sub

    Thank you.
    Verraine

    • Marked as answer by Verraine Tuesday, February 28, 2012 6:45 AM
    Tuesday, February 28, 2012 6:44 AM

All replies

  •  Excel Workbook then when other users open it, from memory, updates only happen when users save their worbook. Obviously is info is changed in the main workbook, it needs saving as well. So, the easiest solution is to make this a training issue. Aquick Ctlr+S will show updates.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Monday, February 27, 2012 11:30 PM
  • Hi Rod,

    Unfortunately we have tried this previously and it doesn't work. 

    Here's the solution I have found:

    Public RunWhen As Double
    Public Const cRunIntervalSeconds = 300 ' five minutes
    Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
    Sub StartTimer()
        RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
            Schedule:=True
    End Sub
     
    Sub TheSub()
     
      ActiveWorkbook.UpdateLink Name:="\\rexzhang-pc\d\test_dde_.xlsx", Type:= _
            xlExcelLinks
        StartTimer  ' Reschedule the procedure
    End Sub

    Thank you.
    Verraine

    • Marked as answer by Verraine Tuesday, February 28, 2012 6:45 AM
    Tuesday, February 28, 2012 6:44 AM