はじめに
Excelの条件付き書式コピペしたときにカオスに増殖するのツライ
ファイルにマクロをつけたくない
PowerShellでやってみよう
やりたいこと
- カオスになった条件付き書式をとりあえず一掃する(消すのすらExcelが固まる場合があるので)
- 新しい条件付き書式を追加する
- 保存
0.前提
- PowerShellが動く環境であること
- 編集したいファイルを配置しておくこと
- 初めてするときは、一行ずつ流して挙動を確認しながらすることをおすすめします
１.PowershellからExcelを起動
# Excelを操作する為のComオブジェクトの宣言
$excel = New-Object -ComObject Excel.Application
# 編集したいファイルのパス(適宜置き換えてください)
$path = "C:¥Users¥test"
#Excel可視化
$Excel.Visible = $TRUE
#Excelファイル開く
$Book = $Excel.Workbooks.Open($path)
#編集するシートを指定(適宜置き換えてください)
$Sheet = $Book.worksheets.Item("シート名")
これで、PowerShellから対象のExcelファイルを編集できるようになりました。
２.条件付き書式の削除
条件付き書式を消したいセルの範囲を指定してから消します。
A1からA20のセルを指定した場合は以下のように記述します。
例)Sheet.Range("A1:A20")
#既存の条件付き書式のクリア
$Sheet.Range("消去したいセルを指定").FormatConditions.Delete()
ここでExcelの条件付き書式を開き、全て消えていることを確認することをおすすめします。
3.条件付き書式の条件の追加
Powershellで追加する際はGUIの操作と異なり、条件を追加→その条件を適用させたセルのアクションの追加の２工程が必要になります。
今回は土、日、祝に一致したセルをそれぞれ指定した色に変えるという想定で書いていきます。
#条件の追加
$Test1 = A1:A10
$Sheet.Range($Test1).FormatConditions.Add(1,3,"土")
$Sheet.Range($Test1).FormatConditions.Add(1,3,"日")
$Sheet.Range($Test1).FormatConditions.Add(1,3,"祝")
この時点では、色が変わるといったことはなくただ、判定条件を入れただけです。
GUIと異なり「FormatConditions」を機能を使って条件を設定します。
詳細は以下のサイトを見ていただくのがわかりやすいかと思います。
(例)
FormatConditions.Add(1,3,"祝")→(セルの値,次の値に等しい,"値")
4.条件付き書式のアクションの追加
3.で追加した条件分に対して、起こしたいアクションを追加します。
$test1のセルの範囲に対してに対して条件が3つ登録されていますので、
その登録した順にアクションを追加する必要があります。
土日祝のセルへのアクション
$Sheet.Range($Test1).FormatConditions(1).Interior.ColorIndex = 45 #セルの色を黄色
$Sheet.Range($Test1).FormatConditions(2).Interior.ColorIndex = 6 #セルの色を黄色
$Sheet.Range($Test1).FormatConditions(3).Interior.ColorIndex = 23 #セルの色を黄色
5.ファイルの保存
編集したものを保存します。
上書きの場合は、EXCEL側に上書きするかのダイアログボックスが表示されるので、よしなにクリックしてください。
$Book.SaveAs(’保存したいファイル名のフルパス’)
6.EXCELを閉じる&ゴミ掃除
EXCELを閉じただけでは、バックグラウンドでプロセスが動いているのでそれらを閉じます。
#EXCELを閉じる
$Book.Close()
#COMオブジェクトの開放
$excel.Quit()
#ごみそうじ
[void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($Sheet)
[void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel)
# プロセスを解放する
$excel = $null
[GC]::Collect()
最後に
最後のゴミ掃除だけ、きっちり説明ができないのですが調べると皆さんこうされているのと
やらないとやっぱりプロセスが死ななかたのでおまじないのようにしています。