Share this content

vba script to update and continue links

vba script to update and continue links

Didn't find your answer?

Search AccountingWEB

I have a macro to update a large group of workbooks (book01, book02, .....book65) without having the need to open and close all 65 of them.

However, with each loop of each workbook Excel prompts me to click "update" links and then click "continue". Is there a script that will do this for me that I can insert into the below?

BTW: I borrowed this script from an earlier respondent who kindly put me on my way. I have since edited it using my very basic knowledge of vba.

Many thanks for reading.


Sub test()

    Dim MyPath          As String
    Dim MyFile          As String
    Dim Wkb             As Workbook
    Dim Cnt             As Long
    Application.ScreenUpdating = False
    MyPath = "C:\folder1\folder2\" 'change the path accordingly
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "book*.xlsm")
    Cnt = 0
    Do While Len(MyFile) > 0
        Cnt = Cnt + 1
        Set Wkb = Workbooks.Open(MyPath & MyFile)
        Wkb.Close savechanges:=True
        MyFile = Dir
    If Cnt > 0 Then
        MsgBox "Completed...", vbExclamation
        MsgBox "No files were found!", vbExclamation
    End If
    Application.ScreenUpdating = True
End Sub

Replies (3)

Please login or register to join the discussion.

By Russell Huk
06th Aug 2014 00:28

I am happy to sort this out for you but of course I would have to charge. Please advise.

Thanks (0)
Replying to Danny_C:
By mung1
07th Aug 2014 09:58

Thanks for the offer Russell but I have now managed to work it out.

Thanks (0)
By jndavs
06th Aug 2014 10:48

Some things you can try

There are some settings within the trust centre you can change to suppress these messages / make the updates happen silently but they may have security implications.

Otherwise you could try one of the following:

Application.DisplayAlerts = False
  <Your Do Loop>
Application.DisplayAlerts = True

Application.AskToUpdateLinks = False
  <Your Do Loop>
Application.AskToUpdateLinks = True

Thanks (1)
Share this content