Box上のExcelファイルを競合させずに読み書きする

クラウドストレージのBox、とりわけBox Driveを利用していると頻繁に遭遇するのが「ファイルの競合」。つまり、複数名でファイルに上書きを行うとデータの整合性がなくなり、片方のファイルはオリジナルから分離されて、コピーとして別ファイル化され、後でトラブルになるというパターンです。

ファイルサーバでは無いので、複数名で同時に開いて同時に編集といったことは、Microsoft365のExcel Online Business(有償)があればウェブ上で可能と言えば可能ですが、無い・許可されていない、またブック間リンクやらAccessのリンクテーブルで利用などが理由で、Box Driveを使ってる場合だと避けがたい問題です。

という事で今回、Box APIを利用してこれを回避しつつ複数名で書き込みができないか挑戦してみました。時間が出来たら、更新時の処理や削除時の対策なども追加しようと思います。

今回使用するファイル等

今回の記事は過去に公開済みの以下のエントリーをベースに進めていますので、Box API使用の事前準備や認証の仕組みなどについては、以下のエントリーを参考に進めてください。

VBAからBox APIを叩いてみる – 準備編

Box Driveの場合の問題点

共有フォルダというフォルダを2名の間で共有し、Box Driveを使えばアップロードとダウンロードのコードを省けるか?と考えてテストしてみましたが、非常に問題点が多い。理由はネットワークにあるのではなく、Box Drive自体の同期するスピードがあまりにも緩慢で、Box Drive上のファイルに対してリンクテーブルを貼って更新を掛けても、対象のxlsxファイル自体が同期がなされていない為、古いファイルのまま。

これでは、読み書きに大きなタイムラグが生じるため、仕方なくBox Driveでファイルの読み書きを今回のプログラムでは実装せずに、都度ダウンロードとアップロードを行う仕様にしています。

酷い時には30秒くらいタイムラグがあったので、ファイルの上書きバッティングが起こりかねません。Boxはあまり事務方向きではないですね。

事前準備

ここでの事前準備は、予め取得しておいたClient IDや配置するdatabase.xlsxのBox上のID、格納するフォルダのIDなどの利用するにあたっての事前作業になります。

database.xlsxの配置

書き込みをする為のExcelファイルで、データの登録部分は今回はテーブル化してあります。Box上にある適当なフォルダを用意して、その中にアップロードしておきます。

この時

  • 配置するフォルダのIDを取得しておきます
  • アップロード後のdatabase.xlsxのファイルのIDを取得しておきます。

この後、プログラム内でこの2つのIDを記述することになります。

boxmerge.accdbの編集

本来は設定用の窓と、設定値を保存する為の仕組みを用意しておくべきなのですが、今回はそれらは取っ払って、コード内に取得済みの設定値を書き込んでおきます。書き込み先はboxauthの標準モジュールになります。最上部のgeneral部に記述します。

  1. client_idにBox APIのクライアントIDの値を書き込む
  2. client_secretにBox APIのクライアントシークレットの値を書き込む
  3. redirecturlにBox APIに設定したリダイレクトURLの値を書き込む
  4. fileidには、前項で取得したファイルのIDを書き込む
  5. folderidには、前項で取得したアップロード先のフォルダのIDを書き込む

図:これらを使って認証を行います。

認証の実行

前項で各種値を書き込んで、boxAuthorizationを実行するとIEが起動しBoxのログイン画面が出ます。そこでログインに認証を実行すると、Access Tokenが取得されて、tokeninfoテーブルにそれぞれデータが追記される仕組みになっています。

デバッグしてみると、最後リダイレクト先に遷移した際のURLの後半に認証コードが含まれています。リダイレクト先がlocalhostの場合、ここが失敗してcodeが入っていないことがあります。

ここまで完了したら、Box APIを使ってのExcelファイルの読み書きをAccess上のプログラムから行えるようになっています。

例:https://officeforest.org/wp/?state=authenticated&code=ここに認証コードが入ってる

図:Boxへのログイン画面が出てきます。

図:認証を要求してくるので許可しましょう。

ソースコードと解説

ファイルのロック状態を取得する

  • 標準のGETリクエストではlockの状態が帰ってこないので、https://api.box.com/2.0/files/fileid/?fields=lockにて、追加のフィールドを取得するエントリポイントでもってリクエストが必要です。
  • ロックされていない場合は、lockの値がNullで返ってくるので、これを元に判定し、ロックされてる場合は処理を中止します

