Finding Every Macro-Enabled File in SharePoint (Before You Block Macros)

In my article on Office macro hardening for Essential Eight, the first step was building a register of who actually uses macros. Before you can decide which macros to keep, block or move to a trusted location, you need to know where they are. In most organisations, a lot of them live in SharePoint.

That sounds like a simple search. It isn’t quite. File extensions don’t tell the whole story, delegated permissions quietly hide sites from you, and a scan that fails silently can look exactly like good news.

I learnt that last one the hard way. My first scan across our tenant reported almost nothing, which looked encouraging until I realised every single site had failed to connect. [CONFIRM: the rough totals from your real scan, e.g. sites scanned and files found.]

This article covers two ways to find macro-enabled files in SharePoint and OneDrive, how to check whether they really contain macros, and how to build a scan you can trust.

What counts as a macro-enabled file

The obvious starting point is the file extension. These Office formats can contain VBA macros:

Three traps are worth knowing before you start:

• .xlsb can hold macros without an “m” in its name. Excel’s binary workbook format supports VBA, so a search that only looks for extensions ending in “m” will miss it.
• Legacy formats hide macros. A .xls or .doc file might contain VBA or might not. The extension tells you nothing, and in most organisations there are far more of these than modern macro-enabled files.
• A macro-enabled extension doesn’t prove a macro exists. Plenty of .xlsm files were saved in that format once and never contained any code, or had the macro removed later.

So think of the extension search as a first pass that tells you where to look. You’ll check the actual contents later.

Option 1: a Purview content search

The quickest way to get a tenant-wide picture is a content search in Microsoft Purview. It searches the index across SharePoint and OneDrive without you writing a script, and it copes with thousands of sites.

Create a search in the Purview portal under eDiscovery, add all SharePoint sites and OneDrive accounts as locations, and search on the file extension property. SharePoint and OneDrive documents expose searchable properties such as FileName, FileExtension and LastModifiedTime (Microsoft Learn: Finding content in sites in eDiscovery). A query like this covers the modern macro-enabled formats:

FileExtension:xlsm OR FileExtension:xltm OR FileExtension:xlam OR FileExtension:xlsb OR FileExtension:docm OR FileExtension:dotm OR FileExtension:pptm OR FileExtension:potm OR FileExtension:ppsm OR FileExtension:ppam

Run it, review the estimate, and export the results report. That gives you each file’s location and last modified date.

It’s a great first pass, but it has limits:

• You need the right role. Content search needs an eDiscovery role, so you may have to ask whoever manages Purview in your organisation.
• It relies on the search index. Anything that hasn’t been indexed won’t appear.
• It only matches extensions. It can’t tell you whether a .xls file contains VBA, or whether a .xlsm file actually has a macro.
• Treat the export as sensitive. A report listing file paths across the whole tenant reveals a lot about the organisation. Store it accordingly.

For many organisations, that’s enough to see where the macro-enabled files are concentrated. When you need more detail, or want something you can re-run on a schedule, a script gives you more control.

Option 2: a PnP PowerShell scan

A PnP PowerShell script walks every site and document library directly, so it doesn’t depend on the search index, and you can record whatever metadata you need.

Use app-only authentication

This is the first trap. If you run the scan as yourself, you’ll only see sites you have access to. Even a SharePoint administrator isn’t automatically a member of every site, so a delegated scan can quietly skip large parts of the tenant and never tell you.

The reliable approach is an Entra app registration with the Sites.Read.All application permission and a certificate, used for app-only access. Current versions of PnP PowerShell also require your own app registration for sign-in, because the shared multi-tenant app was retired (PnP PowerShell: Register an Entra ID application).

The scan

This is a simplified version of the approach:

$tenant   = "contoso"
$clientId = "<app registration client ID>"
$thumb    = "<certificate thumbprint>"
$connect  = @{ ClientId = $clientId; Thumbprint = $thumb; Tenant = "$tenant.onmicrosoft.com" }

$macroExt = 'xlsm','xltm','xlam','xlsb','docm','dotm','pptm','potm','ppsm','ppam'

Connect-PnPOnline -Url "https://$tenant-admin.sharepoint.com" @connect -ErrorAction Stop
$sites = Get-PnPTenantSite -ErrorAction Stop

$results = [System.Collections.Generic.List[object]]::new()
$failed  = [System.Collections.Generic.List[string]]::new()

