改善項目を検証する-1(Excelデータベースその12)

VBA

ExcelVBA-Excelファイルをデータベースとして使う-その12です。 前回の記事はこちらになります。
hirocom777.hatenadiary.org

改善ポイントの動作を確認する-1

　前回はクラスモジュールのテーブル選択機能とトランザクション機能を改善しました。今回は、このうちテーブル範囲選択機能について検証していきましょう。

テーブル選択機能の確認

　改善したクラスモジュールではテーブルの選択方法が拡張されています。指定できるのはシート、セル範囲、名前付きセル範囲の3つです。今まではシートの選択のみでしたが、前回の最後にセル範囲によるテーブル選択でデータの追加を実施してみました。結果は大成功！！でも、よく考えるとデータを追加したことによってテーブルのセル範囲が変わっています。(下に伸びている)と、言うことは次にデータを追加するときは変更した範囲を指定しなければならないということです。前回の条件で以下のプロシージャを実行してみましょう。

'シート範囲指定のテーブルにデータを追加する2
Private Sub AddDataToRAngeTable_2()
Dim recordData() As Variant
  Dim db As clsExcelDbase
  Set db = New clsExcelDbase
  db.dbFileName = ThisWorkbook.Path & "\データ.xlsx"
  db.dbTableName = "Sheet1$B2:F5"
  recordData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "青山一郎", "男性", "AB", 30)
  db.AddRecord recordData()
  recordData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "池田葉子", "女性", "B", 50)
  db.AddRecord recordData()
End Sub


テーブルの範囲を変更しないまま再度データを追加しようとすると以下のようなエラーが出ます。

f:id:HiroCom777:20200418133447j:plain
2回目の書込みの際にエラーが発生していますね。1回目の書込みでデータの範囲が拡張されてしまっています。にもかかわらずテーブルの指定範囲は
"Sheet1$B2:F5"
のままなので、書き込み作業がうまくできないようです。2回目を書き込むにはテーブルの範囲を変更してデータベースに繋ぎなおさなければなりません。どうやらセル範囲によるテーブルの指定はデータを追加する用途には向いていないようです。あらかじめ決められた範囲のデータを検索して参照する用途向けですかね。それでは、名前付きセル範囲ではどうでしょう？

名前付きセル範囲によるテーブル選択

まずは、上で使用したプロシージャのテーブル範囲指定を "Sheet1$B2:F5"から "Table1"に変更します。プロシージャは以下のようになりますね。

'シート範囲指定のテーブルにデータを追加する3
Private Sub AddDataToRAngeTable_3()
Dim recordData() As Variant
  Dim db As clsExcelDbase
  Set db = New clsExcelDbase
  db.dbFileName = ThisWorkbook.Path & "\データ.xlsx"
  db.dbTableName = "Table1"
  recordData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "青山一郎", "男性", "AB", 30)
  db.AddRecord recordData()
  recordData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "池田葉子", "女性", "B", 50)
  db.AddRecord recordData()
End Sub

次に同じフォルダ下に置く"データ.xlsx"を開いてテーブル範囲に"Table1"と名前を付けます。テーブル範囲のセルを選択してから名前ボックス(数式バーの左側のセル選択範囲等を表示しているところ)にTable1と入力してください。以下のようになりますね。
f:id:HiroCom777:20200418135717j:plain
この状態で変更したプロシージャを実行すると・・・あ、エラーが出ずに実行されましたよ！！で、ファイル"データ.xlsx"を確認してみるとデータが追加されているのがわかると思います。テーブル範囲"Table1"はどうなっているでしょうか？
f:id:HiroCom777:20200418141004j:plain
あ、"Table1"で設定したテーブルの範囲が拡張されている！！つまりデータが追加されると、そのデータを含めた範囲にテーブルの範囲が書き換わるので、エラーにならなかったんですね。これならばデータの追加にも使用することが出来ます。

複数のテーブル間でのやりとり

　これは、一つのシートに複数のテーブルを指定できることを意味しています。1つのシートに複数の表があるのは好ましいことではありませんが、今回は動作を確認するという意味で1つのシートに複数のテーブルを配置して動作確認してみましょう。実際に使用する場合には別のシートにするなどして使ってみてください。まずはプロシージャですが、以下のプロシージャを空白シートの中に張り付けてください。

'テーブルからテーブルにデータをコピーする
Private Sub CopyDataToTable()
Dim recordData() As Variant
Dim recordData2() As Variant
Dim sqlCmd As String
Dim recordCount As Long
Dim fieldCount As Long
  Set db1 = New clsExcelDbase
  db1.dbFileName = ThisWorkbook.Path & "\データ.xlsx"
  db1.dbTableName = "Table1"
  
  Dim db2 As clsExcelDbase
  Set db2 = New clsExcelDbase
  db2.dbFileName = ThisWorkbook.Path & "\データ.xlsx"
  db2.dbTableName = "Table2"
  
  sqlCmd = "性別 = '女性'"
  recordCount = db1.SelectRecord(sqlCmd, Me.Range("A1"))
  fieldCount = UBound(db2.dbFieldList) + 1
 
  For i = 1 To recordCount
    recordData() = Me.Range(Me.Cells(i, 1), Me.Cells(i, fieldCount))
    recordData() = WorksheetFunction.Transpose(WorksheetFunction.Transpose(recordData))
    db2.AddRecord recordData()
  Next
End Sub

このプロシージャは、クラスモジュールから2つのオブジェクトを作り出してファイル"データ.xlsx"内の"Table1"、"Table2"の二つのテーブルにそれぞれ接続しています。"Table1"に接続したデータベースから特定の条件(今回は性別が女性)のレコードを抽出、空白シートにコピーして、"Table2"に接続したデータベースに書き込むといった動作をします。次に同じフォルダ内の"データ.xlsx"は以下のように設定します。
f:id:HiroCom777:20200418162503j:plain
"Table1"の範囲は前回の続きでB2:F7の範囲とします。"Table2"は"Table1"のB2:F2をコピーしてH2:L2に貼り付けて、この範囲を"Table2"と名前付けします。

それではプロシージャを実行してみましょう。以下のようになったと思います。
f:id:HiroCom777:20200418164455j:plain
女性のデータだけが選択されて"Table2"と設定した範囲の下に追加されていますね。そして、"Table2"の範囲がH2:L5に拡張されていることが確認できると思います。クラス化すると、(たとえ同じファイルの中でも)複数のデータベースに接続できて便利ですよね。

次回は、トランザクション機能の検証をしたいと思います。お楽しみに！！
hirocom777.hatenadiary.org

ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！