Avoid Update Links dialog in Excel 2003
 

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?

Avoid Update Links dialog in Excel 2003
 

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
...
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?

Avoid Update Links dialog in Excel 2003
 

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" wrote in message
. ..
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
...
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?

Avoid Update Links dialog in Excel 2003
 

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
...
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" wrote in message
. ..
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
...
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?