仕事で「エクセルの案件表を別のエクセルにクエリで表示し、それをフィルター関数でシート毎に担当者別の表にし、VBAで条件付き書式を設定して罫線などで修飾し、定期的に遠隔地のプリンタから出力する」という課題をこなしたので、方法を記録しておきます。
仕事のデータは使えないので替わりに下記のデータを利用します。
なかなかちょうど良い感じのが無くて困りました…
厚生労働省｜女性の活躍推進企業データベースオープンデータ
作業の都合上、遠隔地のプリンタでは無く自PC内にPDFデータを出力することにします。
実現すること
1. 既存の参照元からエクセルにパワークエリでデータを読みこみ、自動更新
2. フィルター関数で特定項目ごとにデータを分けて表示する
3. データの行数に応じてVBAで条件付き書式を適用し、可変の罫線や背景色を付ける
4. 一定の日時に自動でシート毎のPDFファイルを作成する
エクセルファイルとマクロの作成
クエリの設定
上記の参照元からCSVデータを取得し、エクセルファイルに変換後、パワークエリで読みこんでいます。
もちろん、パワークエリで読みこめるものならどんなデータでもOKです。
膨大な項目がありましたが、見本として必要なデータ部分のみ取得しました。
（今回はパワークエリの詳細は割愛します。）
後でVBAでデータの更新も設定するため、読みこむところまでできればOKです。
パワークエリだと参照元のファイルが変更されるのに合わせてこちらのファイルも更新できるので便利です。
扱いやすいようにクエリのテーブル名を適当に変えます。
フィルター関数
シート名を含んだタイトル行の作成については、下記のリンク先を参照しています。
こうしておくとシートをコピーしてシート名を変えると自動的にタイトルが変わるので便利です。
FILTER関数とCHOOSECOLS関数を組み合わせてGoogleスプレッドシートのQUERYのような機能を実現します。
セルA3に下記の数式を入力するとスペル機能によって必要なデータがすべて表示されます。
=CHOOSECOLS(FILTER(元データ,元データ[都道府県]="東京都",""),1,2,4,5,6,7,8)
シートをコピーして増やしたら、"東京都" の部分だけを変えていけば、その名称でフィルターしたデータが表示されます。
シートのコピーの前には出力用にページレイアウトを設定しておくようにしましょう。
FILTER関数とCHOOSECOLS関数はサブスクのMicrosoft365であれば使用が可能とのことです。
数式については下記のリンク先を参照しています。
VBAで範囲可変の条件付き書式を設定
元データの変動に応じてこちらの行も変動するため、いちいち書式設定をいじらずに済むよう、VBAで条件付き書式設定を行います。
例として下記の条件付き書式を設定します。
※タイトルや見出しはシート上で設定しておいてください。
※項目により日付等の書式も列ごとに設定しておいてください。
- A列に文字が記入されていれば、その行のG列までセルに罫線を付ける
- 企業規模が1001人以上の企業の行に水色を付ける
- 企業規模が10人未満の企業の行にピンク色を付ける
- 数字が0になっている部分の0を空白に（見えないように）する
開発タブから「Visual Basic」クリックしてVBEを起動
「Microsoft Excel Object」フォルダ ⇒ 「挿入」 ⇒ 「標準モジュール」を作成
（「マクロの記録」で適当なマクロを作成し、「マクロ」 ⇒ 「編集」でもOKです）
条件付き書式のVBAについては下記のリンク先を参照しています。
下記のようにコードを記述します。
Sub 条件付き書式設定()
Dim sheet As Worksheet
Dim lastRow As Long
'このブックのすべてのシートに対して処理する
For Each sheet In ThisWorkbook.Worksheets
'各シートのアクティブ化が必要
sheet.Activate
'「元データ」以外のシートに対して処理する
If sheet.Name <> "元データ" Then
'最終行数を変数に格納
lastRow = sheet.Cells(1, 1).End(xlDown).Row
'シートの既存の条件付き書式を削除する
sheet.Cells.FormatConditions.Delete
'条件付き書式を追加：A列～G列の見出し行より下から最終行まで
With sheet.Range(Cells(3, 1), Cells(lastRow, 7))
'A列が空白では無い場合の書式：各セルに実線の罫線を付ける
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A3<>"""""
.FormatConditions(1).Borders.LineStyle = xlSolid
.FormatConditions(1).StopIfTrue = False
'企業規模が1001人以上の場合の書式：行の背景色を黄色にする
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C3=""1001～5000人"""
.FormatConditions(2).Interior.Color = RGB(255, 255, 204)
.FormatConditions(2).StopIfTrue = False
'企業規模が10人未満の場合の書式：行の背景色をピンク色にする
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C3=""10人未満"""
.FormatConditions(3).Interior.Color = RGB(255, 204, 255)
.FormatConditions(3).StopIfTrue = False
End With
'数字が0になっているセルの処理：F列 3パターン
With sheet.Range(Cells(3, 6), Cells(lastRow, 6))
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($C3=""1001～5000人"",$F3=0)"
.FormatConditions(4).Font.Color = RGB(255, 255, 204)
.FormatConditions(4).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($C3=""10人未満"",$F3=0)"
.FormatConditions(5).Font.Color = RGB(255, 204, 255)
.FormatConditions(5).StopIfTrue = False
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(OR($C3<>""1001～5000人"",$C3<>""10人未満""),$F3=0)"
.FormatConditions(6).Font.Color = RGB(255, 255, 255)
.FormatConditions(6).StopIfTrue = False
End With
End If
Next
End Sub
セル範囲.FormatConditions.Addで条件付き書式の適用範囲と条件を設定します。今回はすべて「数式を使用して設定する」方法にしています。
続けてFormatConditions(数字)で適用する書式を設定します。数字が上から順に１、２、３……となってますが 《適用セル範囲が重なる設定は順に番号をずらす》 と覚えてください。
適用セル範囲が重ならない設定は (１) となります。
マクロを実行すると、「元データ」以外のすべてのシートに条件付き書式が設定されて下記のようになります。
ちゃんと最終行まで条件付き書式が設定されています。（東京都の会社、多い……）
メニューから条件付き書式のルールを確認してみます。
VBAでPDF出力関連を設定
仕事ではVPNで接続した遠隔地にあるプリンタに出力するという内容だったのですが、こちらではPDF出力をするマクロを作成しました。そんなに難しいものでは無いですがいちおうVBAのコードを載せておきます。
Sub PDF出力設定()
Dim sheet As Worksheet
Dim path As String
Dim today As String
'今日の日付をフォーマットして変数に格納
today = Format(Date, "yyyy.mm.dd")
'このブックのすべてのシートに対して処理する
For Each sheet In ThisWorkbook.Worksheets
sheet.Activate
'「元データ」以外のシートに対して処理する
If sheet.Name <> "元データ" Then
'PDFファイルのパスの生成（このファイルと同じフォルダに作成する）
path = ThisWorkbook.path & "\" & today & "_マクロ自動実行見本_" & sheet.Name & ".pdf"
'シート毎にPDFファイルを生成する（作成後にビューアを起動しない）
sheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=path, _
OpenAfterPublish:=False
End If
Next
End Sub
PDF出力については下記のリンク先を参照しています。
※フォントが「Noto Sans JP」だとPDFにしたときに文字化けするので、「Yu Gothic」に変えてます。
※仕事の方はVPN接続に「SoftEther VPN」というアプリが使ってあって（私が導入したものでは無い）、このアプリで設定してあるクライアントへの接続までPSスクリプトで自動でやるようにしたら色々と勉強になったので、気が向いたらまた別の記事に書こうかなと思います。
一つのマクロにまとめる
上記２つにくわえてパワークエリの更新もVBAで記述し、３つのマクロをCallを使って一つのマクロにまとめます。
クエリの更新は簡単でActiveWorkbook.RefreshAllと書くだけなので、これを最初に書いて他のマクロをCallすると良いですね。
参照データを更新後に条件付き書式を設定し、その後PDFファイルを作成するという流れになります。
自動実行の設定（PowerShellスクリプトとタスクスケジューラ）
押さえておきたいポイント
「エクセル マクロ 定期実行」などでググると、「エクセルファイルのオープンとマクロの実行を記述したPowerShellのスクリプトファイルを作成し、それをWindowsのタスクスケジューラで実行する」という方法が指南してあり、その方法でやってみるのですが、何度やってみてもスクリプトが実行できません。
さんざん試行錯誤した結果、初歩的な部分でやっておくべきポイントが３つありました。
1. スクリプトファイルの文字コードを《UTF-８（BOM付き）》にする
2. タスクスケジューラの設定にPowerShellの実行ポリシーを加える
3. タスクスケジューラの設定で最上位の特権で実行する
２と３はこの方法を説明するネット上の記事でもよく取り上げてあるので最初から対応していたのですが、１ができてないことにずっと気づかずに別の原因を探していたので、なかなかうまく行かなかったという……
ということでポイントを押さえながらPowerShellスクリプトを作成し、タスクスケジューラでタスクを設定します。
PowerShellスクリプトファイルを作成する
エクセルのマクロを定期的に自動実行するのに、Windowsに標準で入っている「タスクスケジューラ」を使います。
タスクスケジューラでこれも標準装備の「Windows PowerShell」を動かし、エクセルの起動についての指示を書いたスクリプトファイルを読みこんで使います。
「タスクスケジューラ PowerShell スクリプト エクセル マクロ」等とググるとやり方を教えてくれるサイトがたくさん見つかります。
私は下記のサイトがわかりやすかったですが、PowerShellに関してはまったくの初心者だったので、かなりあちこち見て概要を理解してから作成するようにしました。
テキストエディタで内容を書いて、拡張子を「ps1」にするとスクリプトファイルが作れます。
いろんなサイトを参考に下記のような感じで作成しました。
# コメント文
# 初期化
$excel = $null
# 起動したいエクセルファイルのフルパスを変数に格納
$file = "C:\フォルダ\ファイルのフルパス.xlsm"
# エクセルの起動
$excel = New-Object -ComObject Excel.application
# エクセルのウィンドウを表示、Falseなら非表示
$excel.Visible = $True
# アラートを非表示
$excel.DisplayAlerts = $false
# 指定ファイルのオープン
$book = $excel.workbooks.open($file)
# マクロの起動（引数が無い場合は名前だけ入れればOK・「"Module1.マクロ名"」と書いても良い）
$excel.run("マクロ名")
# ブックを保存して終了
$book.save
$book.close
# エクセルを終了・ブックの終了だけじゃなくこっちも絶対やる
$excel.Quit()
# 後処理
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($book)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
で、ここからが本題？
PowerShellのスクリプトはテキストエディタで作成し、拡張子を「ps1」にすれば良いのですが、パスやマクロ名に日本語が入っていたりすると文字コードの問題で動きません。
VScodeやメモ帳でスクリプトを作成したら、問題が出ないように文字コードを《UTF-８（BOM付き）》で保存しましょう。
ps1ファイルはPCのなるべくわかりやすい場所に置き、日本語名フォルダは避けましょう。自動起動するエクセルファイルと違う場所で大丈夫です。
下記リンクも参照してください。
タスクスケジューラの設定
タスクの作成
スタートボタンの横にある検索から「タスクスケジューラ」と検索して起動します。
タスクの新規作成についてはネットにたくさんあるので省略します。
下記のリンク先などを参照してください。
タスクの設定ポイント
PowerShellスクリプトを自動実行するためのタスクの作成で大事なポイントは下記のとおりです。
《操作》タブ
- １番めのプログラム/スクリプトには
%Systemroot%\\System32\\WindowsPowerShell\\v1.0\\powershell.exeと入れる。 - ２番めの引数には
-ExecutionPolicy Bypass "ps1ファイルのフルパス"を入れる。 - ３番めの開始には
ps1ファイルがあるフォルダのパスを 「""」無し で入れる。
《全般》タブ
- 「最上位の特権で実行する」に必ずチェックを入れる。
「操作」に関して、１番目には通常は実行するスクリプトのファイル名をフルパスで入れれば動くようですが、PowerShellに関してはプログラム本体のパスを入れないとダメらしいです。
２番めの引数で実行するスクリプトのパスを入れますが、通常はPowerShellはスクリプトの実行ができない設定になっており、これを変更するとセキュリティの問題が生じる可能性があるため、指定したスクリプトに限って実行ポリシーを変更するためにここで追加するということになります。
実行ポリシーについて説明するサイトがたくさんあるのでそちらを参照してください。
PowerShellのスクリプトの実行は管理者権限で行う必要があるため、「全般」の権限の設定も忘れないようにしてください。
すべての設定にスペルミス等が無ければ、作成したタスクを選択して右のメニュー内にある「実行」を押せばすぐにPowerShellスクリプトが実行されてエクセルが開き、マクロが実行されるはずです。
うまく行ったら、タスクのトリガーで日時を指定して定期的に実行できるようにしてください。
PCが起動していないと実行されないので、必要であればPC起動自体もタスクスケジューラで設定しておくなどすると良いでしょう。