Share this content
0
4142

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
    Loop
   
    If Cnt > 0 Then
        MsgBox "Completed...", vbExclamation
    Else
        MsgBox "No files were found!", vbExclamation
    End If
   
    Application.ScreenUpdating = True
   
End Sub

Replies (3)

Please login or register to join the discussion.

avatar
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:
avatar
By mung1
07th Aug 2014 09:58

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

Thanks (0)
avatar
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