【VBA】申込用書から参加者名簿を作成する

9月のイベントの準備で使ったVBAをメモ。


今回のイベントには複数の事務所から、数十名のスタッフが参加した。このイベント地域本社のスタッフが主催しているもので、今年で3回目。

準備については地域本社がリードしてアレンジするものと考え、前に出ることはせず、アシスタントに徹して裏方の業務に徹していた。

基本的に、参加予定者への連絡はすべて地域本社の担当者から行ってもらい、私がするのは現地でのロジなどの手配くらいで十分だろうとの認識だった。

参加者の情報を個別ファイルで管理するのはちょっと不便

勤務しているオフィスが異なるため、準備の進捗については主にメールでやり取り。そのため、メールがどんどん溜まっていき、お互いに段々と疲弊。その上、裏方用のメールとは別に、参加者に対するメールも発信されて、手に負えなくなりだした。

そのため、リーダーさんが、「参加者からの申込みは、他の事前課題と一緒にエクセルファイルにまとめて提出してもらうことにしよう」と提案してくれ、リーダーさんが作ってくれた。

参加者に提出してもらう申込書ファイルは以下のようなシート構成となっていた。内、私が各種手配のために必要としていたのは、【基本情報】シート。

  1. 基本情報
  2. 事前課題1
  3. 事前課題2

細かな項目は異なるが、基本情報シートは概ね下図のように構成。

basic_Information_Form_Sample

このようなファイルが数十個手元に届くと、メールとは別の面倒臭さがプンプン。確かに、メールベースでこのような情報が届くことに比べると、ファイルに各人のデータがまとまっていると便利ではある。

workflow_pre-event-questionnaire

しかし、各種手続きをすすめる上では、それでもやや不便。私の立場からすると、全員の情報が一つの表にまとまっている方が何かと都合がいいのだ。(ホテルの手配が完了したかのチェックや差し込み印刷など)

workflow_to_utilise_collected_data

個別ファイルにまとめられたデータを一つにまとめる

そこで、VBAでデータをまとめることにした。(そもそも、基本情報の集め方をFormsなどで行えば、このような手間をかけずに済む。)

今回は手順を以下の2つに分けた。必ずしも分ける必要はなかった。ただ、当時、一気に全部を考えるのが面倒臭くて分けた。2つ目は、本当に使い捨てマクロ。

  1. 各ファイルから必要なシートを一つのファイルに集約
  2. 各シートの内容を一枚のシートに集約

各ファイルから必要なシートを一つのファイルに集約

このマクロは後日使うかもしれない気がしたため、集約のための設定シートを作り、マクロを追加。(オリジナルにはボタンを設置してマクロを設定。すごくラク。)

setting_Form_Sample

Sub combiningMultiFiles()
Dim wb As Workbook
Dim fileName As String
Dim combinedBook As Workbook
Dim folderName As String
Dim sheetNum As Integer
Dim sheetNum As String
Dim cnt As Long
Application.ScreenUpdating = False
Set wb = ThisWorkbook
With wb.Worksheets(1)
'フォルダ名の書かれたセルを指定する。設定シートのE6セルに入力。
folderName = .Cells(6, 5).Value
'まとめるファイルの種類を指定する。ここでは、.xlsx形式に限定。
fileName = Dir(folderName & "\*.xlsx")
'ワークブックの何枚目のシートをまとめるかを設定する。設定シートのE10セルに入力。
sheetNum = .Cells(10, 5).Value
'結合ファイルでのシート名を設定する。設定シートのE12セルに入力。
sheetName = .Cells(12, 5).Value
End With
cnt = 1
Do While fileName <> ""
Workbooks.Open folderName & "\" & fileName
If combinedBook Is Nothing Then
Workbooks(fileName).Worksheets(sheetNum).Copy
Set combinedBook = wb
Else
Workbooks(fileName).Worksheets(sheetNum).Copy before:=combinedBook.Worksheets(1)
End If
combinedBook.Worksheets(1).Name = sheetName & "-" & cnt
Workbooks(fileName).Close False
fileName = Dir()
cnt = cnt + 1
Loop
Application.ScreenUpdating = True
MsgBox "DONE"
End Sub

各シートの内容を一枚のシートに集約

そして次のマクロを実行して完了。

