If you are a Microsoft tenant administrator, you may want to know if there are any macro-enabled Excel files (xlsm) stored in your SharePoint sites. Macros are small programs that can run inside Excel and automate tasks, but they can also be used for malicious purposes, such as installing malware or stealing data. Therefore, it is important to monitor and audit the use of macros in your organization.

In this blog, I will show you how to use a PowerShell script to search for all xlsm files in your SharePoint sites and export the results to a CSV file. This script uses the PnP PowerShell module, which is a set of cmdlets that simplify SharePoint administration and development. You can install the PnP PowerShell module from here: https://docs.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps

The script has the following steps:

• Connect to your SharePoint tenant using the Connect-PnPOnline cmdlet with the -Interactive parameter. This will prompt you to sign in with your credentials and grant permissions to the PnP PowerShell app.
• Define a query string that filters the results by the file extension xlsm. You can modify this query to include other criteria, such as file name, site name, or date range. For more information on how to use the SharePoint search query syntax, see here: https://docs.microsoft.com/en-us/sharepoint/dev/general-development/keyword-query-language-kql-syntax-reference
• Submit the query using the Submit-PnPSearchQuery cmdlet with the -SelectProperties parameter. This parameter specifies which properties of the search results you want to retrieve, such as title, path, author, size, or modified date. The -All parameter ensures that all results are returned, not just the first 500.
• Loop through the results and create a custom object for each file with the properties you want to export. You can comment out or add any properties you need in the script.

• Append the custom object to an array that will store all the files.

• Export the array to a CSV file using the Export-Csv cmdlet with the -NoTypeInformation parameter. This parameter removes the type information from the CSV file, which is not needed.

The script is shown below:

$SiteURL = "https://yourtenant.sharepoint.com/"

Connect-PnPOnline -Url $SiteURL -Interactive

$itemsToSave = @()

$query = "FileExtension: xlsm"
$properties = "Title,Path,Author,Url,Size,Write,LastModifiedTime"

$search = Submit-PnPSearchQuery -Query $query -SelectProperties $properties -All

Write-Host -f Blue "Result : " $search.ResultRows.Count
foreach ($row in $search.ResultRows) {
$data = [PSCustomObject]@{
"FileName" = $row["Title"]#.Name
"Folder" = $row["Path"]#$FolderPath
"Author" = $row["Author"]#.createdby.user.displayname
"Created" = $row["Write"]#.createdDateTime
"Modified" = $row["LastModifiedTime"]#.lastModifiedDateTime
"Size" = $row["Size"]#$FileSize
"Uri" = $row["Url"]#.WebUrl
}

$itemsToSave += $data
}

$itemsToSave | Export-Csv -Path "C:\Temp\PnPScript\SearchResults-xlsm.csv" -NoTypeInformation
)

By running this script, you will get a CSV file with all the xlsm files in your SharePoint sites, along with their metadata. You can use this file to analyze and audit the macro usage in your organization and take appropriate actions if needed.

Additionally, the query can be modified to suit your needs - this lets you report on any property at will. 

I hope you found this blog post useful and informative. If you have any questions or feedback, please leave a comment below. Thank you for reading!