Ansibleによる広がる自動化から見た VBAで始める手のひらの上の自動化 ～VBAファイル連携編～ 2021/07/18 伊藤雅人 @mizuto1217

自己紹介 2 ◼ 氏名：伊藤 雅人 ◼ 経歴：携帯電話の開発(3G時代)から帯域制御装置の保守・技術検証、 製造業の情シスを経て、現所属へ。 現在は、Ansibleを軸としたネットワーク運用自動化案件のPM、 Ansibleトレーニングの講師を担当しています。

登壇内容について 3 ◼ 2018年に書いた、VBAによる自動化の記事は定期的なアクセスがあります。初めて VBAを触る方や、それを発展させたい方が読まれているのかなと思いますが、ありが たいことです。 ◼ 世には様々なシステムによる自動化があり、私もAnsibleを中心とした自動化を広げる 案件を担当しています。ただ、それを導入したとしても、VBAなくなる気配がありま せん。それぞれの得意なところ、苦手なところが別なのでしょうね。私はかゆいとこ ろに手が届くのでVBAも好きです。 ◼ そこで、今回はVBAですぐにでも始められるファイル連携やVBAを書くときに考える ことについて紹介します。VBAで初めてツールを作るとしたらExcelの加工が多いと思 いますが、Excelファイルやテキストファイルを読み取ったり、Outlook VBAでメール の内容を他ファイルに書き込むこともできます。このような手のひらの上の自動化も 楽しいものです。 ◼ 視聴した翌週には、VBAを試していただけたら幸いです。

目次 4 1. AnsibleとVBAによる自動化のすみわけ 2. VBAのいいところ 3. VBAによるファイル連携 4. VBAで作業を自動化する時に考えること 5. まとめ

AnsibleとVBAによる自動化のすみわけ 5

AnsibleとVBAによる自動化のすみわけ 6 自動化の得意領域 最適化の範囲 自動化の効果 Ansible サーバやNW機器の操作 全体 （自動化を広げやすい） 大きい VBA Excelやテキストなどの ファイルの操作 個別 （1作業に特化） （個人には） 大きい

VBAのいいところ 7

VBAのいいところ 8 実行までの手軽さ 必要なものはExcelのみ。Excelを起動するだけで実行できる わかりやすい 普段から使っているExcelで結果が確認できる 自己完結できる 他システムに影響を与えることはない。好きなように実行、修正できる 見本が作れる 手作業をコードに置き換えてくれるマクロ機能がある

VBAによるファイル連携 9

複数ファイルの読み取り 10 ◼ 目的 ⚫ 複数のExcelファイルを一つにまとめ、集計する ⚫ 複数人の勤怠表Excelをまとめ、自動集計する ⚫ 複数のテラタームログ内の設定ファイルを確認し、資料にまとめる ◼ サンプルコードの説明 ⚫ 利用の前提条件 ⚫ 読み取りたいExcelファイルは、すべて同じフォルダにある ⚫ Excelファイルの拡張子は「 .xlsx 」とする ⚫ 機能 ⚫ 指定フォルダにある全てのExcelファイルのファイル名を配列に格納する

複数ファイルの読み取り 11 ◼ コードのポイント ⚫ 共通で使用する変数（グローバル変数）を決める ‘読み取るExcelファイルをまとめたフォルダの絶対パス Const FULL_PATH As String = “C:¥Users¥ユーザ名¥Desktop¥test¥“ ‘読み取ったExcelファイルの数 Dim NumFiles As Long ‘読み取ったExcelファイルのファイル名を格納する配列 ‘要素数は少し多めにする。修正しやすいように変数にしても良し Dim FileList( 20 ) As String

複数ファイルの読み取り 12 ◼ コードのポイント ⚫ Excelファイル名の一覧を配列に格納する Sub GetExcelList() Dim filename As String NumFiles = 0 filename = Dir( FULL_PATH & “*.xlsx” ) Do While filename <> "" FileList( NumFiles ) = filename NumFiles = NumFiles + 1 filename = Dir() Loop End Sub Dir関数で引数にワイルドカードを指定 すると、一致するファイル名が返る Dir関数で引数を省略すると、 前回の引数を指定したものとして実行する また、すでに返したファイル名を除外する

