1記事1ツールをVBAで作るサンプルシリーズ3です。
今回はサンプルシリーズ1のツール作成サンプルで作成したツールを、完全自動化します。
決まった時間に毎日自動でデータが集計されるツールは、普段の仕事においてこれ以上にない効率化ではないでしょうか。
指定されたフォルダに自動(もしくは手動)で格納される、という条件が発生しますが、1度設定してしまえば、自分が休みの日でも自動で出力してくれますので、非常に楽です。
サンプルシリーズ1の詳細は「売上を自動集計する」をご覧下さい。
また、サンプルシリーズ1を高速化したシリーズ2は「売上を自動集計する(高速編)」をご覧下さい。
今回も過去に書いた記事で掲載した内容で作れるツールなっています。
シリーズ3のポイントです。
- タスクスケジューラで自動実行
- 複数ファイルを1フォルダで管理
- Bat、VBS、VBAを使用する
- 処理済みの読み込みファイルを移動する
1.ツールを作る上で大事なこと
ツールを作るには、ある程度ツールの設計を考える必要があります。
簡単なツールであれば設計を飛ばして作成しても問題ありませんが、複雑なツールになるほど設計は大事になります。
そして、ツールは1つ1つのパーツを作り、組み立てていくことで出来上がります。
各パーツを作るときに、必ずいくつかの選択肢が発生しますので、作りたいツールに合わせた選択をしていきます。
選んだ選択によって、必ず新しい「運用ルール」が発生します。
運用や保守などを考慮しつつ、プロセスを構築するのも大事です。
2.今回使うサンプルデータ
今回は次のような注文日、注文番号、商品、単価、販売数量、売上金額の1か月分のデータ300行を使用します。
3.ツールの最終アウトプットと設計(プロセス)を考える
今回は「毎日決まった時間に、決まった形式のファイルを読み込んで、集計をして、決まったフォルダに別ファイルとして集計結果を出力する」というツールを作成します。
いきなり完成系のツールを作り始めるのではなく、まずは最終的にどのようなアウトプットを目標としているかを考えます。
そして、そのアウトプットを実現するための設計(プロセス)を考えたいと思います。
最終的なアウトプット
本当の最終は「別ファイルで保存」ですが、ここではどのように集計するかを考えます。
シリーズ1を使いますので、今回も「日別」、「商品別」の2種類の売上集計をしたいと思います。
売上と一緒に「販売数量」も一緒に集計したいと思います。
全体設計
最初は大まかで構いませんので全体設計を考えます。
今回はタスクスケジューラでBatファイルを起動して、VBscriptファイルを起動してからExcelVBAファイルを起動します。
管理するフォルダを作成する
今までと違うところはExcelVBAファイルとBatファイル、VBscriptファイル、そして読み込むためのファイルを格納するフォルダが必要となります。
そのため、管理が煩雑にならないために、専用フォルダを作成してそのフォルダで管理します。
今回はCドライブ直下に「Sampleシリーズ」というフォルダを作成します。
各ファイルはここに格納していきます。
その中にさらに読み込み用のファイルを格納するための「データ格納」フォルダを作成します。
また、処理済みの読み込み用ファイルを処理後に移動するための「処理済み」フォルダも作成します。
4.コードを書く
各ファイルはZipに圧縮したものを最後にダウンロードできます。
Batファイル
下記サンプルコードのBatファイルを作成したら、管理用に作成したフォルダに格納します。
詳細は「 タスクスケジューラでマクロを自動実行する 」をご覧ください。
メモ帳で構いませんので、エディタを開いて下記Sampleコード記述します。
「名前を付けて保存」する際に拡張子を「.txt」から「.bat」に書き換えて保存します。
拡張子の選択には「.bat」は出てこないので、手で入力します。
Batファイルはパスの部分は「￥マーク」で良く、「””」ダブルクォーテーションでくくる必要ありません。
作成したVBSファイルのパスやファイル名は便宜書き換えてください。
今回は「Auto.bat」というファイル名で保存します。
cscript C:\Sampleシリーズ\Auto.vbsVBscriptファイル
下記サンプルコードのBatファイルを作成したら、管理用に作成したフォルダに格納します。
詳細は「 タスクスケジューラでマクロを自動実行する」をご覧ください。
メモ帳で構いませんので、エディタを開いて下記Sampleコード記述します。
「名前を付けて保存」する際に拡張子を「.txt」から「.vbs」に書き換えて保存します。
こちらも同様に、拡張子の選択に出てこないので、手で入力します。
また、開きたいExcelファイルを指定しなおすには「“C:/Sample/Book1.xlsm”」の部分をフルパスのファイル名で便宜変更してください。
また、パスの「￥」マークはすべて「 / 」で記載してください。
今回は「Auto.vbs」というファイル名で保存します。
' Excel起動
Set oXlsApp = CreateObject("Excel.Application")
oXlsApp.Application.Visible = true
oXlsApp.Application.Workbooks.Open("C:/Sampleシリーズ/自動売上集計.xlsm")ExcelVBAファイル
ExcelVBAは「ファイル読み込み」「集計」「ファイル出力保存」「読み込む用ファイルを移動」「ファイルOpen時実行」の5つに分けてコードを作成します。
ファイル読み込み
ファイルの読み込みは自動で行いたいので、ファイル指定で読み込みます。
Sub Sample1()
Dim Filename As String
Dim ShCount As Long
ShCount = ThisWorkbook.Worksheets.Count
If Dir("C:\Sampleシリーズ\データ格納\読み込み用データ.xlsx") <> "" Then
Filename = Dir("C:\Sampleシリーズ\データ格納\読み込み用データ.xlsx")
Workbooks.Open "C:\Sampleシリーズ\データ格納\読み込み用データ.xlsx"
Worksheets(1).Copy after:=ThisWorkbook.Worksheets(ShCount)
Workbooks(Filename).Close savechanges:=False
ActiveSheet.Name = "データ"
'データをチェックする
With ActiveSheet
'項目が注文日/注文番号/商品/単価/販売数量/売上金額であることが条件
If Not (.Cells(1, 1) = "注文日" And _
.Cells(1, 2) = "注文番号" And _
.Cells(1, 3) = "商品" And _
.Cells(1, 4) = "単価" And _
.Cells(1, 5) = "販売数量" And _
.Cells(1, 6) = "売上金額") Then
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
MsgBox "集計できないデータ形式です。"
End
End If
End With
Else
MsgBox "ファイルがありません。"
End
End If
End Sub集計
集計部分のコードです。
こちらは「ツール作成サンプル 売上を自動集計する」の目次「集計するコード」そのまま使用しています。
Sub Sample2()
Dim MaxRow1 As Long
Dim MaxRow2 As Long
Dim i As Long
Dim ImpWS As Worksheet
Dim OutWS As Worksheet
Dim DateDic As Object
Dim ItemDic As Object
Dim myVal As Variant
Dim DateKey As Variant
Dim ItemKey As Variant
Const title1 As String = "日別売上"
Const title2 As String = "商品別売上"
Application.ScreenUpdating = False
Set DateDic = CreateObject("Scripting.Dictionary") 'Dictionaryをセット
Set ItemDic = CreateObject("Scripting.Dictionary") 'Dictionaryをセット
Set ImpWS = Worksheets("データ")
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "集計結果"
Set OutWS = Worksheets("集計結果")
With ImpWS
MaxRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
'■■日付のリスト作成■■
For i = 2 To MaxRow1
myVal = .Cells(i, 1)
If Not DateDic.exists(myVal) Then
DateDic.Add myVal, ""
End If
Next i
DateKey = DateDic.keys
'■■商品のリスト作成■■
For i = 2 To MaxRow1
myVal = .Cells(i, 3)
If Not ItemDic.exists(myVal) Then
ItemDic.Add myVal, ""
End If
Next i
ItemKey = ItemDic.keys
End With
With OutWS
'■■アウトプット作成■■
.Cells(2, 2) = title1
.Cells(3, 2) = ImpWS.Cells(1, 1)
.Cells(3, 3) = ImpWS.Cells(1, 5)
.Cells(3, 4) = ImpWS.Cells(1, 6)
.Cells(2, 6) = title2
.Cells(3, 6) = ImpWS.Cells(1, 3)
.Cells(3, 7) = ImpWS.Cells(1, 5)
.Cells(3, 8) = ImpWS.Cells(1, 6)
For i = 0 To UBound(DateKey)
.Cells(i + 4, 2) = Format(DateKey(i), "yyyy/mm/dd")
Next i
.Cells(i + 4, 2) = "総計"
For i = 0 To UBound(ItemKey)
.Cells(i + 4, 6) = ItemKey(i)
Next i
.Cells(i + 4, 6) = "総計"
'■■集計する■■
MaxRow2 = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 4 To MaxRow2 - 1
.Cells(i, 3) = Application.WorksheetFunction.SumIf _
(Range(ImpWS.Cells(2, 1), ImpWS.Cells(MaxRow1, 1)), .Cells(i, 2), Range(ImpWS.Cells(2, 5), ImpWS.Cells(MaxRow1, 5)))
.Cells(i, 4) = Application.WorksheetFunction.SumIf _
(Range(ImpWS.Cells(2, 1), ImpWS.Cells(MaxRow1, 1)), .Cells(i, 2), Range(ImpWS.Cells(2, 6), ImpWS.Cells(MaxRow1, 6)))
Next i
.Cells(MaxRow2, 3) = Application.WorksheetFunction.Sum(Range(.Cells(4, 3), .Cells(MaxRow2 - 1, 3)))
.Cells(MaxRow2, 4) = Application.WorksheetFunction.Sum(Range(.Cells(4, 4), .Cells(MaxRow2 - 1, 4)))
'■■装飾する■■
Range(.Cells(3, 2), .Cells(MaxRow2, 4)).Borders.LineStyle = xlContinuous '罫線
Range(.Cells(3, 2), .Cells(3, 4)).Interior.Color = RGB(64, 64, 64) '背景色
Range(.Cells(3, 2), .Cells(3, 4)).Font.Color = RGB(255, 255, 255) '文字色
Range(.Cells(3, 2), .Cells(3, 4)).HorizontalAlignment = xlCenter '中央揃え
'■■集計する■■
MaxRow2 = .Cells(Rows.Count, 6).End(xlUp).Row
For i = 4 To MaxRow2 - 1
.Cells(i, 7) = Application.WorksheetFunction.SumIf _
(Range(ImpWS.Cells(2, 3), ImpWS.Cells(MaxRow1, 3)), .Cells(i, 6), Range(ImpWS.Cells(2, 5), ImpWS.Cells(MaxRow1, 5)))
.Cells(i, 8) = Application.WorksheetFunction.SumIf _
(Range(ImpWS.Cells(2, 3), ImpWS.Cells(MaxRow1, 3)), .Cells(i, 6), Range(ImpWS.Cells(2, 6), ImpWS.Cells(MaxRow1, 6)))
Next i
.Cells(MaxRow2, 7) = Application.WorksheetFunction.Sum(Range(.Cells(4, 7), .Cells(MaxRow2 - 1, 7)))
.Cells(MaxRow2, 8) = Application.WorksheetFunction.Sum(Range(.Cells(4, 8), .Cells(MaxRow2 - 1, 8)))
'■■装飾する■■
Range(.Cells(3, 6), .Cells(MaxRow2, 8)).Borders.LineStyle = xlContinuous '罫線
Range(.Cells(3, 6), .Cells(3, 8)).Interior.Color = RGB(64, 64, 64) '背景色
Range(.Cells(3, 6), .Cells(3, 8)).Font.Color = RGB(255, 255, 255) '文字色
Range(.Cells(3, 6), .Cells(3, 8)).HorizontalAlignment = xlCenter '中央揃え
.Columns.AutoFit
End With
Application.DisplayAlerts = False
ImpWS.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Subファイル出力保存
ファイルの出力保存はダイアログボックスではなく、フォルダを指定して自動保存を行います。
保存するフォルダを指定して、「Move」でシートごと移動させます。
「集計結果日付(yyyy_mm_dd-hh-mm-ss)」でファイル名を指定して保存します。
Sub Sample3()
Dim Filename As String
Dim SaveFolder As String
Dim OutWS As Worksheet
Set OutWS = Worksheets("集計結果")
SaveFolder = "C:\Sampleシリーズ\"
With OutWS
.Move
ActiveWorkbook.SaveAs _
Filename:=SaveFolder & "集計結果" & Format(Now(), "yyyy_mm_dd-hh-mm-ss"), _
FileFormat:=xlWorkbookDefault
ActiveWorkbook.Close
End With
End Sub読み込み用ファイルを移動する
処理した読み込み用ファイルは次回の集計時に邪魔になりますので、処理後に移動しておきます。
移動時にファイル名を「読み込み用データ日付(yyyy_mm_dd-hh-mm-ss)」と変更しています。
詳細は「FileSystemObjectでファイルを移動」をご覧ください。
最後に「.Close」で自身のExcelVBAファイルを閉じます。
※ブックは閉じますが、Excelのアプリケーションは閉じません。
Sub Sample4()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo ErrLabel
FSO.MoveFile Source:="C:\Sampleシリーズ\データ格納\読み込み用データ.xlsx", _
Destination:="C:\Sampleシリーズ\処理済み\読み込み用データ" & _
Format(Now(), "yyyy_mm_dd-hh-mm-ss") & ".xlsx"
With ThisWorkbook
.Save
.Close
End With
Exit Sub
ErrLabel:
MsgBox Err.Description
End
End SubファイルOpen時実行
前回まではボタンを設置して、ボタンを押したら実行されるようにしていましたが、今回はファイルを開いたら自動実行されるようにします。
そのためには「ThisWorkbook」モジュールにSample1~3のコードを読み出すコードを書きます。
「 ThisWorkbook」モジュール？？ というかたは、次の画面のThisWorkbookをWクリックして、Workbookを選択してください。
自動でOpenが最初に選択されると思いますので、そこに次のコードをコピペしてください。
プロシージャ名は「Workbook」の「Open」を選択したら自動で記述されます。
Private Sub Workbook_Open()
Call Sample1
Call Sample2
Call Sample3
Call Sample4
End Subタスクスケジューラの設定
こちらの詳細は「タスクスケジューラでマクロを自動実行する」を参照してください。
タスクスケジューラの設定は、Batファイルが出来ていないと設定できませんので、まず先に下記Batファイルを作成してください。
「ファイルを指定して実行」で「taskschd.msc /s」を入力してタスクスケジューラを開きます。
基本タスクの設定」を選択 し、「タスク名を付ける」、「トリガーの設定」、「開始日と時間を設定する」、「操作するタスクの種類を選択」、「プログラムを選択する」の工程を設定します。
5.実行結果
実行前
実行前の管理用に作った「Sampleシリーズ」というフォルダ内は次のようになっていると思います。
読み込み用のデータ格納フォルダと、BatファイルをとVBSファイル、そしてExcelVBAファイルの構成になっています。
データ格納フォルダ内には「読み込み用データ.xlsx」というファイルが入っています。
実行後
タスクスケジューラが設定した時間になったら実行が開始されますが、試しに手動で実行してみます。
手動で実行する方法は、 タスクスケジューラ ライブラリを更新すると、先ほど設定したタスクが追加されているかと思いますので、右クリックすると実行が選択できます。
新しい集計結果のファイルが出力されています。
「処理済み」フォルダに今回読み込んだファイルが日付を付けたファイル名で移動されています。
データ格納フォルダにはファイルはありません。
6.注意事項
ここでは毎日読み込み用のファイルがあることを前提としているため、なかった場合や形式の違うデータが格納されていた場合は、MsgBoxで止まってしまいます。
そこを回避するような処理は組み込まれていませんので、便宜対応してください。
単純にエラーで止めたくない場合は各プロシージャのMsgboxをコメントアウトしてください。
読み込むためのファイル名も固定で指定しているので、こちらも便宜変更が必要になります。
処理が終わったあとにファイルを移動する場合は次の記事をご覧ください。
また、Bat→VBS→VBAや格納用のフォルダ名等、各パスやファイル名は参照先を間違えやすいので注意してください。
7.サンプルコード
次のサンプルデータを格納したZipファイルを、Cドライブ直下に 解凍 してください。
デスクトップに設置しても動きません。
もし他の場所に解凍した場合は、手動でCドライブに移動してください。
