How to open an Excel Workbook with Macros Disabled in PowerShell-CodePudding

I'm trying to write a PowerShell script to scan a path for an Excel file (.xlsm) containing a specific string.

The script I'm working on currently opens the files with macros enabled and this could cause issues. There are a few Excel files that have scripts to run on open and I would like to prevent these from being executed.

Is there a way in Powershell to state that I want to open the file and not run macros?

Most of this script was initially taken from: https://shuaiber.medium.com/searching-through-excel-files-for-a-string-using-powershell-964db62348ef

Function Search-Excel {
    [cmdletbinding()]
    Param (
        [parameter(Mandatory, ValueFromPipeline)]
        [ValidateScript({
            Try {
                If (Test-Path -Path $_) {$True}
                Else {Throw "$($_) is not a valid path!"}
            }
            Catch {
                Throw $_
            }
        })]
        [string]$Source,
        [parameter(Mandatory)]
        [string]$SearchText,
        [bool]$ShowWarnings
        #You can specify wildcard characters (*, ?)
    )
    $Excel = New-Object -ComObject Excel.Application
    Try {
        $Source = Convert-Path $Source
    }
    Catch {
        Write-Warning "Unable locate full path of $($Source)"
        BREAK
    }
    Write-Host $Source
    $Workbook = $Excel.Workbooks.Open($Source)
    ForEach ($Worksheet in @($Workbook.Sheets)) {
        # Find Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel
        $Found = $WorkSheet.Cells.Find($SearchText) #What
        If ($Found) {
            # Address Method https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel
            $BeginAddress = $Found.Address(0,0,1,1)
            #Initial Found Cell
            [pscustomobject]@{
                WorkSheet = $Worksheet.Name
                Column = $Found.Column
                Row =$Found.Row
                Text = $Found.Text
                Address = $BeginAddress
            }
            Do {
                $Found = $WorkSheet.Cells.FindNext($Found)
                $Address = $Found.Address(0,0,1,1)
                If ($Address -eq $BeginAddress) {
                    BREAK
                }
                [pscustomobject]@{
                    WorkSheet = $Worksheet.Name
                    Column = $Found.Column
                    Row =$Found.Row
                    Text = $Found.Text
                    Address = $Address
                }                 
            } Until ($False)
        }
        Else {
            If ($ShowWarnings) {
                Write-Warning "[$($WorkSheet.Name)] Nothing Found!"
            }
        }
    }
    $workbook.close($false)
    [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel)
    [gc]::Collect()
    [gc]::WaitForPendingFinalizers()
    Remove-Variable excel -ErrorAction SilentlyContinue
}

$SearchText = Read-Host -Prompt 'What text do you want to search for in every excel file'

Get-ChildItem -Path "C:\JunkSaves" -Recurse -Include *.xls, *.xlsx, *.xlsm | Foreach-Object { Search-Excel -Source $_.FullName -SearchText $SearchText -ShowWarnings $false }
Read-Host -Prompt "Press Enter to continue"

CodePudding user response：

You can set this property on the Excel object you create before opening the file:

$Excel.AutomationSecurity = 3

Here value 3 is the 'msoAutomationSecurityForceDisable' value from the MsoAutomationSecurity Enum

The msoAutomationSecurityForceDisable should disable all macros in all files opened programmatically, without showing any security alerts, according to the docs.