Skip to content
tenantcraft
( Insights)

Excel macros and links broken after a SharePoint migration

The files moved, the Quick Links are on the page, and every click fails. Migration tools copy files but leave the paths inside them pointing at the old server. Here is how to find every broken workbook link and macro path, fix them, and stop Office from blocking the macros.

Stage
Execute
Topic
Migration
Published
Written by

Tested in a live tenant,

(Short version)

Migration tools move files but don't rewrite the paths inside them. Scan the share for workbooks with external links or macros before cutover. Fix links with Workbook Links and Change source, move every VBA path into one constant, and add your SharePoint domain to the Trusted Sites zone so macros aren't blocked.

In this post

Applies to: SharePoint Online, Excel for Microsoft 365, Excel for the web

We’ve all seen it. The migration is done. The new site looks great, and the Quick Links are mapped on the home page. Then you click one, and it fails. You click the next one, and it fails too.

It’s like building a beautiful frontend with no backend behind it.

The same thing happens inside the files. The budget workbook opens fine from SharePoint. But the numbers it pulls from three other workbooks still point at \\fileserver01\Finance. The macro that builds the month-end report still opens S:\Operations\Headcount.xlsx. Nobody notices until someone presses the button.

There is a fix for that, and it’s mostly a list, a find-and-replace, and one policy setting.

The real cause: the paths came along, the server didn’t

The migration didn’t break your links. It copied them as they were.

A link is a line of text. It says “go to this server, this folder, this file.” A Quick Link, a formula that pulls from another workbook, and a line of VBA all store that text. A migration tool copies the file byte for byte, so the text inside stays the same.

Excel gives you one break here. It saves a workbook link relative to the workbook when it can. If the source sits in the same folder or a subfolder, the link keeps working as long as both files move together. Links that reach into another folder tree, another drive or another server carry more of the old path, and those are the ones that fail.

Microsoft is direct about this. In its file share migration guide, the table of what the SharePoint Migration Tool moves lists “Conversion of embedded URLs in content” in the Not migrated column.

So after cutover, every one of those links still points at the old file server. While the server is still up, things seem to work, which hides the problem. The day it’s switched off, they all break at once.

There are three places the old paths hide:

WhereWhat it looks likeHow you fix it
Page linksQuick Links, buttons and text links pointing at the old intranetEdit the page and change each link
Workbook linksA formula like ='\\fileserver01\Finance\[Budget 2024.xlsx]Sheet1'!B2Workbook Links pane, Change source
VBA codeWorkbooks.Open "S:\Operations\Headcount.xlsx"Edit the macro in the VBA editor

The steps below start with workbooks, because they’re the biggest pile, then macros, then pages.

Do this before cutover, while the share is still the source of truth.

You can’t open thousands of workbooks one at a time. Microsoft also notes in its Workbook Links help that “There is no automatic way to find all workbook links in a workbook.” So scan the files from the outside instead.

A modern Excel file (.xlsx, .xlsm, .xlsb, plus templates and add-ins) is a zip archive. Every link that points outside the file, whether it’s a link to another workbook or a hyperlink, is recorded in a small XML part inside it, with the target written in plain text. Macros live in a part called vbaProject.bin. This PowerShell reads both without opening Excel:

Add-Type -AssemblyName System.IO.Compression, System.IO.Compression.FileSystem
$root = '\\fileserver01\Finance'

Get-ChildItem $root -Recurse -File -Include *.xlsx, *.xlsm, *.xlsb, *.xltx, *.xltm, *.xlam |
ForEach-Object {
    $file = $_.FullName
    $zip  = $null
    try {
        $zip = [IO.Compression.ZipFile]::OpenRead($file)
        $hasMacros = [bool]($zip.Entries | Where-Object { $_.Name -eq 'vbaProject.bin' -or $_.FullName -like 'xl/macrosheets/*' })
        $links = foreach ($entry in $zip.Entries | Where-Object FullName -like '*.rels') {
            $reader = New-Object IO.StreamReader($entry.Open())
            try {
                ([xml]$reader.ReadToEnd()).Relationships.Relationship |
                    Where-Object TargetMode -eq 'External' | ForEach-Object Target
            }
            finally { $reader.Dispose() }
        }
        if ($hasMacros -or $links) {
            [pscustomobject]@{ File = $file; Macros = $hasMacros; Links = (($links | Select-Object -Unique) -join '; ') }
        }
    }
    catch {
        [pscustomobject]@{ File = $file; Macros = '?'; Links = 'Could not open: ' + $_.Exception.Message }
    }
    finally {
        if ($zip) { $zip.Dispose() }
    }
} | Export-Csv excel-links.csv -NoTypeInformation -Encoding UTF8

Change $root to your share and run it. The script only reads, and it closes every file even when one fails, so it won’t leave workbooks locked for the people using them. The CSV has one row per file that needs attention, with every outside target in the Links column. Spaces show up as %20, and web hyperlinks and mailto: addresses appear too.

