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

blatham wrote:
>
> 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...o&userid=19441
> View this thread: http://www.excelforum.com/showthread...hreadid=534488

--

Dave Peterson