Sub summarisingSheetData()
Dim wb As Workbook
Dim i As Long, j As Long
Dim summarySheet As Worksheet
Set wb = ThisWorkbook
Set summarySheet = Worksheets.Add(before:=wb.Worksheets(1))
summarySheet.Name = "Summary"
'Headingを設定。
With summarySheet
.cells(1, 1).Value = "事務所コード"
.cells(1, 2).Value = "事務所名"
.cells(1, 3).Value = "氏"
.cells(1, 4).Value = "名"
.cells(1, 5).Value = "氏(ふりがな)"
.cells(1, 6).Value = "名(ふりがな)"
.cells(1, 7).Value = "到着日"
.cells(1, 8).Value = "出発日"
.cells(1, 9).Value = "アレルギー"
.cells(1, 10).Value = "(緊急連絡用)携帯電話"
End With
For i = 2 to wb.Worksheets.Count
For j = 1 to 10
summarySheet.cells(i, j).Value = wb.Worksheets(i).cells(2 * j + 3, 5).Value
Next j
Next i
MsgBox "DONE"
End Sub
'another way
Sub summarisingSheetData_2()
Dim wb As Workbook
Dim i As Long, j As Long
Dim summarySheet As Worksheet
Dim arrHeader As Variant
Set wb = ThisWorkbook
Set summarySheet = Worksheets.Add(before:=wb.Worksheets(1))
summarySheet.Name = "Summary"
'Headingを設定。
arrHeader = Array("事務所コード", "事務所名", "氏", "名", "氏(ふりがな)", "名(ふりがな)", _
"到着日", "出発日", "アレルギー", "(緊急連絡用)携帯電話")
summarySheet.Range("A1:A10") = arrHeader
For i = 2 to wb.Worksheets.Count
For j = 1 to 10
summarySheet.cells(i, j).Value = wb.Worksheets(i).cells(2 * j + 3, 5).Value
Next j
Next i
MsgBox "DONE"
End Sub

もっとラクをするなら

今になって思えばPower Queryを使えばもっと簡単。ただ、当時は、時間もなく自分が一番慣れた方法を取った。(確実に業務を遂行できるから。)

実際には実行していないので、上手く行くかの保証はないけれど、これまでPower Queryで対応してきたタスクを考えるときっと出来るはず。

  1. まず、メールで申込書ファイルを受け取ったら、Outlookの別フォルダに移動(手動でもいいし、Quick Actionを設定してもいい)。
  2. Flowで添付ファイルを指定フォルダに格納する。
  3. Power Queryでデータを整形・同一フォルダ内のファイルをAppendするよう設定。
  4. デフォルトのままLoadをクリックして、シートにテーブルを表示(了)

汎用性はないので、プロジェクトの開始時点で作っておく必要があるけど、これだとノンコーディングで対応可。また、ファイルの入れ替えが合ったとしても、集計用のファイルの「すべて更新」をクリックするだけなので、どんなレベルの人にも業務を渡すことが可能。

データの集計に関する個人的な反省

私の会社では、事務スタッフが別のオフィスで働いている人とのプロジェクトに参加することは、営業チームに比べると少ない。

私はまだ多い方で、過去、人事関連で地域本社のスタッフと昔のデータを整えてみたり、ITの人たちをまじえてデータベースの構築の話をしてみたり、他の事務所の予算管理の補佐をしてみたりということがある。

どの案件も、一緒に働く人達と実際に会って、一緒にパソコンの画面を見ながら、「あーでもない、こーでもない」などとコミュニケーションを取ってきた。私も相手も、どの程度のパソコンスキルがあるかを何となく分かった状態で進めていたので、お互いに得意分野は任せて方法で進めてきた。

一方で今回チームを組んだスタッフとは、これまでに別の仕事で打ち合わせをしたこともあったけど、事務スキルの確認を全くしてきたことがなかった。

そのため、リーダーさんが私にタスクを頼むのを躊躇していたようだった。結局、イベントまで1週間を切った頃に地域本社で初めて本当の進捗状況を目の当たりにし、慌てて追加サポートを申し出た。

計画などが細かくしっかりしている人だと思っていたから安心しきっていたけど、それとデータ処理のスキルは別物という非常に当たり前のことに気付かされた出来事だった。

集計作業を一手に引き受けるのも、それはそれで違う気がするけれど、今後勤務地の違う人と一緒になにかに取り組むときには、単純な集計作業であれば得意だ、と言うことにしようと思う。