Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 20th 06, 10:57 AM posted to microsoft.public.excel.misc
blatham
 
Posts: n/a
Default Suppress Update Links 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...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=534488


  #2   Report Post  
Old April 20th 06, 02:20 PM posted to microsoft.public.excel.misc
Jim Rech
 
Posts: n/a
Default Suppress Update Links Message

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


  #3   Report Post  
Old April 20th 06, 02:34 PM posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Suppress Update Links Message

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I avoid annoying Update Links message phillyjoe Excel Discussion (Misc queries) 2 October 29th 05 03:00 PM
Update Links problem andyp161 Excel Worksheet Functions 1 August 31st 05 04:19 PM
Automatically update links when server names change J Hotch Excel Worksheet Functions 1 August 3rd 05 03:50 PM
keep value from last update and don't ask to update links jh3016 Excel Discussion (Misc queries) 3 July 25th 05 01:37 AM
Update Links - Problem Metallo Links and Linking in Excel 2 January 25th 05 04:42 PM


All times are GMT +1. The time now is 02:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017