UpdateLinks

[Howard Kaikow]

I want to avoid the update links message when a workbook is opened, but
only for that workbook.

So I created a module with the code below.
However Auto_open is run AFTER the update links dialog appears.
I also tried using Workbook_Open.

How do I stop the dialog from appearing?
I do want to update every time this workbook is opened.

Public Sub Autpen()
Application.AskToUpdateLinks = False
End Sub

Public Sub Auto_Close()
Application.AskToUpdateLinks = True
End Sub

 

[cush]

I recently used this code and found it equivalent to Tools>Options>Edit and
unchecking the "Ask to update links automatically"

But it triggered after the wbk was opened, as you have found ( whether I put
it in Wbk_Open or Autpen). Hence, I did not turn it off during wbk_close

The result is that on all subsequent Excel sessions, with this wbk or
others, I was not hassled with the dialog. (Which was fine with me, as I
don't have any use where I would open a workbook that I don't want updated.)

 

[Norman Jones]

Hi Howard,

If you are opening the workbook programmatically, try:

Sub Tester()
Workbooks.Open "MyBook.xls", UpdateLinks:=True
End Sub

If the workbook is to be opened manually, consider using a dummy workbook.
In the dummy book include the code:

Private Sub Workbook_Open()
Workbooks.Open "MyBook.xls", UpdateLinks:=True
ThisWorkbook.Close SaveChanges:=False
End Sub

 

[Howard Kaikow]

Hi Howard,

If you are opening the workbook programmatically, try:

Sub Tester()
Workbooks.Open "MyBook.xls", UpdateLinks:=True
End Sub

If the workbook is to be opened manually, consider using a dummy workbook.
In the dummy book include the code:

Private Sub Workbook_Open()
Workbooks.Open "MyBook.xls", UpdateLinks:=True
ThisWorkbook.Close SaveChanges:=False
End Sub

That may work, but then I'd need a dummy workbook to correspond to each
workbook for which this is an issue.

I was thinking of trying a BeforeXXX event, if I can find one that executes
before the dialog.
Or maybe an Activate event?

 

[Norman Jones]

Hi Howard,

I was thinking of trying a BeforeXXX event, if I can find one that
executes
before the dialog.
Or maybe an Activate event?

I doubt that this will be fruitful.

At: http://www.cpearson.com/excel/events.htm

Chip Pearson lists the order of event Procedures as:

'=================================

When opening a workbook, the order of events is as follows:

1. Workbook_Open
2. App_WorkbookOpen
3. Workbook_WindowDeactivate (of previous workbook)
4. App_WindowDeactivate (of prevous workbook)
5. Workbook_Deactivate (of previous workbook)
6. Workbook_Activate
7. App_WorkbookActivate
8. Workbook_WindowActivate
9. App_WindowActivate
10. Autpen

'=================================

 

[Howard Kaikow]

Hi Howard,


I doubt that this will be fruitful.

Ayup, it was not fruitful.

Easiest may be to write a VB 6 program that presents a list of the affected
workbooks(currently just one), then put a shortcut to that .exe where
useful.

Since there is only 1 workbook affected now, i may just have the vb program
do the open.

 

[Dave Peterson]

The bad news is that the links are handled before any code (in that workbook)
starts running.

Maybe you could create a dummy workbook that allows the user to pick the
workbook to open.

 

[Howard Kaikow]

I ended up using a dummy workbook with the following

Private Sub Workbook_Open()
Dim wbk As Excel.Workbook
Set wbk = Workbooks.Open(StringForPathToRealWorkbook, UpdateLinks:=True)
wbk.Save
Set wbk = Nothing
ThisWorkbook.Close savechanges:=False
End Sub


If I ever feel a need to do this for more than 1 workbook, I'll create a VB
6 .exe to handle all the workbooks.

 

[Lonnie M.]

I was just asked this question by a coworker and I tried the following
code, which worked for our purposes. I understand that the 'update
links' fires before the 'Workbook_Open' code is run, but the links
are remote for the end users of this document and they are not prompted
to 'Update Links', 'Continue', or 'Edit' links.

Public Sub Workbook_Open()
If Application.Version >= "11.0" Then
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.DisplayAlerts = True
End If
End Sub

Then I got to thinking about what was really happening above and I
tried this with the same result:

Public Sub Workbook_Open()
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End Sub

It worked for my needs--it may not be the right solution every time.

Regards--Lonnie M.