Prevent the Update Links dialog from showing.

[Rudi]

Hi all,

Below is a macro that updates links from a source workbook that has a password protection on it. My aim is to update links in my destination workbook without being prompted for the password to the source. The macro is doing this, but I am still hampered by this irritation of being propmted if I wanna update links. How can I stop this dialog appearing. In the macro you will see my attempts that are NOT working... (the first 3 lines!!!). Please HELP!! :)


Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Const cstrPASSWORD As String = "pass"
Dim varLink
Dim wbk As Workbook
For Each varLink In ThisWorkbook.LinkSources(xlExcelLinks)
Set wbk = Workbooks.Open(Filename:=varLink, Password:=cstrPASSWORD, UpdateLinks:=xlUpdateLinksNever)
Calculate
wbk.Close False
Next varLink
Application.AskToUpdateLinks = True
Application.Calculation = xlCalculationAutomatic

[HansV]

When are you being prompted? When you open the destination workbook or when you run the macro?

[Rudi]

when I open the destination workbook.

I have the code in the workbook_open event and I have it as a module macro. the module macro works fine, but its always prompting when I open the destination workbook...even with the event macro there. ??

[Rudi]

Actually my issue is not really the macro...as this works to update the destination without prompting for the password to the source. all I need now is to stop the update link dialog from prompting when I open the destination. TX

[HansV]

You can turn off the prompt from the Edit Links dialog (Excel 2003 or earlier: Edit | Links, Excel 2007: Office button > Prepare > Edit Links to Files):

x358.png

[Rudi]

I'll give it a go. TX.

PS: Is there a way to do this programmatically? Just out of interest, to provide the complete solution in one macro without the need for two actions.
PPS: Welcome back...I hope your leave was restful and recuperating. :)

[HansV]

You could create another workbook with a macro that opens the target workbook with the option not to update links.

Thanks, I had a marvelous holiday in Italy.

[Rudi]

Ok...that will just get more complicated then. I think your screenshot answer will be better in this case since its only one file.

Thanks for the help.
Cheers