Avoid Update Links dialog in Excel 2003

Ian Chappel · External Since: Jun 28, 2007 Posts: 2

I have a workbook with links, and I don't want to be asked whether I'd like
to update these, as the Workbook_Open event will open them in any case.

I've set the "Startup Prompt" for this workbook to the bottom (3rd) option,
but I still get asked every time. Any idea why?

Bob Flanagan · External Since: Apr 03, 2004 Posts: 113

If you have a macro in another workbook (say your personal.xls) open the
files with links, you can set the option to not update links and it will not
prompt to update. If you need help on the code, just repost.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
news:uECdfKduHHA.4572@TK2MSFTNGP02.phx.gbl...
>I have a workbook with links, and I don't want to be asked whether I'd like
>to update these, as the Workbook_Open event will open them in any case.
>
> I've set the "Startup Prompt" for this workbook to the bottom (3rd)
> option, but I still get asked every time. Any idea why?
>
>

Ian Chappel · External Since: Jun 28, 2007 Posts: 2

Thanks Bob,

Any code would have to be in the workbook I'm opening (a template for other
workbooks), which will always have a link(s) to other workbooks, which I
open with code. The code, which works fine except I can't suppress the Links
Dialog, is in fact:

Private Sub Workbook_Open()

Dim i As Integer
Dim alinks As Variant
Dim strWbName As String

MainEst.GetRow

alinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If IsEmpty(alinks) Then
Workbooks.Open MainEst.sMainPath & "Library.xls"
Else
For i = 1 To UBound(alinks)
strWbName = CStr(alinks(i))
If InStr(strWbName, "Library.xls") > 0 Or InStr(strWbName, ".dim") >
0 Then
Workbooks.Open (strWbName)
End If
Next i
End If

Set rLastCell = Cells(5, 5)
Set rLastSheet = ThisWorkbook.Sheets(1)

End Sub

"Bob Flanagan" <noreply.DeleteThis@noreply.net> wrote in message
news:A82dnS5iDcbj1hnbnZ2dnUVZ_oipnZ2d@comcast.com...
> If you have a macro in another workbook (say your personal.xls) open the
> files with links, you can set the option to not update links and it will
> not prompt to update. If you need help on the code, just repost.
>
> Bob Flanagan
> Macro Systems
> 144 Dewberry Drive
> Hockessin, Delaware, U.S. 19707
>
> Phone: 302-234-9857, cell 302-584-1771
> http://www.add-ins.com
> Productivity add-ins and downloadable books on VB macros for Excel
>
> "Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
> news:uECdfKduHHA.4572@TK2MSFTNGP02.phx.gbl...
>>I have a workbook with links, and I don't want to be asked whether I'd
>>like to update these, as the Workbook_Open event will open them in any
>>case.
>>
>> I've set the "Startup Prompt" for this workbook to the bottom (3rd)
>> option, but I still get asked every time. Any idea why?
>>
>>
>
>

Bob Flanagan · External Since: Apr 03, 2004 Posts: 113

Ian, immediately before the open statement, put

Application.EnableEvents = False

and immediately after it, put

Application.EnableEvents = True

You should put an error trap in in case the workbook doesn't open and an
error stops you code. The error trap would set enable envents back to true.
If you don't and an error happens, then whenever you close a modified
workbook if EnableEvents is False, you will not be prompted to save the
workbook.

On Error goto eTrap:
Application.EnableEvents = True
'code to open file
Application.EnableEvents = False
On Error goto 0
'more code
Exit Sub
Etrap:
Msbox "Woops"
Application.EnableEvents = True

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
news:epa9iCiuHHA.4868@TK2MSFTNGP02.phx.gbl...
> Thanks Bob,
>
> Any code would have to be in the workbook I'm opening (a template for
> other workbooks), which will always have a link(s) to other workbooks,
> which I open with code. The code, which works fine except I can't suppress
> the Links Dialog, is in fact:
>
> Private Sub Workbook_Open()
>
> Dim i As Integer
> Dim alinks As Variant
> Dim strWbName As String
>
> MainEst.GetRow
>
> alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
>
> If IsEmpty(alinks) Then
> Workbooks.Open MainEst.sMainPath & "Library.xls"
> Else
> For i = 1 To UBound(alinks)
> strWbName = CStr(alinks(i))
> If InStr(strWbName, "Library.xls") > 0 Or InStr(strWbName, ".dim")
> > 0 Then
> Workbooks.Open (strWbName)
> End If
> Next i
> End If
>
> Set rLastCell = Cells(5, 5)
> Set rLastSheet = ThisWorkbook.Sheets(1)
>
> End Sub
>
> "Bob Flanagan" <noreply.RemoveThis@noreply.net> wrote in message
> news:A82dnS5iDcbj1hnbnZ2dnUVZ_oipnZ2d@comcast.com...
>> If you have a macro in another workbook (say your personal.xls) open the
>> files with links, you can set the option to not update links and it will
>> not prompt to update. If you need help on the code, just repost.
>>
>> Bob Flanagan
>> Macro Systems
>> 144 Dewberry Drive
>> Hockessin, Delaware, U.S. 19707
>>
>> Phone: 302-234-9857, cell 302-584-1771
>> http://www.add-ins.com
>> Productivity add-ins and downloadable books on VB macros for Excel
>>
>> "Ian Chappel" <ichappAThotmailDOTcoDOTuk> wrote in message
>> news:uECdfKduHHA.4572@TK2MSFTNGP02.phx.gbl...
>>>I have a workbook with links, and I don't want to be asked whether I'd
>>>like to update these, as the Workbook_Open event will open them in any
>>>case.
>>>
>>> I've set the "Startup Prompt" for this workbook to the bottom (3rd)
>>> option, but I still get asked every time. Any idea why?
>>>
>>>
>>
>>
>