VBA - bypass the link update dialog box for the source file

Hi,

I am copy some data from another workbook which apparently have links to another source. When I try to open this file (as usual) excel asks me to confirm wether or not I want to update the link.
Although user can simply say yes or no but i was wondering if its possible to by pass this one by saying 'dont update' -> for the links
Such that its not displayed to the user.
Current status is,
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

I though it might help but it doesnt seem to work over the update link pop-up
It would be great if some one could post couple of lines required to achieve it.

Many thanks

SameerMirza

Asked: 2011-09-28

Advertise Here

Microsoft Excel

2 Solutions

LVL 85

Rory ArchibaldCommented: 2011-09-28

In the workbooks.Open method, specify updatelinks:=False

LVL 17

andrewssd3Commented: 2011-09-28

The second parameter of the Workbooks.Open method tells it whether to update links or not (True/False) - I think setting this to False should get rid of your message

LVL 17

andrewssd3Commented: 2011-09-28

Like he just said...

SameerMirzaAuthor Commented: 2011-09-28

Thanks.
Can any one please tell me what the below code is doing and whats the first argument of GetOpenFileName()

where its defined as
Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long

Sub test(sWks As String, sLoc As String)
    
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim FileDir As String
    Dim FilePos As Long
    Dim PrevFilePos As Long
    Dim oWks As Worksheet

    On Error GoTo Data_Err

    Set oWks = Worksheets(sWks)

    With OpenFile
        .lStructSize = Len(OpenFile)
        .hwndOwner = 0
        .hInstance = 0
        .lpstrFilter = "Excel Files" + Chr(0) + "*.xls" + _
            Chr(0) + "All Files (*.*)" + Chr(0) + "*.*" + Chr(0) + Chr(0)
        .nFilterIndex = 1
        .lpstrFile = String(4096, 0)
        .nMaxFile = Len(.lpstrFile) - 1
        .lpstrFileTitle = .lpstrFile
        .nMaxFileTitle = .nMaxFile
        .lpstrTitle = "Oil Index D-1 File"
        .flags = OFN_HIDEREADONLY + _
            OFN_PATHMUSTEXIST + _
            OFN_FILEMUSTEXIST + _
            OFN_ALLOWMULTISELECT + _
            OFN_EXPLORER
        lReturn = GetOpenFileName(OpenFile)

Open in new window

LVL 85

Rory ArchibaldCommented: 2011-09-28

That's a completely different question!

It's an API function for getting a filename to open. The argument is a specific Type expected by the function - you should have it declared at the top of the module.