+ Reply to Thread
Results 1 to 3 of 3

Suppress Update Links Message

  1. #1
    Registered User
    Join Date
    02-04-2005
    Posts
    36

    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

  2. #2
    Jim Rech
    Guest

    Re: 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" <[email protected]> wrote in
    message news:[email protected]...
    |
    | 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. #3
    Dave Peterson
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1