mass removal of excel macros from files

Question

I'm working a process to cleanup macro enabled files across our network. I'm using PowerShell with the excel and word comObjects and have been moving in the right direction with exception to vb project enabled files. The code currently is working for all standard macro enabled files, but a message prompt is occurring during the .saveas method on files with vb projects (excel only so far). The objective is to successfully run this against several thousand files and remove the macro enablement on them for record purposes. i.e. keep the data not the code.

This is the function (using PowerShell) I'm using currently

function Process-ExcelFile {
    param (
        [string]$FullPath,
        [string]$DestinationFile
    )

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = 0 # Disable display alerts 
    #$excel.DisplayAlerts = $false # Disable display alerts works the same as 0
    $excel.AlertBeforeOverwriting = 0
    $workbook = $excel.Workbooks.Open($FullPath)
    # Save the file as non-macro enabled workbook with appropriate extension
    $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
    $workbook.SaveAs($destinationFile, 51) # 51 = Excel File Format for non-macro workbook
    $workbook.Close($false) # Close the workbook without saving changes

    $excel.Quit()
}

I've attempted to use methods of removing the VB projects with limited success, but the saveas dialog related to these files is not suppressed. The dialog is a result of the saveAs method, which prevents me from coding a response to the dialog, since the current process is waiting for completion of the saveas.

The objective is to retain as much of the original file as possible, but to remove all of the VB/macro content from the file. Manually, this is achived by saving the file as a non macro enabled file (xslx). We have considered transferring the content of the file, but this is not desired since the relationship of the original file is lost in this approach, where as the saveas retains the details desired.

Looking for any ideas that might help to work around this.

Answer 1

I want to thank @Doofus for their responses to my question. It prompted me to look at something, which lead to an answer.

The vb project files turned out to have event triggers, including an onsave event. This was part of the notifications that were causing my issue. I made the following changes in the code which have resolved this for now.

For the application, I set .EnableEvents to False.
For the workbook, I iterate through the .VBComponents and remove all type 1 modules.
Those 2 actions resulted in the suppression and successful processing of these files.

Updated function below.

function Process-ExcelFile {
    param (
        [string]$FullPath,
        [string]$DestinationFile
    )
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = 0 # Disable display alerts (e.g., VBA project prompt)
    $excel.EnableEvents = $false # Disable Event hooks in Excel
    $excel.AlertBeforeOverwriting = 0
    $workbook = $excel.Workbooks.Open($FullPath)
    # Check if the workbook has a VB project
    if ($workbook.HasVBProject) {
        # Save the workbook without macros
        $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
        # Access the VBA project
        $vbaProject = $workbook.VBProject
        # Remove the BuildFileName property by setting it to an empty string
        $vbaProject.BuildFileName = ""
        foreach ($module in $vbaProject.VBComponents) {
            if ($module.Type -eq 1) { # Check if it's a standard module (Type 1)
                $vbaProject.VBComponents.Remove($module)
            }
        }
        $workbook.SaveAs($destinationFile, 51) # 51 = Excel File Format for non-macro workbook
    }else {
        # Save the workbook as is (without macros)
        $destinationFile = [System.IO.Path]::ChangeExtension($destinationFile, "xlsx")
        $workbook.SaveAs($destinationFile, 51) # 51 = Excel File Format for non-macro workbook
    }
    # Close the workbook and quit Excel
    $workbook.Close($false)
    $excel.Quit()
}