Access データベース中の添付ファイル検索・保存を⽀援する
VBA マクロプログラムの開発
池田 健二
京都大学企画・情報部情報基盤課情報支援掛（兼）研究推進部産官学連携課知的財産・財務掛
ikeda.kenji.8s@kyoto-u.ac.jp

Development of VBA macros for Access database
IKEDA Kenji
Planning and Information Management Department, Kyoto University

概要
京都大学企画・情報部では、学内の部局に在籍する教職員や学生の情報系支援業務を実施している。特に情報基盤課情報支援掛では、部局の教育研究活動を直接支援するために、掛員が担当する部局に配置されている。本稿では、著者が配置されている研究推進部産官学連携課にて、事務職員の業務を支援するために開発・作成した、Access データベース中の添付ファイル検索・保存を行う VBA マクロプログラムについて述べる。

1 はじめに

著者は産官学連携課に兼務で配置され業務を行っており、そこでは、職員が PC を使った単純な繰

京都大学産官学連携本部は、大学の産官学連携り返し作業に困窮しているという課題があること活動を実施している部局であり、共同研究及び受が分かった。そこで、ヘルプデスク業務の一環と託研究等の推進、知的財産の確保・活用、ベンチャしてルーティン作業の効率化を行っている。

ー企業の育成・支援を活動方針としている。知的

本稿では、産官学連携課の事務職員から要望を財産を扱う弁理士をはじめ、企業での投資事業経受けて開発した Access VBA マクロプログラムに験者や法律関連のサポートをする弁護士などが所ついて述べる。

属しており、それぞれの専門性を活かして、大学の産官学連携活動について企画・戦略立案から活動のマネジメントまで実施している。

2

データ集計時における課題

研究推進部産官学連携課は、産官学連携本部の産官学連携課は、京都大学の産官学連携活動全事務支援を行う組織であり、大学事務職員で構成般に関する事務を担当することから、必然的に学されている。産官学連携課では、一般的な総務・庶内の部局とのやりとりが多く生じることとなる。

務業務や会計業務の他、共同・受託研究におけるなぜなら、学内の多くの部局で共同研究が実施さ学内外調整業務や知的財産に係る会計業務、出資れ、また、知的財産を保有する部局も多くあるた事業の一環である学内助成制度の取りまとめなど、め、教員や部局の事務担当と連携する必要がある産官学連携に関連する事務支援を幅広く行っていためである。

る。

そのため、産官学連携課の事務職員は、担当業

著者が所属している企画・情報部情報基盤課情務の特に全学的な取りまとめを行う際には、大量報支援掛は、学内部局の教職員を直接支援する技のデータを取り扱うことになる。これはデータ処術職員からなる組織である。掛員は企画・情報部

理を行う現場の職員にとって大きな負荷となってと兼務する形で他の部局に配置され、部局の情報おり、ひとつの課題であると考えられる。

関係業務を実施する他、ヘルプデスク業務などをまた、量的な面だけでなく、各部局から情報を通じて部局の教職員の情報支援を行っている。

収集する際のデータフォーマットについても課題があると考える。情報を各部局内で管理する場合

ルを保存するためには、次の手順で作業を行う必には適切なフォーマットであっても、それを全学要がある。なお、共同研究担当掛では、作業した当的に取りまとめる側から見れば整理しづらい場合該月における書類ファイルを保存管理する必要ががある。さらに部局側のフォーマットが急に変わある研究プロジェクトの一覧を把握しており、そった場合には、その都度対応して情報収集処理のれに絞って検索・保存を行う。

やり方を変えていく必要がある。
このような整理しづらい大量のデータを処理する作業は、時間的にも精神的にも負荷が高い。こ
れらの課題に対して、プログラムによる自動化が一つの解決策となるのではないかと考える。

3Access VBA システム開発

図 1 Access データベース中の添付ファイル

