VBA Break Workbook Links: What the Macro Actually Does

VBA & macros · Updated November 18, 2026

Yes. Excel VBA can break external workbook links: Excel replaces formulas that depend on linked workbooks with their current calculated values, so those formulas no longer update from the source file. The macro below targets Excel workbook links only; save a separate copy first, because breaking links is destructive and cannot be undone with Ctrl+Z.

What “breaking a workbook link” means

A workbook link is a reference from one Excel file to another. For example, a formula in a report might contain an external reference such as ='[Sales.xlsx]January'!B4. While the link remains, Excel can use values from Sales.xlsx and may update them when the source workbook changes or when the link is refreshed.

Breaking that link is not the same as deleting the formula’s cell or closing the source file. Excel removes the external dependency by replacing the linked formula with its current result. If the formula currently returns 1250, the destination cell becomes the number 1250 rather than a formula pointing to Sales.xlsx. The result remains, but it will not change when the source workbook changes.

This is useful when distributing a report that should no longer depend on a source file, archiving a final snapshot, or removing prompts to update links. It is unsuitable if recipients need live calculations from the source workbook. Once broken, the original formula is not recoverable from that cell unless you restore a backup or recreate it.

VBA macro to break Excel workbook links

This macro checks the active workbook for links to other Excel workbooks, reports when none are found, and breaks each link it finds. Activate the workbook you intend to change before running it. The code deliberately uses xlLinkTypeExcelLinks, so it does not claim to remove every kind of external connection in an Excel file.

Sub BreakExcelWorkbookLinks()
    Dim wb As Workbook
    Dim links As Variant
    Dim i As Long

    ' The active workbook is the workbook this macro will change.
    Set wb = ActiveWorkbook

    ' Get the list of links to other Excel workbooks.
    links = wb.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsEmpty(links) Then
        MsgBox "No Excel workbook links were found."
        Exit Sub
    End If

    ' Break each link; linked formulas become their current values.
    For i = LBound(links) To UBound(links)
        wb.BreakLink Name:=CStr(links(i)), _
            Type:=xlLinkTypeExcelLinks
    Next i

    MsgBox "Excel workbook links have been broken."
End Sub

The macro does not create a backup, update source data, or ask you to confirm each link. Its success message means the listed Excel links were processed; it is still sensible to inspect the workbook afterward. If the active workbook is not the intended file, the macro can change the wrong one.

How LinkSources and BreakLink work

LinkSources asks the workbook for a list of links of a specified type. Here, the type is xlLinkTypeExcelLinks, which limits the list to links to Excel workbooks. If there are no such links, the method returns an empty result and the macro exits without attempting to break anything.

The macro stores the returned list in a variable before changing the workbook. It then passes each source name to BreakLink. This matters because breaking a link changes the workbook’s link state. Capturing the names first gives the loop a list to work through even as links are removed. The CStr conversion supplies each name as text.

Breaking a link usually converts formulas that refer to that external workbook into fixed values. It is not a general-purpose search-and-delete routine for every path or filename stored anywhere in a workbook. If a workbook contains several external sources, the list can contain several entries, and the macro processes each listed Excel source.

How to add and run the macro

  1. Open the workbook you want to change and save a separate backup copy. Confirm that the workbook you want to modify is the active window.
  2. On Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn.
  3. In the editor, choose Insert > Module, then paste the macro into the new module.
  4. Return to Excel. On Windows, press Alt+F8, select BreakExcelWorkbookLinks, and choose Run. You can also run it from the Visual Basic Editor with the cursor inside the procedure and F5.
  5. On Mac, use Excel’s Tools > Macro > Macros menu to select and run the procedure; menu placement can vary by Excel build. If the Developer tab is visible, its Macros command is another route.

If you want to keep the code inside the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). An ordinary .xlsx file cannot store VBA code. If you only need to run the macro once, you can keep the code in a separate trusted macro workbook, but take care that the target workbook—not the macro container—is active when the code runs. Do not enable macros in files from untrusted sources just to run a link-removal routine.

What the macro changes—and what it may leave behind

The main change is loss of live formula links. A value produced by an external-reference formula is retained as a fixed result, while the formula’s connection to the source is removed. That can make a workbook easier to send or archive, but it also means recalculation cannot retrieve newer source values. If the displayed values are outdated, breaking links preserves outdated results; check that source data is current before proceeding.

