Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid Update Links notification?
Hello experts,
In workbook A I add a worksheet using another workbook B as template, all in VBA code. This workbook B contains references to named regions in workbook A, which works fine once it is loaded. Now I have the following problem: on adding the new worksheet Excel notifies that in workbook B there are links to external workbooks and asks if they must be updated. I want to get rid of this notification. I set Application.Displayalerts to False before adding workbook B, but this has no effect on this notification. Thanks for your help! Ed van Wijngaarden |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to avoid Update Links notification?
"E.J. van Wijngaarden" wrote in message
... Hello experts, In workbook A I add a worksheet using another workbook B as template, all in VBA code. This workbook B contains references to named regions in workbook A, which works fine once it is loaded. Now I have the following problem: on adding the new worksheet Excel notifies that in workbook B there are links to external workbooks and asks if they must be updated. I want to get rid of this notification. I set Application.Displayalerts to False before adding workbook B, but this has no effect on this notification. I have the same scenario in some of my developments. I get agound it by (in the new workbook) copying the cells with the formulas you want to remove then pasting values. One looks like this: Sub CreateOpsFile() Application.ScreenUpdating = False Sheets("Operations").Select Sheets("Operations").Copy Workbooks(Workbooks.Count).Activate ActiveSheet.Unprotect Range("A1").Copy Range("A1").PasteSpecial Paste:=xlPasteValues Range("B3:B7").Copy Range("B3:B7").PasteSpecial Paste:=xlPasteValues Range("B11:L20").Copy Range("B11:L20").PasteSpecial Paste:=xlPasteValues Range("B22:L23").Copy Range("B22:L23").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A1").Select ChDir "I:\Accounting\Daily Tonnes\DailyReports" ActiveWorkbook.SaveAs Filename:= _ "I:\Accounting\Daily Tonnes\DailyReports\" & Range("Date") & "-Ops.xls" ActiveWindow.Close MsgBox "File: I:\Accouting\Daily Tonnes\DailyReports\" & Range("Date") & "-Ops.xls has been created", , "Daily Tonnes" Sheets("Main").Activate Range("Date").Select Application.ScreenUpdating = True End Sub In this way I preserve the local formulas such as totalling columns but remove the formulas referring to other worksheets/workbooks. HTH Damo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoid Update Links dialog in Excel 2003 | Links and Linking in Excel | |||
Avoid "update Links" promt in e-mailed pivot tables? | Excel Discussion (Misc queries) | |||
How to avoid "update links" startup prompt? | Excel Discussion (Misc queries) | |||
Is it possible to receive notification of a file update? | Excel Discussion (Misc queries) | |||
Can I avoid annoying Update Links message | Excel Discussion (Misc queries) |