Share via

Excel VBA Auto update links

Anonymous
2019-07-12T03:27:54+00:00

I have an excel protected spreadsheet at work that automatically updates links from other spreadsheets when a certain macro is called.

I use the below code to do this.

The problem is that sometimes the files are unavailable due to network issues, and when this happens excel asks me to clarify the location by bringing up a browse box.

What I'd like to happen instead is if the data isn't available to skip and just update the next time the macro is run.

Similar to the code "On Error Resume Next", if that's possible. Basically a "If excel doesn't find the link resume next"

Can anyone help me?

Excel version 1803 (Build 9126.2259)

Sub Refresh_Data()

    ActiveSheet.Unprotect

    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,001 Reputation points Volunteer Moderator
2019-07-12T04:56:14+00:00

Try the following

Sub Refresh_Data()

    Dim arrLinks As Variant   'For the Links Array

    Dim i As Long

    'Assign the links to an array

    arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

    If Not IsEmpty(arrLinks) Then   'Test that the array contains at least one link

        For i = LBound(arrLinks) To UBound(arrLinks)

            Application.DisplayAlerts = False

            On Error Resume Next

            ActiveWorkbook.UpdateLink Name:=arrLinks(i)

            On Error GoTo 0

            Application.DisplayAlerts = True

        Next i

    Else

        MsgBox "No links found"

    End If

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-07-25T23:29:54+00:00

    Thanks for your help.

    I gave that a go, but unfortunately it didn't work.

    I'll create a new post.

    Was this answer helpful?

    0 comments
  2. OssieMac 48,001 Reputation points Volunteer Moderator
    2019-07-19T03:43:38+00:00

    I haven't got the facilities to test under shard system but have you tried inserting On Error commands before and after the save command as per following.

        On Error Resume Next    'On Error ignore the error and go to the next command

        ThisWorkbook.Save

        On Error GoTo 0      'Cancels the Resume Next.

    BTW you should be able to use minutes for the interval.  Time Serial function is TimeSerial(Hours, Minutes, Seconds). Change both the Constant and the "RunWhen …. command lines with the Time in the Minutes Argument (or Parameter) position.

    Public Const cRunIntervalMinutes = 5      ' five minute counter. Note that I changed variable Seconds to Minutes for clarity

    RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)     'There are 2 lines with this command

    If the above does not resolve your problem then I suggest that you create a new post with the question and someone else might be able to help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2019-07-19T03:10:52+00:00

    It worked perfectly!! Thanks.

    I had to take the msgbox out as I didn't want it telling me anything, just not doing anything if it failed to update.

    Although it's lead me to another question.

    A different excel file I have lifts data from some third party software and then saves periodically. I've just got that saving periodically on a timer.

    Since it's saving to a network file, sometimes it thinks someone else has it open (even when they don't) and it'll throw a fit and stop doing anything.

    Again, what I want to happen, is if the file save fails for any reason, for it to just skip and go to the next line.

    Here's the code I've got - Start_Counter is run when I open the file, Stop_counter is run on file close.

    Public RunWhen As Double

    Public Const cRunIntervalSeconds = 300 ' five minute counter

    Public Const cRunWhat = "Save_File"  ' Save file macro

    Sub Start_Counter()

        'Start counter as soon as file is opened, but only if opened read/write.

        'Every time the timer finishes, the file will save.

        If ActiveWorkbook.ReadOnly Then

        Else

            RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)

            Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

                Schedule:=True

        End If

    'So Excel won't ask to save when I close the file

        ThisWorkbook.Saved = True

    End Sub

    Sub Save_File()

    '

    ' Save File, then restart timer.

    '

        ThisWorkbook.Save

        RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)

        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

            Schedule:=True

    '

    End Sub

    Sub Stop_Counter()

    'Cancel timer on file close.

    On Error Resume Next

        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

            Schedule:=False

    End Sub

    Was this answer helpful?

    0 comments
  4. Anonymous
    2019-07-17T03:09:39+00:00

    I'll give that a go and get back to you later.

    Thanks.

    Was this answer helpful?

    0 comments