サンプル

ファイル共有(エクセルVBA)

VBAを使って、エクセルのファイルを複数のユーザーで共有し、同時に編集できるようにしてみましょう。

OneDriveに保存すれば、VBAを使わなくても共同編集が可能ですよね。

そのとおりです。ただし、法律事務所の場合、ITセキュリティ上の観点から共同編集を使えないところもあると思います。
これから紹介する方法を使えば、ファイルにパスワードを設定するなどのセキュリティ上の対策を十分に行ったうえでの共同編集が可能になります。また、データベースを構築する必要もありません。

でも、なんだか難しそうですね。
お手柔らかにお願いします!

ダウンロード

まずは、サンプル・ファイルをダウンロードしてください。

使用方法

使用手順

  • 「オプション」シートを開いて、管理者名(Windowsのユーザー名)および一時データのフォルダ名を入力してください。
  • 「データ」シートの「番号」列をダブルクリックすると、番号が自動入力されます。
  • 「データ」シートの各列にデータを入力すると、その行のデータを一時データに書き込みます。
  • 「データ」シートの「更新日時」列をダブルクリックすると、一時データを読み込みます。

細部については、「マニュアル」シートを参照してください。

データ共有の原理

データ共有の方法

エクセルファイルは、通常の開き方では1人のユーザーしか開くことができません。このため、管理者以外のユーザーは、このエクセルファイルを読み取り専用で開いて利用するようにしています。

このままでは、エクセルファイルに加えた変更を保存することができません。このため、ユーザーが加えた変更は、行ごとに一時データ(CSV)ファイルに保存し、必要に応じて、エクセルファイルに読み込むようにしています。

一時データのファイル数が増加すると読み込みに時間がかかるようになってしまいます。このため、管理者がエクセルファイルを閉じる前に、一時データをエクセルファイル内に取り込んでから削除できるようにしています。

他のファイルの共有方法との比較については、次の記事をお読みください。

注意事項

このサンプルは、VBAを使ってファイル共有を行う場合の参考にしていただくためのものです。
使用にあたっては、それぞれの法律事務所に応じた修正を行ってください。

このサンプルのマクロを実行するためには、セキュリティの設定が必要です。設定方法については、こちらの記事を参考にしてください。

管理者名を誤って入力してしまい、読み取り専用でしかファイルを開けなくなってしまった場合は、こちらのサイトで紹介されている方法を使って、マクロを実行せずにファイルを開いてください。

ブックとワークシートの構成

ブックの構成

ファイル共有システムのブックには、次の3つのシートが作られています。

  • データ
  • オプション
  • マニュアル

このブックは、ユーザーが管理者以外の場合は、読み取り専用で開かれます。このため、複数のユーザーが同時に開くことができます。

データシートの構成

「データ」シートには、他のユーザーと共有したい情報をテーブル形式で入力するようになっています。

テーブルの作成方法については、こちらのサイトを参考にしてください。

「データ」シート

ファイル共有のために必ず必要なのは、「番号」と「更新日時」の列です。

「番号」列をダブルクリックすると、その時点の日時を元に番号を自動生成します。これは、それぞれのユーザーが新規データの追加を行った際に「番号」が重複しないようにするためです。

各列にデータを入力すると、その行の「番号」をファイル名にした一時データ(CSVファイル)にデータが書き込まれます。そのファイルのタイムスタンプが「更新日時」に自動入力されます。このため、ブックが読み取り専用でも、入力したデータの保存ができます。

「更新日時」列をダブルクリックすると、他のユーザーが作成したものを含む一時データが読み込まれます。

「オプション」シートの構成

「オプション」シートでは、各種の設定が行えるようになっています。

「オプション」シート

「管理者」には、このファイルを書き込み可能で開く権限を有するユーザーのWindowsユーザー名を 設定します。それ以外のユーザーがこのファイルを開くと、自動的に読み取り専用になります。

「一時データフォルダ」には、一時データ(CSVファイル)を保管するフォルダを設定します。

「マニュアル」シートの構成

「マニュアル」シートには、「ファイル保管」の使用法と変更履歴を記載しています。

「マニュアル」シート

他のサンプルとは違って、テーブルの最初の列と最後の列以外は、入力項目が設定されていないのですね。