産官学連携課にて全学の共同研究や受託研究等の研究プロジェクト管理を担当する掛（以下、共同研究担当掛）においては、データ集計のためのAccess ファイルの処理が課題となっていた。
本章では、共同研究担当掛からの要望を受けてAccess VBA を用いて開発したマクロプログラムについて述べる。
3.1 対象とした業務
共同研究担当掛では研究プロジェクト管理の業務のひとつとして、毎月、研究プロジェクトにおいて新規に作られた書類を保存管理している。
部局ごとのフォルダを作り、配下に年月のフォルダを用意し、更新のあった研究プロジェクトに関する契約書等の書類をファイル名が研究プロジェクトの識別コード（以下、プロジェクトコード）で始まるようにして保存している。
ある部局では、研究プロジェクトを Access デー
タベースで管理しており、毎月最新の情報が格納された Access ファイルを共同研究担当掛宛てに送付している。Access データベースには、研究プロジェクトをカテゴリ別に分類した複数のテーブルが含まれており、テーブルの 1 つのレコードが 1つの研究プロジェクトに対応している。
Access には、レコード中にファイルを複数添付できる機能が存在する。図 1 のように添付ファイル列にクリップのアイコンが表示され、添付されているファイル数も表示される。クリップアイコンをクリックすると図 2 のポップアップ画面が表示され、添付ファイルをデータベースから取り出して保存できる。
Access データベースから、管理対象である研究プロジェクトに今月新しく追加された書類ファイ

図 2 添付ファイル保存時のポップアップ＜作業手順＞
・前月分の Access ファイルを開く
・Access データベースの複数のテーブルを開き、対象の研究プロジェクトのレコードが見つかるまで主キーであるプロジェクトコードで検索する・検索したレコードの添付ファイル列をクリック
してポップアップ画面を開く
・添付されている書類ファイルの名前を記録する
・今月分の Access ファイルを開く
・Access データベースの複数のテーブルを開き、対象の研究プロジェクトのレコードが見つかるまで主キーであるプロジェクトコードで検索する・検索したレコードの添付ファイル列をクリック
してポップアップ画面を開く
・添付されている書類ファイルを確認し、前月分から追加されたファイルを保存する・保存した書類ファイルの名前をプロジェクトコードから始まるように変更し、適切なフォルダに格納する

前月分と今月分の Access ファイルを同時に開けばとフィールド名に続けて、FileName、FileData を記

簡単に比較することができるが、Access ファイル

述することで 、１つのレコード中に複数添付されのサイズは約 2GB であり、両方のファイルを開くている書類ファイルについて、ファイル名とファと動作が非常に重くなり、作業することができなイルデータのリストをそれぞれ取得することがでい。また、添付された書類ファイルの名前を確認きる。なお、DAO の代わりに Microsoft ActiveX デするために毎回ポップアップ画面を表示させる必ー タ オ ブ ジ ェ ク ト (ADO) を 利 用 す れ ば 、 SQL

要があることから、作業コストがかかる。そもそServer などのデータベースにもアクセスが可能な、も、研究プロジェクト一覧のテーブルが複数に分より汎用的なプログラムになる。一見魅力的に感かれていることから検索作業自体の負荷が高く、じられるが、ADO を利用した場合、今回対象としファイル保存後のファイル名変更まで合わせると、たような Access データベースの 1 レコード中の複作業を担当する職員に多大な負荷が掛かっていた。

数添付ファイルに順にアクセスしてデータを保存する処理の実装に時間を要するため採用を見送っ

3.2 添付ファイル保存を自動化する改善
当初、共同研究担当掛より、た。
[要望②]の Access データベースに添付された書

 [要望①] マクロを利用して Access のデータ類ファイル保存については、すべての研究プロジベースの添付ファイルにアクセスできるようェクトを対象に保存を行うと容量が膨大になるこにしてほしい。とが分かった。さらに、各研究プロジェクトの書

 [要望②] Access データベース内のすべての研類ファイルでファイル名が重複している（例えば究プロジェクトを対象にしても構わないので、「契約書.pdf」など）ため、そのままの名前で保存データベース中に保存された書類ファイルをできないことも判明した。これらを解決するため、抜き出してほしい。

検索・保存を行うプロジェクトコードを入力デーの 2 つの要望を受け、改善に着手した。