The macro is specifically about Excel workbook links. Excel workbooks can also contain other external data arrangements, including Power Query queries, data connections, hyperlinks, OLE links, and references embedded in objects or code. These are not all interchangeable with workbook formula links, and this macro is not a reliable way to remove them. A hyperlink to a file, for instance, is a clickable address, not necessarily a formula link that BreakLink will remove.

After running the code, review Excel’s link-management interface if your version provides it. In current Microsoft 365 for Windows, look on the Data tab for Workbook Links; older builds may show Edit Links instead. The exact interface can vary by update and platform. Also inspect formulas, names, charts, queries, and connections if your goal is a workbook with no external dependencies at all.

Safety checks before and after running

Breaking links changes workbook content, and VBA changes generally cannot be reversed with Excel’s Undo command. A backup is the dependable way to recover the original formulas. Save the copy under a different filename or location, then run the macro only in the copy if the source workbook must remain intact.

  • Check the active file: read the workbook name in Excel’s title bar before running the macro.
  • Check the values first: open or refresh the relevant sources as appropriate, and verify that the linked results are the values you intend to preserve.
  • Consider calculation: if formulas have not recalculated, the values preserved may not reflect the latest source data. Review calculation settings and results before breaking links.
  • Expect formula loss: cells may look the same after the change, but their formulas have been replaced by constants.
  • Validate the result: save, close, and reopen the copy, then check whether Excel still reports the external references you meant to remove.

If you need an auditable process, record which workbook you changed, when you changed it, and whether the output was checked. The macro’s message box is not a detailed log: it does not list each source, preserve the original formulas, or certify that every possible external reference is gone.

Common problems and how to diagnose them

The macro says no links were found. The workbook may have no links to other Excel workbooks, or the dependency you are seeing may be a query, connection, hyperlink, or another type of reference. Check the Data tab’s link or connection controls and inspect the relevant formulas. A filename appearing in a hyperlink does not by itself mean LinkSources will report it as an Excel workbook link.

Excel still prompts about external content. The prompt may be caused by something other than the Excel links this macro handles. Review workbook links and separately inspect queries, data connections, and other external-content features. Removing one category does not guarantee that all external content has been removed.

The output values are wrong or blank. The macro does not fetch fresh data from the source before breaking links. Confirm that the linked formulas show the intended results first. If the source is unavailable, Excel may have only previously saved or otherwise incomplete values available.

The macro errors or changes the wrong workbook. Confirm that the target file is open and active, and that macros are allowed for this trusted file. If the code is launched from a personal macro workbook or add-in, ActiveWorkbook can refer to a different file than the one containing the macro. In that setup, explicitly assign the intended open workbook to wb rather than relying on whichever workbook is active.

VBA versus Excel’s built-in link controls

For a one-time cleanup, Excel’s built-in workbook-link controls can be easier because they show link sources and, in interfaces that support it, let you select a particular source to break. Find Workbook Links or Edit Links on the Data tab, depending on your Excel version. Review the listed sources before choosing a break command; the operation has the same important consequence of removing live links and retaining results.

VBA is more useful when the same operation must be repeated across many files or included in a controlled workflow. It can automate the Excel-link portion, but the short macro above does not report each link, ask for confirmation, or process a folder of workbooks. For a single file where you want to inspect sources individually, use the interface. For repeatable automation, adapt and test VBA on copies, and add logging or confirmation if those controls matter.

Frequently Asked Questions

Does breaking an Excel link delete the cell’s value?
No. For linked formulas, Excel normally replaces the formula with its current result. The cell keeps that result as a fixed value, but it no longer updates from the source workbook.

Can I undo a VBA link break with Ctrl+Z?
No. Excel’s Undo command does not reverse VBA changes. Restore the pre-change backup to get the original formulas back, or recreate the formulas manually.

Will this macro remove Power Query connections?
No. The macro asks for Excel workbook links through LinkSources and breaks those links. Queries and data connections are separate features and must be reviewed and managed separately.

Can I break links in every workbook in a folder?
The code shown processes only the active workbook. Folder-wide automation needs additional VBA to open, identify, modify, save, and close each file, so test that workflow on copies and account for file formats and errors before using it on important workbooks.

Can I keep the workbook as an .xlsx after running the macro?
Yes, if you do not need to retain the VBA code in that workbook. Save the finished workbook as .xlsx only after the link-breaking macro has run; Excel will remove VBA code from an .xlsx file. Use .xlsm if the workbook itself must keep the macro.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.