VBA code to remove external links in Excel 365 fails due to Microsoft patch updates after

Reposted from a different forum. Had a number of views in a different forum, but no comments:

Long story short: I had code that has been working for years that suddenly breaks due to Microsoft patch update. The VBA code I wrote copies several worksheets to a new workbook. I have links with named ranges which were able to normally convert to hard-coded values upon breaking the link, and now doesn't break and I'm getting "$NAME?" errors everywhere. I have tried to change security settings for macro security "Enable VBA macros (not recommended; potentially dangerous code can run)", but that fails too.

Any comments or suggestions are appreciated!

This is the code which worked, but now fails:

astrLinks = wkbDisk.LinkSources(Type:=xlLinkTypeExcelLinks)

If IsArray(astrLinks) Then
/* code fails in next portion where it's supposed to break link to */
For iCtr = LBound(astrLinks) To UBound(astrLinks)
wkbDisk.BreakLink Name:=astrLinks(iCtr), _
Type:=xlExcelLinks
Next iCtr
End If
/* end of code containing VBA error */

I have run into a similar issue. This is the code I used to fix it. You may need to make some adjustments but it will give you a good start.

Please Login or Register  to view this content.

Hi beginnerVGAmoron,

First, thank you very much for taking the time to respond. I had long given up on this issue after months of no response, so now I just copy and value paste the sheets, but unfortunately loses the formulas.

I tried to copy the exact code (3 subroutines) and called "Find_namedrange_place". The stand-alone code doesn't work as several variables are not declared, so I guessed what they should be declared to:

Dim Destwb As Workbook ' 1/11/2023 GDC Need to add this line to make it work
Dim I As Long ' 1/11/2023 GDC Need to add this line to make it work
Dim ExternalLinks As Variant ' 1/11/2023 GDC Need to add this line to make it work
Dim NmArray As Variant ' 1/11/2023 GDC Need to add this line to make it work
Dim NmArrayDel As Variant ' 1/11/2023 GDC Need to add this line to make it work
Dim nm As Name ' 1/11/2023 GDC Need to add this line to make it work
Dim NmdRng As Variant ' 1/11/2023 GDC Need to add this line to make it work

The code finally ran, but didn't clear the errors I had before, so I'm probably missing something. Can you give me a high-level understanding of what the subroutines are doing? Maybe that will help me modify my code.

Thank you again!

Garland

Thank you for the updates I wasn't using option explicit and must have gotten sloppy.

The basics of the code are that
Line 2 makes sure that the work book doesn't calc and will cause the ranges to break
Lines 14-23 Breaks external links not attributable to named ranges
Lines 27-43 Gets the "Names" of the named ranges and then gets the named ranges and stores them in the lists for later
Lines 48-80 Finds the cells that use external named ranges and then sets them to their values
Lines 82-84 Deletes the named ranges from the work book

You need call this code each time a new worksheet is copied into the book. You could make the sheet a variable and then use that for xShName.
Make sure the workbook is not being calculated during this process (Application.Calculation = xlCalculationManual this should be used as early as possible in your code you can set it back to auto at the end if wanted)
Make sure that the names of the named ranges are in NmArray can be checked after line 56 with Debug.Print(NmdRng)