タとして読み込み、対象の研究プロジェクトの書[要望①]の Access データベース中に格納された類ファイルのみを保存するようにした。また、保添付ファイル保存のために、VBA のデータアクセ存した書類ファイルの名前をプロジェクトコードスオブジェクト (DAO)を利用し、図 3 の処理を行から始まるように変更する処理をプログラム中でった。図 3 中の下線部分の「フィールド名」は、行い、保存時のファイル名が重複することを回避

Access データベースのテーブル定義時に添付ファした。

イル列につけた名前である。SQL 中でテーブル名

Dim rec_filename As DAO.Recordset '対象レコードの添付ファイルの名前リスト
Dim rec_filedata As DAO.Recordset '対象レコードの添付ファイルのデータリスト'テーブルからレコードを特定する SQL 文を実行し、ファイル名とファイルデータのリストを別々に取得
sql1 ="SELECT projID, テーブル名.フィールド名.FileName AS ファイル名 FROM テーブル名 WHERE （略"sql2 ="SELECT projID, テーブル名.フィールド名.FileData AS ファイルデータ FROM テーブル名 WHERE（略"Set rec_filename = database.OpenRecordset(sql1) 'SQL 実行
Set rec_filedata = database.OpenRecordset(sql2) 'SQL 実行
rec_filename.MoveFirstDo Until rec_filename.EOF'対象レコードの添付ファイルをひとつずつチェックするループ
file_name = rec_filename!ファイル名'ファイル名の取得
rec_filedata!ファイルデータ.SaveToFile dir_path & projID & file_name 'ファイルデータ保存
rec_filename.MoveNext
rec_filedata.MoveNext'次の添付ファイルに移動'次の添付ファイルに移動

Loop

図 3

Access データベース中の添付ファイルを取り出す処理（関連部分のみ抜粋）

3.3 前月分と今月分を比較して差分のみ取得し、して使用していた。
[要望③]の同時処理を実現するために、マクロプ実行結果を可視化する改善
続いて、共同研究担当掛の担当者と相談し、ログラムを部局から送付される Access ファイルか [要望③]研究プロジェクトの前月分と今月分ら分離することとし、データが空の Access ファイの Access をマクロで同時に処理できるように

ルにマクロプログラムの記述のみを行った。外部したい。それぞれで確認した書類ファイルのの Access データベースを参照し、SQL を実行でき差分だけを保存してほしい。ただし、前月とるようにするため、図 4 の処理を行っている。こ

今月で違いがない場合は、すべてのファイルれにより、担当者が部局から受け取った Access フを保存してほしい。

ァイルを開くことなく、前月と今月の研究プロジ

 [要望④]マクロを実行した研究プロジェクト

ェクトを検索し、書類ファイルを比較できるようそれぞれについて、前月分データベースの情になった。

報、今月分データベースの情報、保存したフ[要望④]の結果整理については、マクロを利用すァイルの情報などについて整理し、可視化しる担当者が視覚的に分かりやすくするため、テキてほしい。

スト形式のログ出力などではなく、Excel 表で出力することとした。マクロ中で Excel ファイルを生との要望を新たに受け、改善に着手した。

成し保存するため、図 5 の処理を行っている。

以前の[要望①、②]を受けて作成したマクロについては、部局から送付される Access ファイルに、担当者が都度マクロプログラムをコピー貼り付け

Dim connection As DAO.database '外部の Access データベースへの接続情報
Dim rec As DAO.Recordset
Set connection = DBEngine.Workspaces(0).OpenDatabase(Access ファイルのパス) 'データベースへの接続
rec = connection.OpenRecordset(sql) '外部の Access データベースで SQL を実行

図 4

外部データベースファイルを開く処理

Dim excelapp As Object 'Excel.Application
Dim excelbook As Object 'Excel.Workbook
Dim excelsheet As Object 'Excel.Worksheet
Set excelapp = CreateObject("Excel.Application") 'Excel を起動する
Set excelbook = excelapp.Workbooks.Add'Excel ブックを開く

Set excelsheet = excelbook.Worksheets(1)'Excel シートを開く～～～Excel の表にデータを入力する処理～～～
excelbook.SaveAs (excelfilepath)'データ入力が終わった Excel を保存する

