• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Add extra step to current macro to auto update

Hi Experts

I have the following vba which works perfectly fine...however, when the macro is running it has a intermediate step where the end user has to click the continue button as the following message appears...

This workbook contusions one or more links that cannot be updated

Click continue or edit workbook

I would like to select continue each time and incorporate a step into the current macro to do this automatically if possible

Option Explicit

Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngDst As Range
Dim strFilename As String
Dim strPath As String

    strPath = "C:\Documents and Settings\Test\"
    Set wbDst = ThisWorkbook  ' Workbooks.Open("C:\Documents and Settings\Test\Master Template.xls")

    Set wsDst = wbDst.Worksheets("cm1")

    Set rngDst = wsDst.Range("M18")

    strFilename = Dir(strPath & "*.xls")

    While strFilename <> ""

        If strFilename <> wbDst.Name Then

            Set wbSrc = Workbooks.Open(strPath & strFilename)

            Set wsSrc = wbSrc.Worksheets("cm")

            With wsSrc
                .Range("M18", .Range("M" & .Rows.Count).End(xlUp).Offset(0, 31)).Copy rngDst
            End With

            Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offset(1)

        End If

        strFilename = Dir()


End Sub
  • 2
  • 2
2 Solutions
beginning of the macro, add this code

application.displayalerts = false

Open in new window

NorieVBA ExpertCommented:
You can change this,
Set wbSrc = Workbooks.Open(strPath & strFilename)

Open in new window

to this,
Set wbSrc = Workbooks.Open(strPath & strFilename, UpdateLinks:=3)

Open in new window

and the workbooks should open without a prompt and the links will be updated.
Explanation of my answer:

It says you cannot update links, so regardless of that message and whether you click continue/not, you will not be updating links. This is probably because incorrect references/your source file is corrupted.

Therefore, setting the displayalerts to false will suppress this message.
NorieVBA ExpertCommented:

Shanan212 has a good point.

I assumed you were just getting the update links message, not the can't update links message.
route217Author Commented:
All experts

Firstly thanks for the feedback - the question was to a refer if it appears... Poorly worded in my part - apologies..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now