ハイブリッド・エクセルを使う

ハイブリッド(hybrid)エクセルとは、accessのテーブルを使うエクセルシートで、Win坊の造語です。

ハイブリッド(hybrid)エクセルとは、accessのテーブルをデータベースとして使うものです。データベースが大きくなって エクセルのシートに収まりきらなくなると、もう限界だから「access」を使った方が良い、という場合があります。
しかし、accessとexcelは親戚ではあっても、国も文化も違う外国人のようで、accessを使うのは難しいものです。
特に、excelのハードユーザーには、accessには不便なことが多く、細かなことができません。
エクセルの良い点は、多くのユーザーに支えられて、かゆいところに手が届く何でもできる関数やVBAがあることです。
accessは玄人プログラマー向きで、面倒見が悪いのです。エクセルで簡単にできることができない!おまけに、「値段が高い!」。
access嫌いのWin坊が使うようになったのが、このハイブリッド型エクセル(Win坊の造語)です。
その都度調べながら使ってきましたが、自身のためにもテクニックをまとめることにしました。

■ハイブリッドの利点

以下のようなエクセルシートユーザーに向いています。

1.テーブルとして使うシートがあり、行数が増えてファイルサイズが大きくなっている。
2.シートのデータを共有するために、Bookそのものを共有して使っている。
3.accessを使いたいが、ソフトが必要数ない。
4.accessのRuntimeで動かすためのアプリを開発できない。

■最初の準備 VBAの設定

  VBAの編集画面を開き、ツールの参照設定で、データベースやアクセスの機能を使えるように、設定します。
これをしておかないと、マクロのコマンドが使えません。また、この設定はブックに反映されるので、新規のbookごとに同じ設定をしないといけません。機能を使わない場合は不要です。


これは、Excelのワークシートのアドインのようなもので、使うVBAコマンドによって必要なものが異なります。

ADO方式でデータベースに接続するための
Microsoft Active Data Objects
のライブラリなどが基本になります。

種類が多くて、どれがどんな機能があるのか分かりにくいです。

リストの下の方にあるものを選ぶと、上に上がってきます。

■ACCESSについての基礎知識

ACCESSは、Officeの一部ですが「Professional」バージョンにしか付いてきません。単体でも購入できますが、ワード・エクセルのセットと同じ位の値段がします。Professionalは、Personalバージョンの2倍の価格です。

Accseeで作られたアプリを、Accessの無い環境で動かすためのRunTimeは無料で使えますが、これでは開発やtableの編集はできません。しかし、エクセルからVBAでtableの操作ができます。これが、ここのテーマです。

データベース部分を、別のブックにまとめても良いのですが、エクセルの場合は、共有でデータベースを使うときに問題が出ます。また、データ部分のブックを別にしても、これを開くときには複数のブックを同時にオープンすることになるので、負担は同じです。また、データが同時に開かれると、同期が取れなくなることがあります。
サーバーにデータ部分をおいて、これを共有で使うような場合に、データベースを管理することが必要で、Accessのテーブルはこのような構造になっています。

Accessでアプリケーションを組む場合、リレーショナルデータベースの構造など、本格的な理解が必要になり、アプリの開発は素人プログラマーでは難しいのです。

ハイブリッド型のエクセルでは、データのシートのみをaccessのテーブル構造に置き、これをエクセルで読み書きするものです。エクセルシートでは、データーの行数は65000行しかないので、これを越えてしまうような場合は問題が起きます。一応、2007以降のバージョンでは行数が増えましたが、互換性がなくなります。accessのテーブルには、行数の制限はありません。

■エクセルで、データベースを最適化する

accessのテーブルは、編集するたびにサイズが肥大化し、一定の間隔で最適化しなければなりません。これが、エクセルからできます。
VBAは、DBEngine.CompactDatabase Sourcemdb, Targetmdb です。ここで、Sourcemdb, Targetmdbは、最適化するmdbファイルの名前の文字列です。ファイル名は、場所も含めて Sourcemdb="C:\database\dbname.mdb" のように定義しておきます。これを最適化したものは別のファイルに作られるので、これも同様に指定しておきます。最適化が終わったら、Name でリネームしますが、同名のファイルがあるとエラーになるので、temp.mdbなどの名前で退避させてから、削除してリネームします。