excelbook.Close'Excel を終了する

図 5
プロジェクト 前⽉

今⽉

コード

テーブル名

テーブル名

Excel ファイルを生成し保存する処理

前⽉添付ファイル

今⽉添付ファイル

ダウンロードした

ダウンロード状況

ファイル

300210531 テーブルA

テーブルA

受託研究契約書.pdf

受託研究契約書.pdf

受託研究契約書.pdf

全ファイル保存300610538 テーブルB

テーブルB

契約書1.pdf

契約書1.pdf

契約書3.pdf

追加ファイルのみ保存

契約書2.pdf

契約書2.pdf

契約書4.pdf

契約書3.pdf
契約書4.pdf
300510785 登録なし

テーブルAなし

契約書.pdf

契約書.pdf

申請書.docx

申請書.docx

全ファイル保存

図 6 前月・今月のデータベース検索結果とダウンロードしたファイル情報の可視化（項目抜粋）

マクロ実行結果を可視化した Excel 表を抜粋したものを図 6 に示した。検索を行った研究プロジ

ベースのテーブルを抽象化する方針で改修することが望ましいと判断した。

ェクトごとに情報を１行でまとめ、前月分および今月分の書類ファイルの情報、実際にダウンロー

3.4.3 オブジェクト指向化の検討

ドしたファイルの情報などを整理した。実際には、

VBA はオブジェクト指向プログラミングに対図 6 の項目以外に、ファイル保存時の変更後ファ応しているため、Access のデータベースをクラスイル名や、ファイルの保存先フォルダなど、作業化することで抽象化を実現することとした。クラ

担当者が必要な情報を記載している。

ス化にあたっては、データベースのテーブルの単位でクラス化することとした。これにより、テー3.4 VBA マクロのオブジェクト指向化

ブルのインスタンスを生成すれば、テーブルがど3.4.1 業務状況の変化の Access ファイルのどのデータベースにあるかを共同研究担当掛のプロジェクト管理業務におい考慮することなく処理が可能となる。さらに、改て、部局から送付されてくる Access ファイルと修に合わせて、データベース部分だけでなく、マAccess データベースの構成が変わることとなった。

クロプログラム全体をオブジェクト指向化するこ

これまでは、1 つの Access ファイルの中に Accessととした。そのために VBA のクラスモジュール機データベースが 1 つだけ存在し、多数の研究プロ能を利用し、以下の 3 種類のクラスを用意した。

ジェクト情報がレコード単位で複数のテーブルに1.

分散していた。ある月を境に構成が変わり、送付されてくる Access ファイルが 2 つに増え、これま研究プロジェクトの情報が格納された
Access ファイル2.

で 1 つのファイル（データベース）に含まれてい入力データである検索対象のプロジェクトコードを羅列したテキストファイルたテーブルが 2 つのファイル（データベース）に3.

分散される形式となった。

これら 3 つのクラスを取り扱うメインの処理に出力データである結果整理 Excel ファイル

ついては、VBA の標準モジュールを利用して実装
3.4.2 マクロプログラムの改修方針の検討した。

Access ファイルの構成変更により、マクロプロ
グラム中で開く Access ファイルと Access データ

3.4.4 クラスの実装ベースの数が増え、改修が必要となった。Access デクラスの実装にあたり、Access・Excel・テキス

ータベースからの書類ファイルを保存する際にはトファイルを読み込み・書き込みするためのファ前月分と今月分を比較する必要があるため、新構

イルパスやファイル名を指定する部分は、標準モ成へ移行した直後の月（以下、移行月）においては、前月分は Access ファイルが 1 つ存在し今月分ジュールのメイン処理に記述することとした。これにより、共同研究担当掛の担当者がファイルパ
ス、ファイル名、Access のデータベース名、テーは 2 つ存在するという状況になる。そのため、単ブル名などの必要情報をマクロ実行前に入力する

純に各月の Access ファイルが 2 つに増える構成へ部分を一箇所に集約した。の対応改修とは別に、移行月専用のマクロプログ

