【Excel】を使った書類管理（その②）

同じフォーマットの書式の書類管理をExcelでするなら、ファイルごとで管理するよりデータベースを使ってみては？という話をその①でしました。

hamo440.hatenablog.com

話をまとめると、次の図ような内容です。

f:id:hamochikuwa440:20210604114846p:plain ①より②の方が制御ができ、容量の節約になりますという話です。

それでは早速、Excelで書類をデータベース管理する方法をVBAを使った例でまとめていきたいところですが、、、、

実際に作成するにあたっては、いきなりVBAでシステム構築に取り掛かる前に、管理する書類に合わせてデータベースの形をしっかり整え、ルールを決めることが大事です。

どのような事かと言いますと、例えば、雇用契約書であれば、社員が結婚して名前が変わったり、引っ越しで住所が変わったりしても、時系列で追いかけられるように、個人を判別する固定IDを設定する必要がありますし、業務によっては、部署や就業条件などにも同様に判別IDを設定し、名称の変更や条件の変更履歴を追いかけられるようにする必要もあるかもしれません。

このように、管理する業務や書類によってデータベースの形が変わってきますので、面倒ですが、管理する業務の流れを先につかみ、想定してデータベースを設計することが必要になってきます。
面倒でも手を抜かずに、データの組み合わせを先に設計し、繋がりを作っておくと、条件が変わることによって更新、変更しなければならない書類や情報が芋づる式に把握できます。設計が練り込まれていればいるほど、正確な業務執行ができ、業務軽減にもつながると思います。

しかし、こういった情報履歴の管理は、１００％間違いなく書類を作成できると考えたら、不要なのかもしれません。ですが、その①でも書きましたが「人はミスするもの」と考えてシステムは作るべきだと私は思います。
書類に修正が必要になることも想定し、過去にさかのぼっても出力できるものでないといけません。社員が引っ越しをする前の書類に、新しい住所が記載されたり、部署名が変わる前の書類に、新しい部署名が記載されるようでは、書類管理のシステムとしての意味がないのです。書類管理を考えるのであれば、さらに書類自体にも判別IDをつけて管理し、間違えた出力で出しているならそのまま記録しておかねば履歴にならないし、間違いにも気づかないということです。間違いを修正して出したなら修正した履歴を残すのです。

さて、データベースの設計が大事だと書いてきましたが、書類や業務によって管理するデータが違うということもご理解いただけたかと思います。

ということなので、データベースの設計はひとまず大事だということだけ覚えておいてもらいまして、システムを運用するために使うVBAの仕組みをまとめながら、データベースの扱いは、その都度説明していこうと思います。大事ではあるのですが、うまく説明できない気がしたので棚上げです。

まず、私がExcelの書類管理でよく使うVBAの仕組みは次のようなものです。

データベースを検索し該当するデータを表示する
データベースから絞り込むリストを作成する
絶対参照にならないように書類にデータを表示する
デスクトップに作成したファイルを出力する
ファイルを操作する
フォルダに格納されている複数のデータベースファイルを読み込む

と、だいたい管理に必要な仕組みはこのようなものでしょうか。（足りないものがあればその都度足していきます。）

次から具体的に上に挙げたVBAのコードを紹介していこうと思います。

最後に、VBAを作成する準備として、PCのデスクトップの場所（パス名）を調べるVBAコードを最下段に載せておきます。実際に動かしてみて確認してみてください。のちのち応用で使います。

また、VBAを使ったことが無い方用に「VBAの使い方」ボタンの中に使い方を簡単に書いておきます。クリックして見てください。もし、読んでわからない場合はお手数ですが、各自Google先生に聞いてみてください。。。

VBAの使い方

デスクトップのフルパス名を調べるコード

Sub desktop_pathname()
  Dim pathname as String
    pathname = Thisworkbook.Path
    Msgbox "パス名は" & pathname & "です"
    Worksheets(1).Range("A1") = pathname
End sub

使い方

Excelを新規作成で起動
開発タグのVisual Basicを起動
標準モジュールを挿入し、上のコードをコピペ
マクロ有効ブック形式でデスクトップに保存
開発タグのマクロを選択
リストの「desktop_pathname」を選択して実行
メッセージボックスに結果が表示
閉じるとSheet1のA１セルにパス名が記入される

注意点

マクロ無効設定の場合実行できません
ファイルを保存して閉じた場合、再度開くときはマクロを有効にしてください。

（まあデスクトップは大抵、Cドライブのユーザー名の下にあると思います）

Mac使いの方へ（私も）

Macの人はパス名の区切りがバージョンによって癖があり、MacとWinでファイルを共有する場合は工夫が必要です。（確か2019版は気にしなくても動いた気がしますが、、、）なのでApplication.PathSeparatorで変数に区切りを入れておいて、ファイル操作に使うと比較的安定して動きます。

それでもMacとWinでの共有はおすすめできません。間違いなく原因不明のクラッシュが発生します。Excel for Macについては言いたい事がありすぎて、それだけで１つの記事ができてしまうぐらいです（笑）それはまたの機会にします。

追加（デスクトップのパス名を得るコードの解説）

Sub desktop_pathname()-- Subが区切り始め、そのあとがコードのタイトル、（）は決まりごと。
  Dim pathname as String-- pathnameを文字列を扱う変数として宣言
    pathname = Thisworkbook.Path-- 今扱っているファイルの場所（パス名）を変数に入れる
    Msgbox "パス名は" & pathname & "です"-- メッセージボックスに変数に入ったパス名を表示
    Worksheets(1).Range("A1") = pathname-- 現在のブックのSheet1のA１セルにパス名を代入
End sub-- このコードの終わり（決まりごと）

はもちくわ

コードについて自分なりの解釈を書いてます。

【Excel】を使った書類管理（その②）