Excel の複数ファイル一括検索・置換

# 目的
指定したフォルダの中にある複数の Excel ファイルに対して文字列の一括検索[^1]や一括置換をおこないます。

[^1]: 拡張子 .xlsx, .xlsm の Excel ファイルについては、[こちら](https://qiita.com/earthdiver1/items/5eecd7016bb2ee24c507)の複数行検索版の grep もどきツールでも複数ファイルの一括検索が可能です。

# 使用方法
最も簡単なのは、下記のスクリプトをバッチファイル（拡張子 .bat）として保存して、その上に Excel ファイルが含まれるフォルダ（１つのみ）をドラッグ＆ドロップする方法です。この場合、検索文字列や置換文字列はスクリプトファイルの中に直書きして使用します。
PowerShellコンソール上で使用する場合は、スクリプト中で定義されている ExcelFind, ExcelReplace の各関数を直接利用する方が使い勝手がよいと思います。

# スクリプト
PowerShellスクリプトですが、Windows バッチファイルとしても実行できる特殊な構造になっています。
バッチファイルとして実行する場合は拡張子を .bat として、PowerShellスクリプトとして実行する場合は拡張子を .ps1 として保存してください。

■ 一括検索

``` powershell:excel_find.bat
<# : バッチコマンド(PowerShellコメント)開始
@echo off & setlocal
rem
rem
rem    excel_find.bat  対象フォルダ名  [検索文字列]  [表示する列の番号のリスト※]
rem                                                ※ 1 から始まる整数（カンマ区切り）
rem                                                  例：A列とC列の場合は 1,3。
rem
rem
rem    指定したフォルダ（ドラッグ&ドロップ可）の中にある複数のExcelファイルに対して
rem    文字列検索を行ないます。
rem
rem
rem    Creadted by earthdiver1
rem
rem    クリエイティブ・コモンズ 表示 - 継承 4.0 国際 ライセンスの下に提供されています。
rem
rem -------------------------------------------------------------------------------
rem 以下はPowershellスクリプトをバッチファイルの中に埋め込むためのプリアンブルです。
set BATCH_ARGS=%*
if defined BATCH_ARGS set BATCH_ARGS=%BATCH_ARGS:"=\"%
if defined BATCH_ARGS set BATCH_ARGS=%BATCH_ARGS:^^=^% 
endlocal & Powershell -NoP -C "&([ScriptBlock]::Create((gc '%~f0'|Out-String)))" %BATCH_ARGS%
exit/b
rem -------------------------------------------------------------------------------
: バッチコマンド(PowerShellコメント)終了 #>
param([String]$Dir,
#++++++++++++++ EDIT HERE +++++++++++++++
      [String]$String = "検索文字列",
      [Int[]]$Columns = @()
#----------------------------------------
)

Function ExcelFind {
    [CmdletBinding(PositionalBinding=$False)]
    param([parameter(Position=0)][String]$Dir,
          [parameter(Position=1)][String]$String,
          [Alias("s")][String]$sheet = "*",   # シートの名前。全シートを対象とする場合は "*"
          [Alias("r")][String]$range = "*",   # セルの範囲（"A1:E5"など）。全セルを対象とする場合は "*"
          [ValidateSet("C","F","V")]
          [Alias("i")][String]$LookIn = "F",  # 検索対象  C:コメント F:数式 V:値
          [Alias("a")][Int]$LookAt    = 2,    # セル内容が完全に同一であるものを検索  1:する 2:しない
          [Alias("c")][Int]$MatchCase = 0,    # 大文字と小文字の区別  0:しない 1:する
          [Alias("b")][Int]$MatchByte = 0,    # 半角と全角の区別      0:しない 1:する
          [ValidateRange(1,256)]
          [Int[]]$Columns = @()               # 表示する列の番号のリスト（カンマ区切り）
    )

    if (-not $Dir) { 
        Write-Host "対象フォルダが指定されていません。処理を終了します。" -Fore Red
        return
    } 

    if (-not (Test-Path -LiteralPath $Dir -PathType Container)) { 
        Write-Host "対象フォルダがありません。処理を終了します。" -Fore Red
        return
    }

    Add-Type -AssemblyName Microsoft.Office.Interop.Excel

    Write-Host "処理を開始します。"

    try {
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $False
        $excel.DisplayAlerts = $false
        $default = [Type]::Missing
        $xlLookIn = @{ "C" = [Microsoft.Office.Interop.Excel.XlFindLookIn]::xlComments
                       "F" = [Microsoft.Office.Interop.Excel.XlFindLookIn]::xlFormulas
                       "V" = [Microsoft.Office.Interop.Excel.XlFindLookIn]::xlValues   }
        $num_files = 0
        $num_found = 0

        Get-ChildItem -LiteralPath $Dir -Filter "*.xls*" -Recurse | %{
            $first = $true
            $filename = $_.FullName
            $workbook = $excel.Workbooks.Open($filename)
            $workbook.Sheets | %{
                $worksheet = $_
                if ($sheet -ne "*" -and $sheet -ne $worksheet.Name) { return }
                $firstAddress = ""
                if ($range -eq "*") { 
                    $range1 = $worksheet.Cells
                } else {
                    $range1 = $worksheet.Range($range)
                }
                $result = $range1.Find($string,             # What
                                       $default,            # After
                                       $xlLookIn[$LookIn],  # LookIn
                                       $lookAt,             # LookAt
                                       $default,            # SearthOrder
                                       $default,            # SearchDirection
                                       $matchCase,          # MatchCase
                                       $matchByte,          # MatchByte
                                       $default             # SearchFormat
                                      )
                while ($result) {
                    $address = $result.Address(0,0)
                    if ($address -eq $firstAddress) { break }
                    if (-not $firstAddress) { $firstAddress = $address }
                    if ($first) { $num_files++; $first = $false }
                    $num_found++
                    Write-Output "--------------------------------------------------"
                    switch ($LookIn) {
                        "C" { Write-Output "$filename, $($worksheet.name)[$address]: '$($result.Comment)'" }
                        "F" { Write-Output "$filename, $($worksheet.name)[$address]: '$($result.Formula)'" }
                        "V" { Write-Output "$filename, $($worksheet.name)[$address]: '$($result.Text)'"    }
                    }
                    if ($Columns) {
                        $Columns | % {
                            $formula = $worksheet.Cells.Item($result.Row,$_).Formula
                            $text    = $worksheet.Cells.Item($result.Row,$_).Text
                            if ($formula -ne $text) {
                                Write-Output (("{0,5}行{1,3}列" -F $result.Row,$_) + " 式: '$Formula'")
                            }
                            Write-Output (("{0,5}行{1,3}列" -F $result.Row,$_) + " 値: '$text'")
                        }
                    }
                    $result = $range1.FindNext($result)
                }
            }
            $workbook.Close()
        }
        Write-Host "処理が終了しました。"
        Write-Host "$num_files ファイル, $num_found 件のマッチがありました。"
    } catch {
        Write-Host $Error[0].ToString() $Error[0].InvocationInfo.PositionMessage
    } finally {
        if ($excel) { $excel.Quit() }
        $result,$range1,$worksheet,$workbook,$excel | % { try{[Runtime.Interopservices.Marshal]::ReleaseComObject($_)}catch{}; $_ = $Null } | Out-Null
        [GC]::Collect()
    }
}

ExcelFind $Dir $String -Columns $Columns

Write-Host "終了するには何かキーを押してください . . ."
$Host.UI.RawUI.FlushInputBuffer()
$Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyUp") | Out-Null
```

■ 一括置換
結果のファイルは、指定したフォルダの名前の末尾に "_new" を付した名前の新規フォルダの中に出力されます。

``` powershell:excel_replace.bat
<# : バッチコマンド(PowerShellコメント)開始
@echo off & setlocal
rem
rem
rem    excel_replace.bat  対象フォルダ名  [検索文字列]  [置換文字列]
rem
rem
rem    指定したフォルダ（ドラッグ&ドロップ可）の中にある複数のExcelファイルに対して
rem    文字列置換を行ないます。結果のファイルは、元のフォルダ名の末尾に "_new" を
rem    付した新規フォルダの中に出力されます。
rem
rem
rem    Creadted by earthdiver1
rem
rem    クリエイティブ・コモンズ 表示 - 継承 4.0 国際 ライセンスの下に提供されています。
rem
rem -------------------------------------------------------------------------------
rem 以下はPowershellスクリプトをバッチファイルの中に埋め込むためのプリアンブルです。
set BATCH_ARGS=%*
if defined BATCH_ARGS set BATCH_ARGS=%BATCH_ARGS:"=\"%
if defined BATCH_ARGS set BATCH_ARGS=%BATCH_ARGS:^^=^% 
endlocal & Powershell -NoP -C "&([ScriptBlock]::Create((gc '%~f0'|Out-String)))" %BATCH_ARGS%
exit/b
rem -------------------------------------------------------------------------------
: バッチコマンド(PowerShellコメント)終了 #>
param([String]$Dir,
#++++++++++++++ EDIT HERE +++++++++++++++
      [String]$OldStr = "検索文字列",
      [String]$NewStr = "置換文字列"
#----------------------------------------
)

Function ExcelReplace {
    [CmdletBinding(PositionalBinding=$False)]
    param([parameter(Position=0)][String]$Dir,
          [parameter(Position=1)][String]$OldStr,
          [parameter(Position=2)][String]$NewStr,
          [Alias("s")][String]$sheet  = "*",  # シートの名前。全シートを対象とする場合は "*"
          [Alias("r")][String]$range  = "*",  # セルの範囲（"A1:E5"など）。全セルを対象とする場合は "*"
          [Alias("a")][Int]$LookAt    = 2,    # セル内容が完全に同一であるものを検索  1:する 2:しない
          [Alias("c")][Int]$MatchCase = 0,    # 大文字と小文字の区別  0:しない 1:する
          [Alias("b")][Int]$MatchByte = 0     # 半角と全角の区別      0:しない 1:する
    )

    if (-not $Dir) { 
        Write-Host "対象フォルダが指定されていません。処理を終了します。" -Fore Red
        return
    } 

    if (-not (Test-Path -LiteralPath $Dir -PathType Container)) { 
        Write-Host "対象フォルダがありません。処理を終了します。" -Fore Red
        return
    } 

    $srcdir = Convert-Path -LiteralPath $Dir
    $dstdir = $srcdir + "_new"

    if (Test-Path -LiteralPath $dstdir) {
        Write-Host "$($dstdir)が上書きされます。続行しますか。Y/N [N]：" -Fore Yellow -NoNewline
        $answer = ""
        try { $answer = (Read-Host).Trim().ToUpper() } catch {}
        switch ($answer) {
               "Y" { Remove-Item -LiteralPath $dstdir -Recurse -Force }
           default { Write-Host "処理を終了します。" -Fore Red; return }
        }
    }
    New-Item $dstdir -ItemType Directory | Out-Null

    Add-Type -AssemblyName Microsoft.Office.Interop.Excel

    Write-Host "処理を開始します。"

    try {
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $False
        $excel.DisplayAlerts = $false
        $xlFomulas = [Microsoft.Office.Interop.Excel.XlFindLookIn]::xlFormulas
        $xlValues  = [Microsoft.Office.Interop.Excel.XlFindLookIn]::xlValues
        $default = [Type]::Missing
        $num_files = 0

        Get-ChildItem -LiteralPath $srcdir -Filter "*.xls*" -Recurse | % {
            $srcfile = $_.FullName
            Write-Host "$srcfile : " -NoNewline
            $workbook = $excel.Workbooks.Open($srcfile)
            $found = $false
            $workbook.Sheets | % {
                $worksheet = $_
                if ($sheet -ne "*" -and $sheet -ne $worksheet.Name) { Return }
                if ($range -eq "*") { 
                    $range1 = $worksheet.Cells
                } else {
                    $range1 = $worksheet.Range($range)
                }
                $result = $range1.Find($OldStr,     # What
                                       $default,    # After
                                       $xlFomulas,  # LookIn
                                       $LookAt,     # LookAt
                                       $default,    # SearthOrder
                                       $default,    # SearchDirection
                                       $matchCase,  # MatchCase
                                       $matchByte,  # MatchByte
                                       $default     # SearchFormat
                                      )
                if ($result) {
                    $found = $true
                    $range1.Replace($OldStr,     # What
                                    $NewStr,     # Replacement
                                    $LookAt,     # LookAt
                                    $default,    # SearthOrder
                                    $matchCase,  # MatchCase
                                    $matchByte,  # MatchByte
                                    $default,    # SearchFormat
                                    $default     # ReplaceFormat
                                   ) | Out-Null
                }
            }
            $newfile = $srcfile.Replace($srcdir,$dstdir)
            if ($found -and $srcfile -ne $newfile) {
                $num_files++
                Write-Host "置換あり" -Fore Green
                $dstsubdir = Split-Path $newfile -Parent
                if (-not (Test-Path -LiteralPath $dstsubdir -PathType Container)) {
                    New-Item $dstsubdir -ItemType Directory | Out-Null
                }
                $workbook.SaveAs($newfile)
            } else {
                Write-Host "置換なし"
            }
            $workbook.Close()
        }
        Write-Host "処理が終了しました。"
        Write-Host "$num_files ファイルの置換を行ないました。"
    } catch {
        Write-Host $Error[0].ToString() $Error[0].InvocationInfo.PositionMessage
    } finally {
        if ($excel) { $excel.Quit() }
        $result,$range1,$worksheet,$workbook,$excel | % { try{[Runtime.Interopservices.Marshal]::ReleaseComObject($_)}catch{}; $_ = $Null } | Out-Null
        [GC]::Collect()
    }
}

ExcelReplace $Dir $OldStr $NewStr

Write-Host "終了するには何かキーを押してください . . ."
$Host.UI.RawUI.FlushInputBuffer()
$Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyUp") | Out-Null
```

# 注意（2020.6.15追記）
Range.Find()メソッドの仕様によるものだと思われますが、スクリプト中で \$range（セルの範囲）に１つのセルを指定した場合に挙動がおかしくなるようです。