Share
Embed
Transcript
24 slides
-
登壇内容について 3 ◼ 2018年に書いた、VBAによる自動化の記事は定期的なアクセスがあります。初めて VBAを触る方や、それを発展させたい方が読まれているのかなと思いますが、ありが たいことです。 ◼ 世には様々なシステムによる自動化があり、私もAnsibleを中心とした自動化を広げる 案件を担当しています。ただ、それを導入したとしても、VBAなくなる気配がありま せん。それぞれの得意なところ、苦手なところが別なのでしょうね。私はかゆいとこ
ろに手が届くのでVBAも好きです。 ◼ そこで、今回はVBAですぐにでも始められるファイル連携やVBAを書くときに考える ことについて紹介します。VBAで初めてツールを作るとしたらExcelの加工が多いと思 いますが、Excelファイルやテキストファイルを読み取ったり、Outlook VBAでメール の内容を他ファイルに書き込むこともできます。このような手のひらの上の自動化も 楽しいものです。 ◼ 視聴した翌週には、VBAを試していただけたら幸いです。 -
AnsibleとVBAによる自動化のすみわけ 6 自動化の得意領域 最適化の範囲 自動化の効果 Ansible サーバやNW機器の操作 全体 (自動化を広げやすい) 大きい
VBA Excelやテキストなどの ファイルの操作 個別 (1作業に特化) (個人には) 大きい -
複数ファイルの読み取り 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 ◼ 目的 ⚫ メール本文が定型化しているシステムからの通知メールを自動転記する ⚫ 管轄外のシステムとも連携が図れる ◼
サンプルコードの説明 ⚫ 利用の前提条件 ⚫ 項目名とデータが1行である(改行を含まない) ⚫ 1メールにつき、1内容である ⚫ サンプルコード ⚫ 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で作業を自動化する時に考えること 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を書くときは、フローから考えるとハマりにくい