foreach ($site in $sites) {
    try {
        Connect-PnPOnline -Url $site.Url @connect -ErrorAction Stop
        $libraries = Get-PnPList -ErrorAction Stop |
            Where-Object { $_.BaseTemplate -eq 101 -and -not $_.Hidden }

        foreach ($library in $libraries) {
            Get-PnPListItem -List $library -PageSize 2000 -Fields FileLeafRef, FileRef, Modified, Editor -ErrorAction Stop |
                Where-Object { $_.FileSystemObjectType -eq 'File' } |
                ForEach-Object {
                    $ext = [IO.Path]::GetExtension($_['FileLeafRef']).TrimStart('.').ToLower()
                    if ($macroExt -contains $ext) {
                        $results.Add([pscustomobject]@{
                            Site       = $site.Url
                            Path       = $_['FileRef']
                            Extension  = $ext
                            Modified   = $_['Modified']
                            ModifiedBy = $_['Editor'].Email
                        })
                    }
                }
        }
    }
    catch {
        $failed.Add("$($site.Url): $($_.Exception.Message)")
    }
}

It connects to each site, finds visible document libraries (base template 101), and records any file with a macro-enabled extension, along with who last modified it and when. The “modified by” column is useful later, because it often points to the person who actually owns the macro.

One detail to watch: if a site has a retention policy, its Preservation Hold Library can contain copies of the same files. Decide whether to include or exclude it so you don’t double-count.

As written, though, this script has a serious weakness. It catches errors and carries on, which is exactly what bit me.

The mistake that failed 1,710 sites silently

My first run finished quickly and reported very few macro-enabled files. For about a minute, that looked like good news.

Then I looked at the error list. Every one of the 1,710 sites had failed to connect. I’d got the tenant domain wrong, so every Connect-PnPOnline call failed, the catch block dutifully recorded it, and the loop moved on to the next site. The script ran start to finish without stopping, and a scan of nothing looked almost identical to a clean tenant. [CONFIRM: exactly what was wrong with the domain, e.g. a typo or the wrong tenant name, and what the first run reported.]

Catching errors per site is the right idea, because one inaccessible site shouldn’t stop a scan of thousands. The mistake was having no check on whether the scan as a whole was working. The fix is fail-fast logic: stop immediately when something is systemically wrong, and only keep going past individual failures.

Three changes make the scan trustworthy.

1. Prove the connection before the loop. Connect to one known site and read something from it before scanning the rest. If that fails, stop:

Connect-PnPOnline -Url "https://$tenant.sharepoint.com" @connect -ErrorAction Stop
$null = Get-PnPWeb -ErrorAction Stop   # throws immediately if the connection isn't real

2. Stop on consecutive failures. One failed site is normal. Ten failures in a row almost always means a configuration problem, such as the wrong tenant, an expired certificate or a missing permission:

$consecutive = 0
# inside the loop, after a successful site:
$consecutive = 0
# inside the catch block:
$consecutive++
if ($consecutive -ge 10) {
    throw "10 consecutive sites failed. Last error: $($_.Exception.Message)"
}

3. Always report the totals. At the end, print how many sites succeeded and how many failed, and save the failures to a file:

$ok = $sites.Count - $failed.Count
Write-Host "Sites scanned successfully: $ok of $($sites.Count)"
Write-Host "Macro-enabled files found:   $($results.Count)"
$failed  | Set-Content .\failed-sites.txt
$results | Export-Csv .\macro-files.csv -NoTypeInformation

The general lesson reaches well beyond SharePoint: in an inventory scan, zero results is a finding that needs proving, not a relief. If the numbers look too good, check that the scan actually looked.

Checking whether files actually contain macros

An extension match tells you a file could contain macros. For the register, you want to know which ones actually do. You don’t need to download everything to find out. Start with the files that matter most, such as recently modified ones or ones in business-critical sites.

Modern formats (.xlsm, .docm, .xlsb and the rest) are zip containers. When a file contains VBA, the code is stored in a part called vbaProject.bin. You can check for it with PowerShell, without opening the file in Office:

Add-Type -AssemblyName System.IO.Compression.FileSystem

function Test-HasVbaProject {
    param([string]$Path)
    $zip = [IO.Compression.ZipFile]::OpenRead($Path)
    try {
        [bool]($zip.Entries | Where-Object Name -eq 'vbaProject.bin')
    }
    finally {
        $zip.Dispose()
    }
}

Test-HasVbaProject -Path 'C:\Temp\Budget.xlsm'