ファイルに対してロックする

  • 以前の記事にも記述した、ロックを掛けるコード。但し今回は無期限でロックを掛けるので、expires_atの指定はしていません
  • is_download_preventedにて、ダウンロードも不可の設定も行っています。
  • ロックさせる事で、他者に対してファイルの上書きを阻止し、自分自身がキープする事が可能になります。

ファイルのロックを解除する

Excelファイルに1行データを書き込んだら、速やかにロックを掛けてアップロードを行い、最後にロックを解除して上げる必要があります。

  • ロックを掛ける場合と殆どコードは同じ
  • 異なる点は、JsonObject.Add “lock”, Nullとする所。Nullとしてリクエストするとロックが解除されます。

ファイルのアップロード

  • アップロードは以前の記事にも記述しています。詳細は以下の記事を参照してみてください。
  • 今回異なる点は、xlsxファイルであるので、contentTypeでのMIME Typeはapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetを指定する必要があります。
  • また、アップロードするファイルはダウンロード時のパスであるCurrentProject.Path & “¥database.xlsx”となります。

VBAからBox APIを叩いてみる – 実践編

editフォームロード時などのインメモリレコードセット

  • 直接リンクテーブルをレコードソースにしない理由は、データ保存時に最新のExcelをダウンロードしてきてリンクを張り直す際に、上書きできなくなるため。その為、今回はincidentテーブルに直結せずインメモリのレコードセットをサブフォームに指定する為に、上記のコードを利用しています。
  • カラムを指定し、incidentテーブルの内容をメモリ上のrstADOに対してレコードを追加しています。

保存時のロジック

  • 詳細な解説は次項の仕組みの項目で説明しています。
  • AccessからはExcelに直接書き込みが出来ないので、オートメーションを利用し、尚且テーブルに対して行追加と値のセットを行っています。
  • 最新レコードを一番上にするために、テーブルに対してListRows.Addしています。
  • 今回は新規追加だけですが、更新時用に対象のレコードが書き換えられていないか?をチェックするための最終更新日情報の日付データを生成し、テーブルに書き込んでいます(更新時はこの値をチェックして読み込み時と書き込み時で値が変わってないかをチェックするようにします)
  • 書き込むIDは、リンクテーブルのincidentに対してDMaxで簡単に取得可能。+1した値が新規書き込み時のIDとなります。

実際のExcelファイルに対してロックを掛ける⇒書き込む⇒アップロード⇒ロックを解除する時間は、人間が手作業でやるにはあまりにも面倒な上に、時間が掛かるため、他者の手を止めてしまいますが、今回のプログラムの場合は、ロックを自動でコントロールし、掛かってる時間もアップロード時の一瞬なので、限りなくバッティングが発生しません。

また、手動でdatabase.xlsxは編集しないように運用が必要です(手動で行うからバッティングが発生するわけで)。本物のDBのように行単位ロックなどが出来ず、ファイル単位ロックになってしまいますが、簡易的な社内システムをこれで構築する事も可能になるのではないかと思います。

AccessでExcelを操作する

仕組み

今回のプログラムは以下のような手順でExcelファイルへの書き込みを行っています。

  1. 起動時に最新のExcelファイルをBox APIを使ってダウンロードしてくる
  2. 1.に対して、incidentという名前でリンクテーブルを自動で作成
  3. editフォームを開くと、2.の内容を直接ではなくインメモリレコードセットに作成し、それをレコードセットとして開く(リンクテーブルとExcelファイルは掴んだままではない状態)
  4. 下の画面にはExcelの内容が列挙されています。
  5. 上部の画面で入力欄を埋めてから保存をクリックすると今回の最も重要な作業が開始されます。

さて、ここからのロジックはバッティングを避けてデータをアップロードする為のフローになりますが、以下のようなロジックになっています。

  1. Box APIで対象のファイルがロックされているかを確認
  2. ロックされていなければ、今一度最新のExcelデータをダウンロードして、リンクテーブルを更新。
  3. 同時にファイルに対して、Box APIでロックを掛ける(ダウンロードも不可にしています)
  4. ExcelをAccessから操作し、テーブルに対して1行追加、フォーム上の値を追加
  5. この時、リンクテーブルのID列の最大値をDMax関数で取得しておき、+1した値を取得しています。
  6. Box APIを利用して、database.xlsxファイルをアップロードします。
  7. Box APIを利用して、ファイルに対してのロック解除を実行します。
  8. 最後にもう一度最新版のExcelファイルをダウンロードして、再度リンクテーブルを更新します。

今回は、新規追加のみを対応させましたが、既存のレコードの更新や削除されてた場合の対応を追加する事で、Box上のExcelをデータベースとして複数名で読み書きするようなプログラムをVBAで構築することが可能です。

図:メインの入力画面

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください