目的
指定したフォルダの中にある複数の Excel ファイルに対して文字列の一括検索1や一括置換をおこないます。
使用方法
最も簡単なのは、下記のスクリプトをバッチファイル（拡張子 .bat）として保存して、その上に Excel ファイルが含まれるフォルダ（１つのみ）をドラッグ＆ドロップする方法です。この場合、検索文字列や置換文字列はスクリプトファイルの中に直書きして使用します。
PowerShellコンソール上で使用する場合は、スクリプト中で定義されている ExcelFind, ExcelReplace の各関数を直接利用する方が使い勝手がよいと思います。
スクリプト
PowerShellスクリプトですが、Windows バッチファイルとしても実行できる特殊な構造になっています。
バッチファイルとして実行する場合は拡張子を .bat として、PowerShellスクリプトとして実行する場合は拡張子を .ps1 として保存してください。
■ 一括検索
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" を付した名前の新規フォルダの中に出力されます。
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（セルの範囲）に１つのセルを指定した場合に挙動がおかしくなるようです。