Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to avoid the pop up message for updating links? | Excel Discussion (Misc queries) | |||
Avoid Update Links dialog in Excel 2003 | Links and Linking in Excel | |||
Can I avoid annoying Update Links message | Excel Discussion (Misc queries) | |||
How to avoid Update Links notification? | Excel Programming | |||
Why Am I Getting Links and How to Avoid Them | Excel Programming |