VBA: Save As Workbook still refers to original Workbook: Annoying Update Links every time

[Bill Bisco]

Dear all,

Any help would be greatly appreciated on this annoying issue.

I have Excel code to save the current workbook with a specified name

ActiveWorkbook.Sheets.Copy 'Create new workbook
Fname = sItem & "\" & strLegalFileName(MyFile)
    'SAVES FILE USING THE VARIABLE BOOKNAME AS FILENAME 'Replace with proper code to specify location and filename
    ActiveWorkbook.SaveAs filename:=Fname, FileFormat:=52 _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

Whenever I open up one of the files that was "Saved as" I get that annoying update links message at the top:

It still think that the file that was saved should actually be the original file. In other words, my original file was in Folder1 and named Master and my copied file is in Folder2 and Named Copy1, but Copy1 still retains a link to Master.

I have tried putting in

Application.AskToUpdateLinks = False but that doesn't seem to work.

I can manually edit the TrustCenter settings to prevent the popups, but I cannot force users to change their settings.

Any suggestions?

Sincerely,

Bill

 

[JLGWhiz]

I got this from StackOverflow website. Give it a try.

Sub BreakExternalLinks()
'PURPOSE: Breaks all external links that would show up in Excel's "Edit Links" Dialog Box
'SOURCE: [URL="http://www.TheSpreadsheetGuru.com/The-Code-Vault"]www.TheSpreadsheetGuru.com/The-Code-Vault[/URL]
Dim ExternalLinksArray As Variant
Dim wb As Workbook
Dim x As Long
Set wb = ActiveWorkbook
'Create an Array of all External Links stored in Workbook
  ExternalLinksArray = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
'if the array is not empty the loop Through each External Link in ActiveWorkbook and Break it
 If IsEmpty(ExternalLinksArray) = False then
     For x = 1 To UBound(ExternalLinksArray )
        wb.BreakLink Name:=ExternalLinksArray (x), Type:=xlLinkTypeExcelLinks
      Next x
end if
End Sub