Outlook VBAでメールの内容を他ファイルに書き込む 13 ◼ 目的 ⚫ メール本文が定型化しているシステムからの通知メールを自動転記する ⚫ 管轄外のシステムとも連携が図れる ◼ サンプルコードの説明 ⚫ 利用の前提条件 ⚫ 項目名とデータが１行である（改行を含まない） ⚫ １メールにつき、１内容である ⚫ サンプルコード ⚫ https://mitomito.hatenablog.jp/entry/2020/04/13/235922 空行を除くと、100行もありません

Outlook VBAでメールの内容を他ファイルに書き込む 14 ◼ コードのポイント ⚫ メール受信イベント Private Sub Application_NewMailEx(ByVal EntryIDCollection As String) Dim myMsg As Object Set myMsg = Session.GetItemFromID(EntryIDCollection) /* ここに、何をしたいかを記述する */ Set myMsg = Nothing End Sub Outlookでメールを受信すると、 このApplication.NewMailEx イベントが発生します

Outlook VBAでメールの内容を他ファイルに書き込む 15 ◼ コードのポイント ⚫ メールオブジェクト Dim myMsg As Object Set myMsg = Session.GetItemFromID( EntryIDCollection ) 件名 ： myMsg.Subject 受信日時： myMsg.ReceivedTime 送信者 ： myMsg.Sender.Address 本文 ： myMsg.Body

VBAで作業を自動化する時に考えること

VBAで作業を自動化する時に考えること 17 ◼ 以下の流れで考える 1. 自動化したい作業をきめる 2. 現行手順を文章で“大まかに”整理する 3. 作成するツールの“大まかな”フローを書く 4. フローを細分化する 5. 必要そうな変数を考える 6. コードを書く

VBAで作業を自動化する時に考えること 18 1. 自動化したい作業をきめる ⚫ 複数のExcelファイルを一つにまとめ、集計する ⚫ 規模が小さいものを選択 2. 現行手順を文章で“大まかに”整理する ⚫ 複数のExcelファイルを同じフォルダにまとめる ⚫ 読み取るExcelファイルの一覧を取得する ⚫ 集計ファイルに、集計したいExcelファイルのシートをコピーする ⚫ 集計ファイルに、集計したいシートの値をコピーする （↑二つを全てのファイルをコピーするまで繰り返す） ⚫ 集計シートで、値を集計する

VBAで作業を自動化する時に考えること 19 3. 作成するツールの“大まかな”フローを書く ⚫ 意識してない前提も洗い出す ⚫ 形式通りに入力されていること ⚫ 同じフォルダにあること ⚫ 集計シートがあること ⚫ etc スタート エンド コピー前の ファイルはない？ シートをコピーする 項目をコピーする 値を集計する ある ない コードは考えません ファイルの一覧を取得する

VBAで作業を自動化する時に考えること 20 4. フローを細分化する ⚫ 「ファイルの一覧を取得する」って具体的には何をする？ ファイルの一覧を取得する コードは考えません 読み取るフォルダを決める ファイル名を取得する 読み取るファイル はない？ ある ない ファイル名を格納する

VBAで作業を自動化する時に考えること 21 5. 必要そうな共通変数を考える ⚫ 読み取るファイル名 ⚫ コピー元のシート名 ⚫ コピー先のシート名 ⚫ 集計する項目名 ⚫ 集計する項目値 ⚫ etc ファイルの一覧を取得する エンド コピー前の ファイルはない？ シートをコピーする 項目をコピーする 値を集計する ある ない まだコードは考えません 読み取る ファイル名 コピー元の シート名 コピー先の シート名 項目名 各項目の値 項目名 スタート

VBAで作業を自動化する時に考えること 22 6. コードを書く ⚫ 処理を大まかにわけ、それぞれを関数にする ⚫ ループや分岐をコードにする ⚫ そのほかの処理をコードにする スタート エンド コピー前の ファイルはない？ シートをコピーする 項目をコピーする 値を集計する ある ない While .Copy IF、For ファイルの一覧を取得する

まとめ

まとめ 24 ◼ VBAが得意な領域はある ◼ VBAを試すハードルはとても低く、しかも効果は大きい ⚫ Excelがあるだけでよい ⚫ 他に影響を与えることもない ◼ VBAはExcelだけではない、Outlook VBAでメール連携もできる ◼ VBAを書くときは、フローから考えるとハマりにくい