If you run it in PowerShell 7, change the last line to -Encoding utf8BOM so Excel opens the CSV with accented file names intact. PowerShell 7 also copes better with very long paths on a file share.

The scan misses some things:

  • Old .xls files. They aren’t zip archives. Open and save them as .xlsx or .xlsm first, or list them separately and check by hand.
  • Paths inside the VBA code. The scan tells you a file has macros. It can’t read the code, because Excel stores it compressed in a binary part. Step 3 covers that.
  • Power Query and data connections. Their sources aren’t stored as plain links. Check them in the Excel desktop app.
  • Encrypted files, and files open on someone’s PC. They show up in the “Could not open” rows. Ask the owner, or run the scan again after hours.

Read the Links column for a server name or a drive letter, such as \\fileserver01 or S:. Those are the links that break. A target with only a file name is relative and often survives the move. You’ll usually find a handful of source workbooks that dozens of others point at. Those are the ones to move first.

Once the source files are in SharePoint, open each linking workbook in the Excel desktop app.

  1. On the Data tab, in the Queries & Connections group, select Workbook Links. The pane lists every workbook this file pulls from.
  2. Next to a broken link, select More Commands (…), then Change source.
  3. Browse to the file’s new location in SharePoint and select it.
  4. Save the workbook.

I generated a test workbook with a script, gave it two links, one to \\fileserver01\Finance and one to S:\Operations, and uploaded it to my own tenant. Excel for the web found both links and flagged each one the same way.

Excel for the web > Data > Workbook Links

A workbook uploaded to SharePoint with two links still pointing at the old file server. Excel can't reach either source, so each one is flagged File not in the cloud.

Workbook Links > Link options

The menu next to each link. Change source points the formulas at the file's new home and keeps them live. Break links turns them into fixed numbers.

If you’ve used Excel for a while, you might look for Edit Links. Microsoft replaced it with Workbook Links, and the old command can’t be added back to that group.

Excel for the web has the same pane, and the menu next to each link is Link options (…). The web version can also suggest the renamed file for you. Microsoft notes the Suggested feature is only in the web version.

Microsoft’s help page carries two warnings worth repeating.

  • Break links turns the formulas into fixed values. On the desktop, “this action cannot be undone”. Use Change source unless you really want frozen numbers.
  • Excel for the web “does not support refreshing links from a different domain, including cross-geography and cross-tenant scenarios.” If the source file lives in another tenant, refresh it in the desktop app.

Fix the source workbooks before the ones that point at them. Otherwise you’ll repoint a link at a file that’s about to move again.

Step 3: Fix the paths inside your macros

This is the part link-checking tools struggle with. A path in VBA isn’t a link Excel knows about. It’s a string, and it can be built in pieces:

folder = "S:\Operations\"
Workbooks.Open folder & "Headcount.xlsx"

A find-and-replace won’t catch every one of those, so someone has to read the code.

For each macro file from your CSV:

  1. Open it in the Excel desktop app and press Alt+F11 to open the VBA editor.
  2. Press Ctrl+F and search the Current Project for :\ and then for \\. That finds drive letters and server paths.
  3. Move every path into one constant at the top of a module.
  4. Point that constant at the new location.

Before:

Sub BuildReport()
    Workbooks.Open "\\fileserver01\Finance\Budget 2024.xlsx"
    Workbooks.Open "S:\Operations\Headcount.xlsx"
End Sub

After:

' Every file location this project uses. Change them here, nowhere else.
Const FINANCE_ROOT As String = "https://contoso.sharepoint.com/sites/Finance/Shared Documents/"
Const OPS_ROOT As String = "https://contoso.sharepoint.com/sites/Operations/Shared Documents/"

Sub BuildReport()
    Workbooks.Open FINANCE_ROOT & "Budget 2024.xlsx"
    Workbooks.Open OPS_ROOT & "Headcount.xlsx"
End Sub

The constants are the real fix. The path will change again: a site gets renamed, a library gets split, a department moves. With the paths in constants, that’s a one-line edit instead of another hunt.

What you set the constants to is your call. You have two options:

Point it atGood forWatch out for
The SharePoint web addressWorks for anyone with access, no setup on the PCTest it with a real user account, not only an admin
A synced library folder on the PCCode that loops through folders and expects a local pathThe path includes the user’s name, so build it at run time; every user has to sync the same library

One trap applies to both. When a workbook is opened from SharePoint, or from a synced folder with AutoSave on, ThisWorkbook.Path can return a web address instead of a folder. Commands that expect a folder, such as Dir, Kill and MkDir, fail on it. Build paths from your constants, not from ThisWorkbook.Path.

Whichever you pick, test every macro with an ordinary user’s account before you switch the share to read-only.