VBA ではクラスの初期化設定を行うデフォルラムも必要となる。なるべく改修作業にかかるコトのコンストラクタ機能で引数を利用できない。

ストを減らし、かつ、今後同様に発生する可能性のある Access ファイル・データベース・テーブルの構成変更にも対応できるように、Access データ

そこで、ファイル情報を各クラスに渡すために、ファイルパスの設定を行うメソッドを用意し、コンストラクタ代わりに利用した。

Set db = New AccessDB'インスタンス生成
db.filepath = （略）'ファイルパスの設定（兼コンストラクタ）
file_names = db.check(project_code)'添付ファイル名の取得
Call db.save_file(project_code, download_list, save_filepath)'添付ファイルデータの保存

図 7 Access ファイル管理クラスを利用する部分の処理
Set input_text = New InputText
input_text.filepath = （略）project_codes = input_text.get_data'インスタンス生成'ファイルパスの設定（兼コンストラクタ）'プロジェクトコードの配列作成

図 8 テキストファイル管理クラスを利用する部分の処理
Set output_excel = New OutputExcel'インスタンス生成
output_excel.filepath = （略）'ファイルパスの設定（兼コンストラクタ）
Call output_excel.inputdata(nrow , dl_status, "追加ファイルのみ保存") 'セルに値入力

図 9 Excel ファイル管理クラスを利用する部分の処理

Access ファイル管理クラスには、図 7 に示すように、2 つのメソッドを用意した。引数のプロジェ

4 おわりにクトコードから添付ファイル名のリストを返すメ

本稿では、産官学連携課において実施した

ソッドは、前月と今月でファイルの数や名前を比Access データベース中の添付ファイルの検索・保較するために使用した。2 つめは添付ファイルを存を支援する Access VBA マクロプログラム開発保存するためのメソッドで、引数でプロジェクトについて述べた。マクロ作成の要望を受けて最初コードと保存対象ファイルを指定し、同じく引数に対応した際には、毎回プログラムコードをコピで指定したフォルダに保存できるようにした。

ー貼り付けして使ってもらうレベルであったが、テキストファイル管理クラスでは、図 8 に示す

最終的にはオブジェクト指向化まで行い容易にメように、プロジェクトコードの配列を作成するメンテナンスできるようになった。マクロを利用しソッド 1 つを用意した。テキストファイル中に記た業務担当者からは、「作業時間が大幅に削減でき載されたプロジェクトコードの重複を省く処理もた」との評価をいただいた。特にマクロ実行結果クラス内で行った。

を可視化した Excel 表の自動生成が好評であった。

Excel ファイル管理クラスは、図 9 に示すよう今回のマクロプログラム開発において、Accessに、セルに値を入力するためのメソッドを用意しデータベースの添付ファイルを VBA で処理するた。これは Cells プロパティを呼び出すだけの冗長部分が最も困難な箇所であった。Access VBA や的なものだが、利用者がメイン処理のコードをコVBA のオブジェクト指向化については、書籍やメントと合わせて確認する場合に備えて、より直Web で公開されている情報が非常に少ないため、感的に理解できることを意図した。また、Excel フMicrosoft 公式のドキュメントを適宜参照しながらァイルのフォントの調整や行高・列幅の設定、フプログラミングを行う必要があると感じた。

ィルタの設定、罫線の設定などの処理もクラス内

すでに述べたように大学事務の現場においてで行った。完成した Excel ファイルを保存する処は、Access や Excel を用いて大量のデータを管理

理はクラスの終了処理を行うデストラクタに記載し、その中から必要な情報を抽出して整理するよした。

うな業務が多く存在している。単純なデータ処理

これら 3 種類のクラスを用意し、それぞれが管については可能な限り自動化し、現場の担当者が理するファイルに関する処理をメイン処理から分本質的な業務に注力できるようになることが望ま離できた。今後、共同研究担当掛のプロジェクト

しい。今回習得した VBA プログラミングの知見を管理業務に新たな構成変更が発生したとしても、活かし、今後もプログラム開発を含めた情報支援柔軟に対応できると考える。

を行っていきたいと考えている。