PowerShell Excel 操作例

3個のExcelファイルを開き各ファイル（ワークブック）のセル B2:C5 のデータを新規のワークブックにコピーします。

# excelwbook1.ps1 Excel ワークブック操作例

Set-StrictMode -Version latest

# Excel ファイル名配列
$sarwkbook = "wBook1.xlsx", "wBook2.xlsx", "wBook3.xlsx"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true            # 表示状態とする

# 新規のワークブック
$wbook0 = $excel.Workbooks.Add()
$sheet0 = $wbook0.Sheets(1)
Write-Host $sheet0.Name

$ifc = $sarwkbook.Count       # Excelファイル数
# 全ファイルを開く
for($iw = 0; $iw -lt $ifc; $iw++){
   $sfnm = $PSScriptRoot + "\" + $sarwkbook[$iw]
   [void]$excel.WorkBooks.Open($sfnm)
}

$ic = $excel.WorkBooks.Count      # 開いているワークブック数
Write-Host $ic

# セル内容をすべてコピーする場合
$iy = 2
for($iw = 0; $iw -lt $ifc; $iw++){
   $sheet = $excel.WorkBooks.item($sarwkbook[$iw]).Sheets(1)
   [void]$sheet.Range("B2:C5").Copy($sheet0.Range("B" + $iy.ToString()))
   $iy += 5
   $ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
   $sheet = $null
}

# セル内容の値のみコピーする場合
<#
$exlpasteType = "Microsoft.Office.Interop.Excel.XlPasteType" -as [type]
$iy = 2
for($iw = 0; $iw -lt $ifc; $iw++){
   $sheet = $excel.WorkBooks.item($sarwkbook[$iw]).Sheets(1)
   [void]$sheet.Range("B2:C5").Copy()
   [void]$sheet0.Range("B" + $iy.ToString()).PasteSpecial($exlpasteType::xlPasteValues)
   $excel.CutCopyMode = 1
   $iy += 5
   $ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
   $sheet = $null
}#>

pause

# 開いたExcelファイルを閉じる
for($iw = 0; $iw -lt $ifc; $iw++){
   $wbk = $excel.Workbooks.item($sarwkbook[$iw])
   $wbk.Saved = $true              # 終了時の保存確認メッセージを表示しない
   $wbk.Close()
   $ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbk)
   $wbk = $null
}

$sr = Read-Host "保存しますか？ y/n"
if ($sr -eq "y"){
   $sfnm = $PSScriptRoot + "\nwBook0.xlsx"
   if (Test-Path $sfnm){
      Remove-Item $sfnm
   }
   $wbook0.SaveAs($sfnm)
}

$wbook0.Saved = $true
$wbook0.Close()

$excel.Quit()
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet0)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbook0)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
$sheet0 = $null
$wbook0 = $null
$excel = $null
[System.GC]::Collect()
   

3個のExcelファイルを1ファイルずつ順に開き各ファイル（ワークブック）のセル B2:C5 のデータを新規のワークブックにコピーします。

# excelwbook2.ps1 Excel ワークブック操作例

Set-StrictMode -Version latest

# Excel ファイル名配列
$sarwkbook = "wBook1.xlsx", "wBook2.xlsx", "wBook3.xlsx"

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true            # 表示状態とする

# 新規のワークブック
$wbook0 = $excel.Workbooks.Add()
$sheet0 = $wbook0.Sheets(1)
Write-Host $sheet0.Name

$ifc = $sarwkbook.Count       # Excelファイル数

$iy = 2                       # セル内容をすべてコピー
for($iw = 0; $iw -lt $ifc; $iw++){
   $sfnm = $PSScriptRoot + "\" + $sarwkbook[$iw]
   $wbk = $excel.WorkBooks.Open($sfnm)
   Write-Host $wbk.Name      # ワークブック名
   $sheet = $wbk.Sheets(1)
   [void]$sheet.Range("B2:C5").Copy($sheet0.Range("B" + $iy.ToString()))
   $wbk.Saved = $true
   $wbk.Close()
   $iy += 5
   $ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
   $ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbk)
}

$sr = Read-Host "保存しますか？ y/n"
if ($sr -eq "y"){
   $sfnm = $PSScriptRoot + "\nwBook0.xlsx"
   if (Test-Path $sfnm){
      Remove-Item $sfnm
   }
   $wbook0.SaveAs($sfnm)
}

$wbook0.Saved = $true
$wbook0.Close()

$excel.Quit()
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet0)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbook0)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
$sheet0 = $null
$wbook0 = $null
$excel = $null
[System.GC]::Collect()
   

VLOOKUP 関数 VLOOKUP(検索値, 範囲, 列番号, 検索方法)
検索方法 $false: 完全一致 $true: 検索値以下の最大値
検索値が存在しない場合、例外が発生するため下記例では、try catch で例外処理を行っています。

# exlVlookup.ps1 vlookup 操作例

Set-StrictMode -Version latest

$sExlfnamer = $PSScriptRoot + "\exlTest.xlsx"       # 開くExcel ファイル名

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true            # 表示状態とする

$wbook = $excel.Workbooks.Open($sExlfnamer)

$sheet = $wbook.Sheets(3)         # 3番目のシートを取得
$sheet.Activate()                 # シートを前面に

# セル C2 の値を検索値とする
try{
   $sv = $excel.WorksheetFunction.VLOOKUP($sheet.Range("C2"), $sheet.Range("B4:D9"), 2, $false)
}
catch{
   $sv = "検索値は存在しません。"
}
Write-Host $sv

# 検索値を直接指定
try{
   $sv = $excel.WorksheetFunction.VLOOKUP(66, $sheet.Range("B4:D9"), 2, $false)
}
catch{
   $sv = "検索値は存在しません。"
}
Write-Host $sv

Pause

$wbook.Saved = $true              # 終了時の保存確認メッセージを表示しない

$excel.Quit()
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbook)
$ir = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)