Suppress Update Links Message

[blatham]

I have a spreadsheet with links to another workbook that take a long
time to update if the external workbook is closed. To prevent this I
have written a macro that fires on the open event of the workbook that
automatically opens the links. However the message box asking whether
the user wants to update links or not still appears (even though I
suppress alerts in the on open macro) so there is still the opportunity
for a user to click 'Update' and be waiting ages.

Is there a way to suppress this message does anyone know as it appears
to occur before the on open macro fires.

Regards

Ben

 

[Jim Rech]

If you have Excel 2002 or 2003 the Edit, Links, Startup Prompt command gives
you greater control over how links update. Otherwise I don't think there is
an answer.

--
Jim
message |
| I have a spreadsheet with links to another workbook that take a long
| time to update if the external workbook is closed. To prevent this I
| have written a macro that fires on the open event of the workbook that
| automatically opens the links. However the message box asking whether
| the user wants to update links or not still appears (even though I
| suppress alerts in the on open macro) so there is still the opportunity
| for a user to click 'Update' and be waiting ages.
|
| Is there a way to suppress this message does anyone know as it appears
| to occur before the on open macro fires.
|
| Regards
|
| Ben
|
|
| --
| blatham
| ------------------------------------------------------------------------
| blatham's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=19441
| View this thread: http://www.excelforum.com/showthread.php?threadid=534488
|

 

[Dave Peterson]

How about just having another workbook that opens your workbook with the links
updated/not updated the way you want.

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=0
ThisWorkbook.Close savechanges:=False
End Sub

You could enhance this to open the other files first, then open the real
workbook. Then the links would be refreshed (quicker???).

Saved from a previous post:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim myPasswords As Variant
Dim iCtr As Long
Dim myRealWkbk As Workbook
Dim myRealWkbkName As String
Dim wkbk As Workbook

myRealWkbkName = "C:\my documents\excel\book1.xls"

myFileNames = Array("C:\my documents\excel\book11.xls", _
"C:\my documents\excel\book21.xls", _
"C:\my other folder\book11.xls")

myPasswords = Array("pwd1", _
"pwd2", _
"pwd3")

If UBound(myFileNames) <> UBound(myPasswords) Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0)

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _
Password:=myPasswords(iCtr))
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox "Check file: " & myFileNames(iCtr)
Exit Sub
End If

wkbk.Close savechanges:=False
Next iCtr

End Sub

(I got bored after 3 workbooks. You may want to test it with a couple to get it
going.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm