Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I avoid annoying Update Links message | Excel Discussion (Misc queries) | |||
Update Links problem | Excel Worksheet Functions | |||
Automatically update links when server names change | Excel Worksheet Functions | |||
keep value from last update and don't ask to update links | Excel Discussion (Misc queries) | |||
Update Links - Problem | Links and Linking in Excel |