Get macros related files in sharepoint Online

31 October 2024 arwayne

If you want to find the macros related file(.xlsm or .docm) in a specific document library use the below pnp script

Make sure the Sharepoint PnP module is installed

# Connect to SharePoint Online
connect-pnponline -Url https://YourDomain.sharepoint.com/sites/slptest03 -ClientId ad895741-ecd8-46de-b683-d2aa12271431 -Interactive

# Get all macro files (Excel and Word)
$macroFiles = Get-PnPListItem -List "Documents" -Fields "FileLeafRef", "FileRef" | Where-Object { 
    $_.FieldValues.FileLeafRef.EndsWith(".xlsm") -or $_.FieldValues.FileLeafRef.EndsWith(".docm") 
}

# Prepare the report data
$report = @()
foreach ($file in $macroFiles) {
    $report += [PSCustomObject]@{
        FileName = $file.FieldValues.FileLeafRef
        FilePath = $file.FieldValues.FileRef
    }
}

# Count the number of macro files
$macroFileCount = $report.Count

# Output the result
Write-Host "Number of macro files: $macroFileCount"

# Display file details
$report | Format-Table -AutoSize

# Optionally, export to CSV
$report | Export-Csv -Path "C:\temp\MacroFilesReport.csv" -NoTypeInformation

To get the Macros file from a specific site collection

# Connect to SharePoint Online
#relplace
Connect-PnPOnline -Url "https://domain.sharepoint.com/sites/site" -UseWebLogin

# Get all document libraries in the site
$lists = Get-PnPList | Where-Object { $_.BaseTemplate -eq 101 }  # 101 is the base template for document libraries

# Prepare the report data
$report = @()

# Iterate through each document library
foreach ($list in $lists) {
    # Get all macro files (Excel and Word) in the current library
    $macroFiles = Get-PnPListItem -List $list.Title -Fields "FileLeafRef", "FileRef" | Where-Object { 
        $_.FieldValues.FileLeafRef.EndsWith(".xlsm") -or $_.FieldValues.FileLeafRef.EndsWith(".docm") 
    }

    # Add file details to the report
    foreach ($file in $macroFiles) {
        $report += [PSCustomObject]@{
            FileName = $file.FieldValues.FileLeafRef
            FilePath = $file.FieldValues.FileRef
            LibraryName = $list.Title
        }
    }
}

# Count the number of macro files
$macroFileCount = $report.Count

# Output the result
Write-Host "Number of macro files: $macroFileCount"

# Display file details
$report | Format-Table -AutoSize

# Optionally, export to CSV
$report | Export-Csv -Path "MacroFilesReport_SiteLevel.csv" -NoTypeInformation

Output

In PowerShell output

In excel file

Related Posts