close pop up when trying to open a multiple worksheet from a macro

Question

I am trying to extract a some data from multiple worksheets but when I run the macro some worksheet are coming up with a Pop-up window asking me to update or don't update the link attached to the worksheet. I am looking for a way to make the macro hit "Don't update"

P.S. if possible I would like sFolder to be the root folder and also search for any "*.xlsx" contained inside C:\temp

Sub tgr()

    Dim wbDest As Workbook
    Dim wsDest As Worksheet
    Dim rCopy As Range
    Dim sFolder As String
    Dim sFile As String
    Dim lRow As Long

    Set wbDest = ThisWorkbook                   'The workbook where information will be copied into
    Set wsDest = wbDest.Worksheets("Sheet1")    'The worksheet where information will be copied into
    sFolder = "C:\Path\" 'The folder path containing the xlsx files to copy from

    'would like sFolder to be the root folder and also 
    '   search for any "*.xlsx" contained inside C:\temp

    lRow = 1 'The starting row where information will be copied into

    'Adjust the folder path to ensure it ends with \
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"

    'Get the first .xlsx file in the folder path
    sFile = Dir(sFolder & "*.xlsx")

    'Begin loop through each file in the folder
    Do While Len(sFile) > 0

        'Open the current workbook in the folder
        With Workbooks.Open(sFolder & sFile)
            'Copy over the formulas from A1:C3 from only the first 
            '   worksheet into the destination worksheet
            Set rCopy = .Sheets(1).Range("C9:D26")
            wsDest.Cells(lRow, "A").Resize(rCopy.Rows.Count, rCopy.Columns.Count).Formula = rCopy.Formula

            'Advance the destination row by the number of rows being copied over
            lRow = lRow + rCopy.Rows.Count

            .Close False    'Close the workbook that was opened from the folder without saving changes
        End With
        sFile = Dir 'Advance to the next file
    Loop

End Sub

https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open see the `UpdateLinks` parameter — Tim Williams, Mar 27 '19 at 21:42
I have about 2500 files all with link to another sheet, this is why I am looking for a solution. do you think it is doable? — Spock, Mar 27 '19 at 21:53
I did but I am afraid I don't know how to use it ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks — Spock, Mar 27 '19 at 22:29

score 1 · Answer 1 · answered Mar 27 '19 at 22:56

1

Change

With Workbooks.Open(sFolder & sFile)

To

With Workbooks.Open(sFolder & sFile, UpdateLinks:=False)

More here on this topic: How to suppress Update Links warning?

answered Mar 27 '19 at 22:56

Tim Williams

154,628
8
97
125

Thank you Much Tim. sorry I was asking so much, I just barely getting started on this so not the brightest cookie out there – Spock Mar 27 '19 at 23:06
I have been running the macro and now I am not getting the update links anymore but it does comes up with a Update Values: Source. I have sent the UpdateLinks to =0 =False =Never. any recommendation – Spock Mar 28 '19 at 00:51
Maybe try some of the other suggestions at the question I linked to. – Tim Williams Mar 28 '19 at 03:43

close pop up when trying to open a multiple worksheet from a macro

1 Answers1