そのとおりです。
このサンプルは、ファイルの共同編集の部分だけに特化したものとなっていますので、これまで紹介した電話メモやファイル管理などのシステムと組み合わせて使用することを想定しています。

マクロの構造

プロジェクトの構成

全てのマクロは、イベントプロシージャで起動しています。

「WriteTempFile」は単数形で、「ReadTempFiles」は複数形なんですね。

書き込みは1つのファイルごとに行いますが、読み込みは全部のファイルを読み込みますので、そのように名前を付けています。

「ThisWorkbook」モジュールの機能

ThisWorkbook」モジュールには、ブックを開いた時に起動する「Workbook_Open」プロ―シージャ、ブックを保存する前に起動する「Workbook_BeforeSave」プロシージャおよびブックを保存した後に起動する「Workbook_BeforeClose」プロシージャが組み込まれいます。

Workbook_Open」プロ―シージャは、管理者名に応じて、ブックを読み取り専用にするかどうかを判断し、設定します。また、一時データからのデータの読み込みを行います。
Workbook_BeforeSave」プロシージャは、ブックが読み取り専用の場合に保存をキャンセルします。
Workbook_BeforeClose」プロシージャは、ブックが書き込み可能の場合に一時データの取り込み、ブックの保存、一時データの削除の一連の動作を行います。また、ブックが読み取り専用の場合に保存の確認を行わないようにします。

「Sheet11(データ)」モジュールの機能

Sheet11」モジュールには、まず、シートをダブルクリックした時に起動する「Worksheet_BeforeDoubleClick」プロシージャ、およびシートに変更が加えられた時に起動する「Worksheet_Change」プロシージャが組み込まれており、それぞれのイベントに応じたマクロを呼び出しています。

WriteTempFile」プロシージャは、修正が加えられた行のデータを一時データに書き込みます。他のユーザーが同じ番号のデータを既に変更している場合に対応できるようにフローを構成しています。

一時データの書き込みフロー


ReadTempFiles」プロシージャは、一時データの全データをシートに読み込みます。既にシートにデータがあるかどうかに応じて、上書きするか追加するかを決定するようにフローを設定しています。

一時データの読み込みフロー

このほかに、テーブルの並び替えを行う「SortTable」プロシージャ、およびテーブルの書式を設定する「FormatTable」プロシージャがあり、必要に応じて、他のプロシージャから呼び出されるようになっています。

「Sheet91(オプション)」モジュールの機能

Sheet91」モジュールには、シートをダブルクリックした時に起動する「Workseet_BeforeDoubleClick」プロシージャが組み込まれています。

Workseet_BeforeDoubleClick」は、「Sheet91」モジュールの「ReadTempFiles」プロシージャおよび「MoveTempFiles」プロシージャを呼び出し、一時データの整理を行います。

「Module1」の機能

標準モジュールである「Module1」モジュールには、イベントの抑止およびその解除などを行うプロシージャが組み込まれています。

DsblEvents 」プロシージャは、マクロ実行中のイベントの発生を抑止して、イベントが連鎖するのを防止します。併せて、画面の描画などを停止して、マクロの実行速度の向上を図っています。
EnblEvents」プロシージャは、「DsblEvents」が行った設定を解除するプロシージャです。

マクロの開始時にイベントの発生を抑止し、マクロの終了時に再開するようにしています。

マクロの全体的な構造は分かりました。あとはプログラムの細部ですね。

そうですね。それについては、サンプルファイルのコードにできるだけ詳しいコメントを付けてありますので、そちらをご覧ください。

このマクロの作成にあたっては、次のサイトの記事を参考にさせていただきました。
素晴らしいアイデアを公表していただけた井上 治様に感謝申し上げます。

配布の問題(1つのExcelブックを大勢が開く場合の対処)

コメント

  1. 管理人 より:

    サンプル・ファイルに次の修正を加えました。
    ・モジュールレベル変数による値の共有を引数に変更
    ・一時データの保存時、同一ファイル名がある場合は削除するように変更

  2. 管理人 より:

    次の修正を加えました。
    ・ブックを閉じる際に一時データの削除を自動的に実行
    ・上記に伴い一時データの移動機能を削除

タイトルとURLをコピーしました