ExcelVBA-Excelファイルをデータベースとして使う---の2回目です。
前回の記事はこちらです。
今回は、データの追加を実行するプロシージャAddRecordについて説明していきたいと思います。
Microsoft ActiveX Data Objects 2.8 Libraryを参照設定して試してみてください。
ソースリストは此方です。
Public Function AddRecord(strFileName As String, strSheetName As String, vrnField() As Variant, vrnData() As Variant) As Boolean
On Error GoTo ErrorHandler
Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection")
With adoCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0"
.Open strFileName
End With
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")
With adoRs
.Open "[" & strSheetName & "$]", adoCn, adOpenKeyset, adLockOptimistic
.AddNew vrnField, vrnData
.Update
End With
adoRs.Close
adoCn.Close
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "データベース接続に失敗しました"
Else
AddRecord = True
End If
End Functionまずは、簡単に動かしてみましょう。簡単な名簿のデータベースを作ってみます。
Private Sub AddData()
Dim vrnField() As Variant
Dim vrnData() As Variant
vrnField() = Array("登録日時", "氏名", "性別", "血液型", "年齢")
vrnData() = Array(Format$(Now, "YYYY/MM/DD hh:mm:ss"), "山田 太郎", "男性", "O", 25)
If AddRecord(ThisWorkbook.Path & "\データ.xlsx", "Sheet1", vrnField(), vrnData()) Then
MsgBox "登録しました"
Else
MsgBox "登録に失敗しました"
End If
End SubこのプロシージャAddDataを実行すると名簿ファイルは以下のようになっているはずです。
次に、プロシージャAddRecordのポイントを解説します。
引数として使用する変数は以下の通りです。
strFileName データベースのファイル名
strSheetName データベースファイル内のアクセスするシート名
vrnField() データベースのフィールド名が入る配列
vrnData() データベースに書き込むデータが入る配列
このプロシージャはADODB.Connectionオブジェクトを使用してExcelファイルをデータベースとして接続してデータを追加していきます。
基本はAccessファイルに接続する場合と同じなのですが(https://tonari-it.com/excel-vba-access-database-connection/)
ポイントは、Properties("Extended Properties") の指定。"Excel 12.0"とするとExcel2007以降で作成したブックと接続することができます。
また、Excelファイルの場合シートをデータベースのテーブルとして扱います。(セル範囲での指定もできるようですが、ここでは複雑になるので割愛します)その際には、
シート名の最後に"＄"をつけて"[]"で囲みます。
その他は、Accessデータベースに接続する方法と同じですね。
因みに、Excelファイルを対象とした場合レコードの削除はできないようです。
この辺は運用方法を工夫する必要があります。
次回は、データベースらしくSQLを使ったデータの検索を説明したいと思います。
hirocom777.hatenadiary.org