If it returns False, the file uses a macro-enabled format but has no VBA project, so it probably doesn’t belong in the register.

Legacy formats (.xls, .doc, .ppt) aren’t zip files, so this check doesn’t work. For those, olevba from the open-source oletools project can detect and extract VBA from both legacy and modern Office files, and it flags suspicious keywords too (oletools on GitHub). It’s handy for reviewing what a macro actually does before you decide whether to trust it.

One caution: only analyse copies in a controlled location, and never open unknown macro files in Office just to see what they do. Some of what you find may not be as harmless as it looks.

Don’t forget file shares

SharePoint is only part of the picture. Legacy macros are often on file servers, especially old templates and add-ins. A recursive search covers those:

$macroExt = '.xlsm','.xltm','.xlam','.xlsb','.docm','.dotm','.pptm','.potm','.ppsm','.ppam'

Get-ChildItem -Path '\\fileserver.example.local\Shares' -Recurse -File -ErrorAction SilentlyContinue -ErrorVariable scanErrors |
    Where-Object { $macroExt -contains $_.Extension.ToLower() } |
    Select-Object FullName, Extension, LastWriteTime, Length |
    Export-Csv .\fileshare-macro-files.csv -NoTypeInformation

"Folders that couldn't be read: $($scanErrors.Count)"

The same fail-fast principle applies. -ErrorVariable collects the folders that couldn’t be read, such as ones your account has no access to, so you can see what the scan missed instead of assuming it found everything. Run it with an account that can read the whole share, and check that the error count is small before trusting the results.

Turning the results into a macro register

A spreadsheet with a few thousand file paths isn’t a register yet. It’s a list of places to ask questions. The next step is narrowing it down.

Sort by last modified date. A macro file nobody has touched in eight years is a very different conversation from one modified last week. Recently modified files are the ones most likely to be in active use, so start there.

Group by site and team. Macro files usually cluster in a small number of places, such as a finance site, an estimating share or a templates library. That tells you which teams to talk to first.

Use “modified by” as a lead. The person who last saved the file is often the person who knows what it does, or at least who to ask.

Check contents for the files that matter. Run the vbaProject.bin check or olevba on the shortlist to confirm which files really contain macros.

Then talk to the business. For each group of files, find out what the macro does, who uses it, who maintains it, and what happens if it stops working. That’s what turns a file list into the register the Essential Eight work depends on: the file and its location, the business owner, the users who need it, what it does, and where it will be allowed to run.

In my experience, the list shrinks dramatically once you do this. Most macro-enabled files turn out to be old copies, abandoned tools, or files that were never really macros at all. [CONFIRM: how much your own list shrank between the raw scan and the final register.]

Finally, treat this as a repeatable scan rather than a one-off. Macro-enabled files will keep appearing after you block macros, and running the scan periodically is a simple way to spot new ones before they become a support call.

Checklist: finding macro-enabled files

• Search for every macro-capable extension, including .xlsb and the legacy .xls, .doc and .ppt formats.
• Use a Purview content search for a quick tenant-wide first pass.
• For a detailed or repeatable scan, use PnP PowerShell with app-only authentication, so no sites are hidden from you.
• Prove the connection works before scanning, and stop on repeated failures.
• Report how many sites succeeded and failed, and treat zero results as something to prove.
• Decide whether to include Preservation Hold Libraries, so you don’t double-count.
• Scan file shares as well as SharePoint, and record folders the scan couldn’t read.
• Check which files actually contain macros using vbaProject.bin or olevba.
• Prioritise by last modified date and location, and use “modified by” to find owners.
• Turn the list into a register by talking to the business.
• Re-run the scan periodically to catch new macro files.

Finding every macro-enabled file isn’t the exciting part of an Essential Eight project, but everything else depends on it. You can’t decide which macros to trust until you know which ones exist, and you can’t trust that list until you’ve proved the scan actually looked.

Sources

• Microsoft Learn: Finding content in sites in eDiscovery
• PnP PowerShell: Register an Entra ID application
• oletools on GitHub
• Microsoft Learn: Macros from the internet are blocked by default in Office
• ASD: Essential Eight maturity model

Daniel Lutton

From my early days on the helpdesk through roles as a service desk manager, systems administrator, and network engineer, I’ve spent more than 25 years in the IT world. As I transition into cyber security, my goal is to make tech a little less confusing by sharing what I’ve learned and helping others wherever I can.