Problem with Updating links (XLUpdateLinksNever)

[Hitesh_Banda]

Hi,
Hope someone can help me on this.
I have a macro which consolidates data from several files in a selected folder. But some of them contain external links. Macro is working fine. All I want is that it should not update the external links while opening the files. I tried the following, all in vain.

UpdateLinks:=xlUpdateLinksNever
Application.AskToUpdateLinks = False

The macro still asks me whether or not to update the links, and somethimes it also fails because of this.
Please help me on this.
ray:

Thanks in advance.
Hitesh

 

[Andrew Poulsom]

Welcome to the Board.

Where did you put those 2 lines of code? This:

Application.AskToUpdateLinks = False

needs to happen before the workbook is opened.

 

[Hitesh_Banda]

There is a function which displays the "Select Folder" dialog box and returns the selected folder path to a variable called msg, and here's the main function for consolidate.



Sub consolidate()
Range("A8").Select
Call selectdir 'Selectdir is the function i was talking about...for selecting folder
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = msg
ChDir sPath
sFil = Dir("*.xl*")
Do While sFil <> ""
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Set oWbk = Workbooks.Open(sPath & "\" & sFil, UpdateLinks:=xlUpdateLinksNever)
oWbk.Activate
Sheets("Milestone Tracker").Select
Range("A2").Select
If (ActiveCell.Value <> "") Then
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End If
ThisWorkbook.Activate
Range("A7").Select
Selection.End(xlDown).Select
If (ActiveCell.Row > 60000) Then
Range("A8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
oWbk.Close (0)
sFil = Dir
Loop
End Sub

 

[Andrew Poulsom]

Since you have used the constant xlUpdateLinksNever you must have Excel 2002 or later. I don't know the value of that constant, but I would use 0 (zero). What Startup Prompt settings do you heve for the offending workbook(s)? Do they contain any invalid or broken links? More here:

http://support.microsoft.com/kb/826921

 

[Hitesh_Banda]

Thanks for you time Andy..

I am using Excel 2007. I tried the option suggested by you (using 0).I also tried unchecking the option "Update Links to other documents" and re-opening the file and re-running the macro, again everything in-vain

Forgot to mention that some of my files contain broken-links, which is the reason why I am facing the error.

 

[Andrew Poulsom]

Do you still get prompted to update links if you open the offending workbook(s) manually (with Ask to update links unchecked)? If so one of the links may be invalid or broken.

 

[Hitesh_Banda]

Yes Andy.. some of the files contain broken links, which is why the macro is facing problem. I want the macro to ignore the broken links.

 

[Andrew Poulsom]

You can't. On the link I gave you it says:

Regardless of the options that you choose, Excel still displays a message if the workbook contains links that are not valid or links that are broken.

 

[Hitesh_Banda]

Thanks a lot buddy... I will change my procedure in a more favourable way then.

 

[v0vets]

We've moved from Office 2010 to 2021 and found strange Excel's bahavior with external links when opening w/book as well. It looks like in some circumstances Excel does update links ignoring its settings. Or, more precisely it opens external woorkbook (in the background) and AFTER that checks the settings. If settings is "don't update links", it just closes the external workbook. )) And if not - updates then. )))
The same situation was with Word-2010 and was fixed by inserting into the registry special keys ("NoActivateOleLinkObjAtOpen"=dword:00000001 + "DelayOleSrvParseDisplayName"=dword:00000001)
Unfortunately we still hasn't found similar solution for Excel.