How to avoid Update Links notification?

[E.J. van Wijngaarden]

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

 

[Damien McBain]

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