VBA & Macro Problems

How to Break OLE Links in Multiple Excel Workbooks Using VBA

Elise Williams Oct 21, 2026 869 views

Question details

The user needs a method to remove or disable broken OLE links in multiple Excel files simultaneously.

How to Break OLE Links in Multiple Excel Workbooks Using VBA

Product: Excel
Device & OS: not provided
Scenario: After an accounting system change, multiple existing Excel files are displaying OLE link errors and need to be cleaned up.
Observed behavior: OLE links in the affected workbooks are broken, triggering update prompts and error messages when the files are opened.

Before you start

Because VBA macros will permanently modify your files by breaking links and saving the changes, it is strongly recommended to copy all affected Excel workbooks into a separate backup folder before running the script.

Solution 1Recommended

Use a VBA Macro to Break OLE Links in a Folder

Automate the process of opening, breaking links, and saving multiple Excel files within a specific folder using a VBA script.

This macro will loop through all Excel files in a designated folder. It opens each workbook, identifies any OLE links, breaks them, saves the file, and closes it. You can adapt the code to include subfolders if necessary by utilizing the FileSystemObject.

Open the VBA Editor

Open a blank Excel workbook and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu and select 'Module' to create a blank script window.

Paste the Link-Breaking Code

Write or paste a VBA script that utilizes the Dir function to loop through your folder, opens each workbook, and uses the ActiveWorkbook.BreakLink method specifically targeting xlLinkTypeOLELinks.

Set the Target Folder Path

Within your script, modify the folder path string (e.g., 'C:\YourPath\AccountingFiles\') to point to the directory containing the workbooks you want to process.

Run the Macro

Press F5 or click the Run button to execute the macro. Allow Excel to process all files in the background.

Use a VBA Macro to Break OLE Links in a Folder

Processing Subfolders: To process files in a folder and its subfolders, you must use a recursive VBA function or FileSystemObject (FSO) script instead of the basic Dir loop.

Solution 2

Manually Break OLE Links via Edit Links

If the VBA macro fails or you only have a small batch of files to process, use the built-in Edit Links feature.

Streamline Workbooks with WPS Spreadsheet

Effortlessly Manage VBA and Links in WPS Office

WPS Spreadsheet fully supports VBA macros and provides intuitive tools to manage workbook links, making it easy to clean up OLE errors across multiple files simultaneously.

1. Open WPS Spreadsheet: Download, install, and launch WPS Office, then open a blank Spreadsheet document.
2. Access the Macro Editor: Navigate to the Tools tab and click 'Macro', or press Alt + F11 to open the built-in VBA Editor.
3. Insert the Script: Insert a new Module and paste your folder-looping VBA script for breaking OLE links.
4. Run and Automate: Run the macro to automatically clear broken OLE links in your specified directory.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xls)Supports advanced VBA macros and scripting for automationLightweight and fast for processing multiple filesIntuitive Data tab for manual external link management

microsoft office alternative - wps office

Frequently Asked Questions

Why do OLE links break in Excel?

OLE (Object Linking and Embedding) links usually break when the source file is moved, renamed, or deleted, or when the source application (such as an outdated accounting software) is uninstalled or updated to a new system.

Can I reverse the 'Break Link' action?

No, breaking a link permanently converts the linked data or objects into static values or pictures. You cannot undo this action after saving the file, which is why creating a backup beforehand is crucial.

Will the basic VBA macro work on files in subfolders?

A standard VBA macro using the `Dir` command will only process files in the top-level folder. To process subfolders, the script must be modified to use the FileSystemObject (FSO) along with a recursive function.

How do I find hidden OLE links that don't appear in the Edit Links dialog?

Hidden OLE links might exist in named ranges, data validation rules, or conditional formatting. You can use the Name Manager (Ctrl + F3) to look for external references, or use a specialized VBA script to hunt them down.

Excel & Data Problems