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
Prevent the Update Links dialog from showing.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Prevent the Update Links dialog from showing.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Prevent the Update Links dialog from showing.
When are you being prompted? When you open the destination workbook or when you run the macro?
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Prevent the Update Links dialog from showing.
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. ??
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. ??
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Prevent the Update Links dialog from showing.
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Prevent the Update Links dialog from showing.
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):
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Prevent the Update Links dialog from showing.
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. :)
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. :)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Prevent the Update Links dialog from showing.
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.
Thanks, I had a marvelous holiday in Italy.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Prevent the Update Links dialog from showing.
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
Thanks for the help.
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.