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

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