DBEngine.CompactDatabase Sourcemdb, Targetmdb
Kill temp.mdb     'temp.mdbがあると、この名前にリネームできない
Name Sourcemdb,temp.mdb    'これでSorcemdbが無くなり、temp.mdbになる
Name Targetmdb,Sourcemdb  'これで最適化が終わったファイルが、Sourcemdb という名前になり、Targetmdbが無くなる
この時点で、元のファイルは、temp.mdbとして残っています。

最適化は、DAO ObjectLibrary を使うので、この参照を追加しておきます。
*データベースへの接続は、基本的にDAOでなくADOを使うが、ADOの参照だけでは最適化はできないようです。しかし、2007以降の環境では最適化もADOの参照だけでできるようです。2003以前のoffice環境では、ADOだけの参照ではエラーが出ます。DAOを参照することで、Accessがインストールされていなくても最適化できます。

MsgBox FileLen(temp.mdb)&"バイトが、"& FileLen(Soucemdb) &"バイトに最適化されました。"
と確認を出すと、最適化の効果が分かります。次回、このマクロを実行するまでは、最適化する前のファイルもバックアップとして残っています。


■テーブルを読み込む 準備

ここからが、ハイブリッドの本題です。参照は、ADOを追加します。最適化をしないか、あるいはExcel2007以上で使うか、またはAccessもインストールされている環境では、DAOは無くても良いようです。DAOはADOよりも古い接続方式で、古いAccessでは基本の接続形式のようです。
基本的には、ADO(ActiveXを使った接続方式)を使います。
どちらを使うかで、コマンドは別のものになります。

Moduleに変数を定義します。

Const cnsADO_CONNECT = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'これはデータベースに接続するエンジンの定義

Dim dbCon As ADODB.Connection  '接続するデータベース
Dim dbRes As ADODB.Recordset  'テーブルデータ格納用
Dim dbFld As ADODB.Fields    'テーブルのフィールドデータ格納用
Dim LastPos As Double   'テーブルの行数格納用
Dim strSQL As String    'SQL文格納用
Const cnsADO_DB = "\hattyu.mdb"  '接続するデータベースの名前   ここではhattyu.mdbというデータベースに接続します。

■テーブルをすべて読み込み、シートに展開する

path1 = ThisWorkbook.Path   'cnsADO_DBには、ファイル名だけでパスが含まれていないのでpath1で場所を指定する
Set dbCon = New ADODB.Connection
Set dbRes = New ADODB.Recordset
dbCon.Open cnsADO_CONNECT & path1 & cnsADO_DB

strSQL = "SELECT * FROM tyuumon"    'tyuumonという名前のテーブルを指定します。
dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly
Rows("2:65536").ClearContents        '2行目以降をきれいにし
Range("A2").CopyFromRecordset dbRes   'コピーしたテーブルをA2以降に貼り付けます
dbRes.Close
Set dbRes = Nothing
dbCon.Close

ここでは、フィールド名はシートの1行目にあるものとします。従って、中身だけ2行目からシートに表示します。

■テーブル情報を読む

前項青文字部分は同じで、その間に以下の記述をして、テーブルの項目名・レコード数を取得します。

dbRes.Open "tyuumon", dbCon, adOpenKeyset, adLockReadOnly   'Openの後のテーブル名は”で挟んで文字列にします。
For i = 1 To dbRes.Fields.Count               'レコードセット.Fields.Count でテーブルの項目数
Sheet1.Cells(1, i) = dbRes.Fields(i - 1).Name       'レコードセット.Field(n).name で項目名 nは0から
Next i
MsgBox "件数" & dbRes.RecordCount          'レコードセット.RecordCount  でレコード件数
dbRes.Close
Set dbRes = Nothing
dbCon.Close
 

■テーブルからキィを指定して一行のデータを読む

