Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Excel 2003 remote links update | Excel Discussion (Misc queries) | |||
Excel 2003 remote links update | Excel Discussion (Misc queries) | |||
Manual Update of Links in Excel 2003 | Links and Linking in Excel | |||
Can I avoid annoying Update Links message | Excel Discussion (Misc queries) |