Disable Update Links Message

[Tom Schreiner]

Hi all...
I did try to search and it appears that it is not working...

Using a macro, I am attempting to combine many emailed workbooks onto one Master workbook.

When the macro opens each individual file, the "Update Links" message appears and pauses the code...

Application.DisplayAlerts = False
is not helping me in this matter.

Any other ways to ignore the links message?

Thanks,
Tom

Private Sub cmdUpdate_Click()

Dim FS, i

Dim PlaceRow As Long

Dim OpenedName As String

Dim DoNotReopenActiveWB_Name As String

Dim MasterWorkBook As String



Application.DisplayAlerts = False

Application.EnableEvents = False

Application.ScreenUpdating = False

DoNotReopenActiveWB_Name = ActiveWorkbook.FullName

MasterWorkBook = ActiveWorkbook.Name

PlaceRow = Sheets("MasterSheet").Range("c1:c" & Range("c65536").End(xlUp).Row).Rows.Count

If PlaceRow < 3 Then PlaceRow = 3

Set FS = Application.FileSearch

With FS

    .LookIn = ActiveWorkbook.Path

    .Filename = "*.xls"

    If .Execute Then

        For i = 1 To .FoundFiles.Count

            If .FoundFiles(i) <> DoNotReopenActiveWB_Name Then

                PlaceRow = PlaceRow + 1

                Workbooks.Open .FoundFiles(i)

                OpenedName = ActiveWorkbook.Name

                Workbooks(MasterWorkBook).Sheets("MasterSheet") _

                    .Range("B" & PlaceRow & ":AH" & PlaceRow).Value = _

                Workbooks(OpenedName).Sheets("Data") _

                    .Range("A3:AG3").Value

                Workbooks(OpenedName).Close savechanges:=False

            End If

        Next i

    End If

End With

Application.DisplayAlerts = True

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

[ This Message was edited by: TsTom on 2002-05-22 02:30 ]

[Mudface]

Tom- from the Help on the Open method: -

Opens a workbook.

Syntax

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)

expression Required. An expression that returns a Workbooks or RecentFile object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references
If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.

HTH.

[Tom Schreiner]

Thanks Mudface!
It's amazing what a comma and a zero can do!
I appreciate the quick reply and the exact answer I needed. Tested perfect!
Tom

[BenNV]

Tom, I use the following:

Private Sub Workbook_Open()

Application.AskToUpdateLinks = False

End Sub

This sometimes works but sometimes I still do get the popup message - I'm not sure why.

[Mudface]

I think you could also set the Application.AskToUpdateLinks property to False as well. Although this just updates links without asking so you don't have as much control as with the Open method, and would have to set it back to True at some point as well.

Ben, I think the Update Links dialog box gets fired before the Open event (bizarrely). Also, you would need to have that code in every file that were going to open.

[ This Message was edited by: Mudface on 2002-05-22 02:52 ]