突然ですが資料作りって大変じゃないですか？
フォーマット、タイトル、図などと考えるべきことがたくさんあるはずです。
独自性のあるモノですといちから作る必要があると思いますが、ある程度フォーマットが決まっていて入力する内容も決まっているとなるといちから作るよりもテンプレートのようなものに記載した方が楽ではないでしょうか？
今回解説するツールはいちから資料を作成するのではなく、Excelにあるデータを転記してそれを基に資料を出力するというものです。
今回仕事でそういったツールを作る機会があり、これは業務効率化になりそうだと思いましたので、ここでその作成方法などを解説しておきます。
作成するツールの説明
では具体的に今回作成するツールの説明をしていきます。
Excelに社員情報や仕事で普段使う情報が記載されていたとします。
それを基に定期的に資料を作成する必要が出てきたときに、毎回一から作ったりそのデータを手動でコピーしたりするというのは面倒です。
そこで必要なデータのみを資料に転記してそれを別ファイルとして出力するというのが今回ご紹介するツールとなります。
では実際に私が作成したツールを動かしてみます。
ツール上には下記の3つのシートが存在します。
- 社員情報リスト
- 出張申請書
- 辞令書
中身は下記のようなものです。
では次に作成方法ですが、Excelでデータを管理していることを前提にVBAを使用してその機能を実現していきます。
「社員情報リスト」には下記のようなボタンを用意しています。
このボタンをそれぞれ押下することで、
「社員情報リスト」シートの「出力有無」欄に〇が付いている行のデータを基にそれぞれの資料がこのツールが置いてあるフォルダ上に作成されます。
複数の行で〇があっても複数ファイル作成されるようになっています。
このようなツールを今回は解説していきます。
VBA解説
では早速どういったコードを書いているのか解説していきます。
とその前にVBAを普段使用されない方もいるかと思いますので、簡単にコードを書くまでの説明をしておきます。
マクロの有効化
ではまずデータを管理しているExcelを起動します。
そしてマクロの有効化をするのですが、起動したExcelを「名前を付けて保存」をします。
その際に下記のように「ファイルの種類」を「Excel マクロ有効ブック」というものを選択して保存します。
これでマクロの有効化が完了し、VBAで書いた機能を動かすことができるようになります。
コードを書くまで
ではコードを書くのに必要なVisual Basic Editorを起動していこうと思います。
まずウィンドウ上部の「ファイル」タブ→「オプション」をクリックします。
起動した「Excelのオプション」ウィンドウ左端の「リボンのユーザー設定」をクリックします。
そしてウィンドウ右端に下記のような項目が表示されるはずですので、この「開発」にチェックを入れて、ウィンドウ下の「OK」をクリックします。
そうするとExcelのタブに「開発」という項目が追加されるため、それを選択し「Visual Basic」という項目をクリックします。
これでVisual Basic Editorが起動します。
次に起動したVisual Basic Editorの左端に下記のような項目があるはずです。
その中の「VBAProject(ファイル名)」というような項目があるため、そこで右クリックして「挿入」→「標準モジュール」を選択します。
そうすると下記のようなウィンドウが起動するため、ここにコードを書いていきます。
コードの解説
では先ほど解説したツールがどういったコードで動作しているのか解説していきます。
コード解説
'出張申請書ボタン
Sub TripPostingBotton()
Dim type_material As Integer
type_material = 0
PreparationMaterials (type_material)
Worksheets("社員情報リスト").Select
End Sub
'辞令書ボタン
Sub ResignationPostingBotton()
Dim type_material As Integer
type_material = 1
PreparationMaterials (type_material)
Worksheets("社員情報リスト").Select
End Sub
'データ取得
Sub PreparationMaterials(type_material)
Set list = ThisWorkbook.Worksheets("社員情報リスト")
Dim employeenumber As Integer
employeenumber = Cells(Rows.count, 1).End(xlUp).Row '表の最終行を取得番号
Dim OutputPresence As Integer '出力有無の判定がある列(データ項目数)
OutputPresence = 9
Dim information() As Variant '社員情報を全て格納する配列
Dim count As Integer '出力データ数
count = 0
Dim data_co As Integer
data_co = 0
'出力判定数
'3行目から最終行までの値を取得
For i = 3 To employeenumber
'出力有無の判定
If Cells(i, OutputPresence).Value = "〇" Then
count = count + 1
End If
Next i
'必要な配列を準備
ReDim information(count - 1, OutputPresence - 1)
'3行目から最終行までの値を取得
For i = 3 To employeenumber
'出力有無の判定
If Cells(i, OutputPresence).Value = "〇" Then
'列のカウント
For j = 1 To OutputPresence
'配列にセルの値を代入
information(data_co, j - 1) = Cells(i, j).Value
Next j
data_co = data_co + 1
End If
Next i
'どの資料を作成するか判定して関数にデータを渡す
If type_material = 0 Then
Call TripPosting(information())
ElseIf type_material = 1 Then
Call ResignationPosting(information())
End If
End Sub
'出張申請書に転記
Sub TripPosting(information() As Variant)
Dim affiliation As String '所属
Dim employeenumber As String '社員番号
Dim name As String '氏名
Set BusinessTrip = ThisWorkbook.Worksheets("出張申請書")
BusinessTrip.Select
Dim i As Integer
Dim j As Integer
Dim dir As String 'ファイル出力先フォルダ
dir = ThisWorkbook.Path 'このツールのある場所のパスを入れる
Dim fileout As String '出力フォルダ＆ファイル名
'データ数を取得
For i = LBound(information, 1) To UBound(information, 1)
affiliation = ""
employeenumber = ""
name = ""
'データ項目数を取得
For j = LBound(information, 2) To UBound(information, 2)
If j = 3 Or j = 4 Or j = 5 Then
affiliation = affiliation + information(i, j)
ElseIf j = 0 Then
employeenumber = information(i, j)
ElseIf j = 1 Then
name = information(i, j)
End If
Next j
'出張申請書シートに転記(+初期化)
Range("D11") = affiliation
Range("D12") = employeenumber
Range("D13") = name
'出張申請書を出力
fileout = dir & "\" & employeenumber & "_" & name & "_出張申請書.xlsx"
ThisWorkbook.Sheets(Array("出張申請書")).Copy
'すでにファイルがある場合は上書き保存する
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileout
Application.DisplayAlerts = True
ActiveWorkbook.Close
Next i
End Sub
'辞令書に転記
Sub ResignationPosting(information() As Variant)
Dim branchoffice As String '支社
Dim affiliation As String '所属
Dim name As String '氏名
Set Resignation = ThisWorkbook.Worksheets("辞令")
Resignation.Select
Dim i As Integer
Dim j As Integer
Dim dir As String 'ファイル出力先フォルダ
dir = ThisWorkbook.Path 'このツールのある場所のパスを入れる
Dim fileout As String '出力フォルダ＆ファイル名
'データ数を取得
For i = LBound(information, 1) To UBound(information, 1)
branchoffice = ""
affiliation = ""
name = ""
'データ項目数を取得
For j = LBound(information, 2) To UBound(information, 2)
If j = 3 Or j = 4 Or j = 5 Then
affiliation = affiliation + information(i, j)
ElseIf j = 1 Then
name = information(i, j)
ElseIf j = 7 Then
branchoffice = information(i, j)
End If
Next j
'出張申請書シートに転記(+初期化)
Range("F9") = branchoffice + "支社"
Range("F10") = affiliation
Range("F11") = name + " 殿"
'辞令を出力
fileout = dir & "\" & name & "_辞令.xlsx"
ThisWorkbook.Sheets(Array("辞令")).Copy
'すでにファイルがある場合は上書き保存する
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileout
Application.DisplayAlerts = True
ActiveWorkbook.Close
Next i
End Sub
上記コード内には下記のような流れで5つの関数を使っています。
ではそれぞれの関数ごとに解説していきます。
因みに関数とは処理のまとまりのようなものと考えておいていただければイメージしやすいかと思います。
まずTripPostingBottonとResignationPostingBottonという関数を準備しています。
今回実施する処理として「出張申請書」と「辞令」というボタンを作成しておき、そのボタンが押されたらそれぞれの資料を作成するという流れになっています。
その最初のボタンを押された際の処理がTripPostingBottonの出張申請書とResignationPostingBottonの辞令書になります。
これらの関数は次に一つの関数に集約されます。
それがPreparationMaterials関数で、これは「社員情報リスト」から必要なデータを取得するための処理を記述した関数になっています。
出張申請書のボタンが押された際には、0を辞令ボタンが押された際には1をPreparationMaterials関数に渡すことでどちらの資料を作成するのかを判断しています。
このように関数を一つに集約することでコード量が減り見やすいものとなりますので、このような書き方をしています。
そしてPreparationMaterialsではいくつかの処理をしています。
'出力判定数
'3行目から最終行までの値を取得
For i = 3 To employeenumber
'出力有無の判定
If Cells(i, OutputPresence).Value = "〇" Then
count = count + 1
End If
Next iまず上記では「社員情報リスト」の「出力判定」という列に〇という項目がいくつあるのかを数えています。
この〇となっている行のみ資料として作成します。
ReDim information(count - 1, OutputPresence - 1)
'3行目から最終行までの値を取得
For i = 3 To employeenumber
'出力有無の判定
If Cells(i, OutputPresence).Value = "〇" Then
'列のカウント
For j = 1 To OutputPresence
'配列にセルの値を代入
information(data_co, j - 1) = Cells(i, j).Value
Next j
data_co = data_co + 1
End If
Next i次に上記で〇となっている行のデータを配列に格納していっています。
'どの資料を作成するか判定して関数にデータを渡す
If type_material = 0 Then
Call TripPosting(information())
ElseIf type_material = 1 Then
Call ResignationPosting(information())
End If最後に上記で出張申請書の資料を作成するのか、辞令書を作成するのかを分岐しています。
この分岐後の関数が出張申請書を作成するTripPosting関数と、辞令書を作成するResignationPosting関数となっています。
TripPosting関数とResignationPosting関数は出力する内容と出力する行列が異なる点を除けばほぼ同じ処理をしています。
具体的にはPreparationMaterials関数で配列に代入したデータを一つづつ取得して目的のデータのみ表に出力して、それとを新しいファイルで出力するというものです。
以上がコードの解説です。
ボタンの作成
コードが書き終わりましたらボタンを作成していきます。
Visual Basic Editorを保存して閉じたら、Excelの開発タブ→挿入の下記赤枠のモノをクリックします。
これでボタンを作成できますので、適当なところでドラッグしてボタンを作成します。
そうすると下記のようなマクロの登録というウィンドウが表示され、先ほど作成したコード上の関数が表示されているためどちらかを選択します。
そうすると下記のようにボタンを作成することができます。
しかしこれでは何のボタンか分からないので、ボタン上で右クリックしてから左クリックします。
そうするとボタンの名前を変更することができ、これでツールが完成となります。
後はこのボタンをクリックするだけで先ほど書いたコードの処理を実行することができます。
まとめ
この記事ではVBAを使用してデータを転記して新たなファイルを作成する方法について解説してきました。
- 作成するツールの説明
- VBA解説
- コードの解説
今回解説したものは社員情報を使用して出張関連の資料や辞表などの資料を作成するものとなっていました。
しかし少し資料を改良することで、社員情報出力以外のモノにも応用が可能だと考えています。
自分の会社でこのツールを少し改良すればもしかしたら業務効率アップにつながるかも？とお考えの方はぜひ実施してみてください。
以上で今回の解説は終了となります。
コメント