Keep the path short too. In SharePoint, “The entire decoded file path, including the file name, can’t contain more than 400 characters.” Deep folder trees from a file server can hit that once the site address is added in front.

Step 4: Stop Office blocking the macros

Some macros fail after a move even with perfect paths. Office blocks VBA macros in files from the internet, and it decides that with a tag Windows adds to downloaded files, called Mark of the Web.

Microsoft’s page on this spells out when SharePoint files get the tag:

  • A file opened with Open in Desktop App from a SharePoint site or OneDrive won’t have Mark of the Web.
  • A file the OneDrive sync app downloads won’t have it either.
  • A file someone downloads through the browser might, depending on how the Windows internet security zones are set.

So the users who download a copy and open it are the ones who see the Security Risk banner saying macros are blocked.

Microsoft’s fix is a policy. Use the Site to Zone Assignment List policy to add https://{your-domain-name}.sharepoint.com for SharePoint and https://{your-domain-name}-my.sharepoint.com for OneDrive to the Trusted Sites zone. Then files downloaded from your tenant aren’t treated as internet files.

Know what you’re trusting before you do it. Microsoft’s page warns that “Anyone with permissions to add files to SharePoint could add files with active content, such as macros.” Tighten who can upload to the libraries that hold macro files.

Don’t make the old network share a Trusted Location to keep things running. Microsoft’s page is clear on this: “Network locations can also be set as a Trusted Location, but it’s not recommended.”

One limit stays no matter what policy you set: Excel for the web doesn’t run macros. In Microsoft’s words: “Although you can’t create, run, or edit VBA (Visual Basic for Applications) macros in Excel for the web, you can open and edit a workbook that contains macros.” Anyone who opens a macro workbook in the browser has to switch to the desktop app to run it.

Now the Quick Links from the opening. They break for the same reason: each one stores a full web address, and the migration didn’t touch it.

I tested this in my own tenant. Quick Links won’t take a file share path at all. Paste a file:// address and the picker says “Enter a link from a website (https:// or http://) or email address (mailto:)”. So the Quick Links that fail after a migration point at old web addresses, usually the on-premises intranet the content came from.

To fix one:

  1. Open the page and select Edit.
  2. Select the Quick Links web part, then select the link you want to fix.
  3. In the Quick links pane, under Link, check the address. If it points at the old intranet, select Change.
  4. Pick the file’s new home in SharePoint, or paste its new address, and confirm.
  5. Repeat for each link, then republish the page.

Page edit > Quick links pane

The pane shows the exact address each Quick Link stores. This one still points at the old on-premises intranet, so it fails for everyone the day that server goes away.

Then sign in as a normal user and click every link, because an admin account can hide access problems. I’d add a line to the cutover checklist too: no page goes live until every link on it has been clicked once.

Mapping a drive to buy time

The tempting shortcut is to map SharePoint as a drive letter, so S:\ still works and nothing needs fixing.

Microsoft’s own guidance points the other way. Its help page on opening SharePoint files in File Explorer says “we no longer recommend the View in File Explorer command,” which is the same connection most drive mappings to SharePoint rely on. The page doesn’t cover mapped drive letters directly. It recommends syncing the library or Add shortcut to My files in OneDrive instead, and calls the shortcut “the most versatile option when available.”

A synced library can buy you time. It gives each user a local folder, so code that expects a folder path keeps working once you point it there. It won’t hold up for long, though: every user needs the same library synced, and the path still changes. Use the time to put the paths into constants.

What this does not fix

  • Links in Word, PowerPoint and Access files. They break for the same reason, and the Workbook Links pane can’t see them. They need their own pass.
  • Bookmarks, emails and shortcuts. Links people saved outside the files point at the old server too. The only fix is telling people, and a good redirect page.
  • Macros that talk to the server in other ways. Code that reads folder lists over the network or calls other systems may need a rewrite, not a new path. Test those first; they take the longest.
  • Repairing links automatically. Microsoft documents a repair for one narrow case: ImportRange links in workbooks migrated from Google Workspace. It works only in Excel for the web, and only if the migration recorded each file’s original ID. Nothing like it exists for files that came from a network share.

The bottom line

The migration copied your links exactly, including the server they point at. Scan the share for workbooks with links and macros before cutover, fix the links with Change source, and move every macro path into one constant. Then add your SharePoint domain to the Trusted Sites zone and click every link on every page as a normal user before you switch the old share off.

(Paired checklist, 7 pages)

SharePoint Discovery & Inventory Worksheet

You get one email with the link. That's the only email.

Read it on the site
  • One fill-in record per site collection: size, owners, customizations, decision
  • Where every field comes from: PowerShell, Central Admin, and the SPMT scan
  • A four-way call on every site: migrate, consolidate, archive, or retire
  • An exceptions log for the lists, forms, and workflows that can't move as-is
Sneak peek

Document preview

100%

Loading the document…