Avoid DIsplay of links
 

Hi,
I create client package by exporting a series of sheets to a new workbook.
I want to programmatically prevent the update links popup from showing. I
want to have this code inserted into the newly created package. How can i do
this in the export process used below:

Sub Mail_Sheets_Array()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("Daily Report", "T-30 Days", "T-30 Graphs",
"Monthly Graphs", "Year at a Glance")).Copy
Set Destwb = ActiveWorkbook


'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog
that you only
'see when you copy sheets from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheets to values if you want
' For Each sh In Destwb.Worksheets
' sh.Select
' With sh.UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Destwb.Worksheets(1).Select
' Next sh

'Save the new workbook/Mail it/Delete it


TempFilePath = Environ$("temp") & "\"
TempFileName = "Copreco Client Report Package for" & " " & Format(Now,
"dd-mmm-yyyy")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete

On Error Resume Next
.SendMail "", _
"Copreco Daily Report Package"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Sub SendToDesktop(WB As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim myPath As String
Dim myShortcutPath As String
Dim sStr As String

With WB
myPath = .FullName
sStr = "\" & Left(.Name, Len(.Name) - 4)
End With

Set oWSH = CreateObject("WScript.Shell")

With oWSH
myShortcutPath = .SpecialFolders.Item("Desktop")
Set oShortcut = .CreateShortcut _
(myShortcutPath & sStr & ".lnk")
End With

With oShortcut
.TargetPath = myPath
.Save
End With

Set oWSH = Nothing
End Sub


--
Carlee