キィを指定ということは、1件しかないデータを読むということです。マクロの前段と後段の共通部分は省略します。(前項青文字)
IDという項目を指定して、一致したものをシートに書き出します。

 strSQL = "SELECT * FROM tyuumon where ID= '" & Sheet2.Cells(2, 1) & "';"
dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly    '読み込み専用を指定します
For i = 1 To 14       '項目が14あるので
Sheet2.Cells(2, i) = dbRes.Fields(i - 1).Value       'これをSheet2に書き出します
Next i
dbRes.Close
Set dbRes = Nothing
dbCon.Close

■条件に合う複数行をシートに読み込む

項目の一つを指定し、条件に合うものを選ぶには、SQL文をそのように指定します。この時の注意は、SQL文は文字列であること・エクセルでは文字列と数値と区別が見えないことです。日付の値を検索に指定する場合注意が必要です。
例は、日付をシートから読み込んで、これを検索し、複数あった場合もシートに読み出すものです。

d0 = Sheet1.Cells(2, 2).Text       '日付書式のセルのデータを表示のままの文字列にします。
Rows("2:65536").ClearContents    '書き出す準備に項目行以外の行を空にします
gy = 2                    '2行目から書き出します
strSQL = "SELECT * FROM tyuumon WHERE 日付=#" & d0 & "#"     '日付けの文字列を#で挟んでaccessに渡します
dbRes.Open strSQL, dbCon, adLockReadOnly
If dbRes.EOF <> True Then       'テーブルの最終行でなければ
Do Until dbRes.EOF            '最終行まで繰り返します
With dbRes
For i = 1 To 14
Sheet1.Cells(gy, i) = Trim(.Fields(i - 1).Value)
Next i
End With
gy = gy + 1                '書き出し位置を繰り下げ
dbRes.MoveNext            '条件に合う次の行を探します
Loop
End If

■複数条件で検索して、複数行読み出す

SQL文のWHEREに、OR または AND で条件を追加します。
例えば、日付と記載者名のフィールドをANDで指定する場合は、

 d0 = Sheet1.Cells(2, 2).Text: d1 = Sheet1.Cells(2,3)      '(2,3)に記載者名がある場合
strSQL = "SELECT * FROM tyuumon WHERE 日付 =#" & d0 & "# And 記載者名 = '" & d1 & "'"

これ以前と以後は、前項と同じです。

■シートにあるデータを、キィを指定して一行書き込む

dbCon.BeginTrans    '前端のset文の後にこの一行追加します

strSQL = "SELECT * FROM tyuumon where ID= 'N10231'"    'IDがN10231のデータを探します
dbRes.Open strSQL, dbCon, adOpenKeyset, adLockOptimistic
If dbRes.EOF = True Then dbRes.AddNew               'もし、これが見つからなかったら新規に追加します
For i = 1 To 14
dbRes.Fields(i - 1).Value = Sheet2.Cells(2, i)             'Sheet2の2行目にあるデータを14項目テーブルに書き込みます
Next i
dbRes.Update                   '書き込んだ後処理ののちCloseを指定します
dbRes.Close
dbCon.CommitTrans               'dbCon.BeginTrans を閉じるコマンドです
Set dbRes = Nothing              '後は、同じ後段です。
dbCon.Close
Set dbCon = Nothing

■キィを指定して一行削除する
ここまでは前項と同じです。
strSQL = "SELECT * FROM tyuumon where ID= 'N10231'"    'IDがN10231のデータを探します
dbRes.Open strSQL, dbCon, adOpenKeyset, adLockOptimistic
dbRes.Delete      'このレコードセットを削除します。
dbRes.Update
dbRes.Close      'これ以降は、前項と同じです。

これも、access無しで作ることもできますが、どこかaccessがある環境で作ってしまう方が、簡単です。accessで新規作成し、その中にテーブルを作って、この種をエクセルで利用していきます。こうすることで、拡張子mdbのファイルが作られ、その中に複数のテーブルを配置できます。mdbという器を作り、その中のテーブルにデータを出し入れします。