Excel, PowerPoint, Word ファイルをオープンしてはクローズするだけの PowerShell スクリプト

動機

大量の Excel, PowerPoint, Word ファイルに破損したものがあるかどうかをしらべたい。

概略

指定したフォルダーの配下の Excel, PowerPoint, Word ファイルをひたすらオープンしてはクローズするだけの PowerShell スクリプトです。オープンの成否を記録したカスタム オブジェクトを出力します。

コード

Test-MSOfficeFile.ps1

param(
    [Parameter(Mandatory=$true)]
    [String[]]$Path
)

Set-StrictMode -Version Latest

$excel = New-Object -ComObject Excel.Application
$powerpnt = New-Object -ComObject PowerPoint.Application
$word = New-Object -ComObject Word.Application

$excel.DisplayAlerts = $false
$word.DisplayAlerts = [Microsoft.Office.Interop.Word.WdAlertLevel]::wdAlertsNone

try {
    Get-ChildItem -Path (Join-Path -Path $Path -ChildPath '*') -Include *.xls*, *.xlt*, *.pot*, *.pps*, *.ppt*, *.doc*, *.dot* -Recurse |
        Where-Object -Property PSIsContainer -EQ -Value $false |
        Select-Object -ExpandProperty FullName |
        ForEach-Object -Process {
            $fileName = $_

            $outputObject = [PSCustomObject]@{
                FileName = $fileName
                Open = ''
                Close = ''
                Message = ''
            }

            # https://social.msdn.microsoft.com/Forums/ja-JP/5deec897-a897-404b-a610-f7d894fde1b3/office?forum=officesupportteamja
            $books = $book = $presns = $presn = $docs = $doc = $null

            try {
                switch -Regex ($fileName) {
                    '\.xl[ast][bmx]?$' {
                        $books = $excel.Workbooks
                        # https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
                        $book = $books.Open($fileName, $false, $true, [System.Reflection.Missing]::Value, '')
                        $outputObject.Open = 'Success'
                    }
                    '\.p(ot|p[st])[mx]?$' {
                        $presns = $powerpnt.Presentations
                        # https://docs.microsoft.com/en-us/office/vba/api/powerpoint.presentations.open2007
                        $presn = $presns.Open2007($fileName + '::*', [Microsoft.Office.Core.MsoTriState]::msoTrue, [System.Reflection.Missing]::Value, [Microsoft.Office.Core.MsoTriState]::msoFalse, [Microsoft.Office.Core.MsoTriState]::msoFalse)
                        $outputObject.Open = 'Success'
                    }
                    '\.do[ct][mx]?$' {
                        $docs = $word.Documents
                        # https://docs.microsoft.com/en-us/office/vba/api/word.documents.opennorepairdialog
                        $doc = $docs.OpenNoRepairDialog($fileName, $false, $true, $false, '*')
                        $outputObject.Open = 'Success'
                    }
                }
            } catch [System.Runtime.InteropServices.COMException] {
                $outputObject.Open = 'Failed'
                $outputObject.Message = $_.Exception.Message
            }

            try {
                if ($book) {
                    # https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.close
                    $book.Close($false)
                    $outputObject.Close = 'Success'
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book) | Out-Null
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($books) | Out-Null
                }
                if ($presn) {
                    # https://docs.microsoft.com/en-us/office/vba/api/powerpoint.presentation.close
                    $presn.Saved = $true
                    $presn.Close()
                    $outputObject.Close = 'Success'
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($presn) | Out-Null
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($presns) | Out-Null
                }
                if ($doc) {
                    # https://docs.microsoft.com/en-us/office/vba/api/word.document.close(method)
                    $doc.Close([Microsoft.Office.Interop.Word.WdSaveOptions]::wdDoNotSaveChanges)
                    $outputObject.Close = 'Success'
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($doc) | Out-Null
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($docs) | Out-Null
                }
            } catch [System.Runtime.InteropServices.COMException] {
                $outputObject.Close = 'Failed'
                $outputObject.Message = $_.Exception.Message
            }

            $books = $book = $presns = $presn = $docs = $doc = $null
            Remove-Variable -Name books, book, presns, presn, docs, doc -ErrorAction SilentlyContinue
            [System.GC]::Collect()
            [System.GC]::WaitForPendingFinalizers()
            [System.GC]::Collect()

            Write-Output -InputObject $outputObject
        } | Out-GridView -PassThru
} finally {
    $books = $book = $presns = $presn = $docs = $doc = $null
    Remove-Variable -Name books, book, presns, presn, docs, doc -ErrorAction SilentlyContinue
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    [System.GC]::Collect()
    $excel.Quit()
    $powerpnt.Quit()
    $word.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($powerpnt) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($word) | Out-Null
    $excel, $powerpnt, $word = $null
    Remove-Variable -Name excel, powerpnt, word -ErrorAction SilentlyContinue
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    [System.GC]::Collect()
}

(2021/10/7 よくわかっていませんが、「.NETを使った別プロセスのOfficeの自動化が面倒なはずがない―そう考えていた時期が俺にもありました。」を参考にさせていただき、 COM オブジェクトを解放するコードを追加しました。)