はじめに
VBAでは、Microsoft ActiveX Data Objects X.X Library(ADODB)を使うことで、既存のデータベースに接続して情報を取得したり、SQLを実行できます。
SQL Serverのようなちゃんとしたデータベースだけでなく、ExcelのブックなどもAccessのエンジン経由でデータベースとして扱うことができ、CREATE TABLE
などのSQLを実行することでシートの追加が可能です。
この記事では、SQLを使わず、Microsoft ADO Ext. X.X for DDL and Security(ADOX)を使って、Excelのブックを作成する方法のメモとなります。
ADOX とは?
ActiveX Data Objects(ADO)を構成する機能の一つであり、データベース内のテーブルなどの構成の確認や変更を行えるライブラリです。
ADOX の基礎 - SQL Server | Microsoft Docs
しかし、ADOの中核であるADODB
ライブラリに比べると、比較的重要度は低いものになります。
なぜかと言うと、ADOX
で行える操作はSQL(CREATE TABLE
など)がわかっていればADODB
だけでも実行可能な操作だからです。
その上でADOXを使用するメリットとしては「SQLの構文を知らなくても操作ができる」・「動的に構成を組み立てられる」といったところになります。
操作の流れ
ADOX.Catalogと対象のデータベースを紐付ける
ADOXを使うとは言っても、まずはADODBで(明示的にせよ・暗黙的にせよ)接続を行う必要があります。
CreateObject("ADOX.Catalog")
などでCatalogオブジェクトを作成した後、Create
メソッドに接続文字列を指定したり、ActiveConnection
プロパティに既存の接続を設定することで紐付けを行えます。
接続文字列などはADODBの基本的な操作であり、多くの記事で紹介されているため、この記事では割愛します。
紐付けすることで、データベース(今回はブック)内部の(ADOとしての)テーブル(ADOX.Table
)と認識されるものを管理できるようになります。
Catalogにテーブルを追加
Catalog内のテーブルの一覧(Catalog.Tables)に、自分で作成・定義したADOX.Table
を追加することで、テーブルの追加を行えます。
Excelの場合、テーブルを追加するとそのテーブルの名前のシートと名前付き範囲が追加されます。
ADOX.Table
もCreateObject("ADOX.Table")
などで作成できるため、作成後に名前(Nameプロパティ)や、列の設定(Columns)を行っていきます。
列の追加はTable.Columns.Append "列名", 型
でも行えますし、
CreateObject("ADOX.Column")
で作成したColumnオブジェクトを追加することもできます。
サンプル
Public Declare PtrSafe Sub Sleep Lib "kernel32.dll" ( _ Optional ByVal dwMillisecond As Long = 0 _ ) '以下の二つの参照設定が必要。 'Imports ADODB = Microsoft ActiveX Data Objects X.X Library 'Imports ADOX = Microsoft ADO Ext. X.X for DDL and Security Sub Sample_CreateXlsxByADOX() '動作の流れ '1. ブックへの接続を確立 '2. ブック内の(ADOとしての)テーブルの作成 or 取得 '3. テーブルへの情報の設定 '1. ブックへの接続を確立 '作成・追記するExcelブックへのパス(動作確認は.xls, .xlsx, .xlsbのみ)。 Dim destPath As String destPath = VBA.Interaction.Environ$("USERPROFILE") & "\Documents\新しいフォルダー\sample.xlsx" 'Excelのブックへの接続を用意。 Dim cnn As ADODB.Connection Set cnn = CreateAceExcelConnection(destPath) '2. ブック内の(ADOとしての)テーブルの作成 or 取得 'データソース内の情報を管理するオブジェクトを作成。 Dim ctlg As ADOX.Catalog Set ctlg = VBA.Interaction.CreateObject("ADOX.Catalog") '上記で作成した接続と紐付け。 Set ctlg.ActiveConnection = cnn '作成・追記する範囲。 '指定した名前付範囲があればそれを、無ければ新規シートが作成される。 Dim tableName As String tableName = "Test" 'tableNameと一致するテーブルを探す。 Dim tbl As ADOX.Table For Each tbl In ctlg.Tables If tbl.Name = tableName Then _ Exit For 'TODO:Excel独自動作もあるため、よく動作検証すること。 Next tbl If tbl Is Nothing Then 'テーブルが見つからなかったら定義する。 Set tbl = VBA.Interaction.CreateObject("ADOX.Table") tbl.Name = tableName With tbl.Columns .Append "秒", adInteger .Append "文字", adLongVarWChar End With ctlg.Tables.Append tbl End If '閉じる必要は無いけれど、動きを見せるために一旦閉じてブックを開く。 cnn.Close Shell "explorer.exe " & destPath Sleep 5000 Set cnn = CreateAceExcelConnection(destPath) '3. テーブルへの情報の設定 Dim rs As ADODB.Recordset Set rs = VBA.Interaction.CreateObject("ADODB.Recordset") rs.Open tbl.Name, cnn, adOpenForwardOnly, adLockOptimistic Dim i As Long For i = VBA.Strings.AscW("A") To VBA.Strings.AscW("z") rs.AddNew With rs.Fields .Item(0) = Second(Time) .Item(1) = VBA.Strings.String$(i / 5, i) End With rs.Update Sleep 300 Next i cnn.Close End Sub 'inDataSourceをExcelのファイルと見なして接続するADODB.Connectionを作成する。 '「Microsoft.ACE.OLEDB.12.0」を使用するため、Accessのエンジンが必要。 '引数 'inDataSource :対象のExcelブックのパス(実行時に存在していなくても可)。 'inMode :接続モードを指定する。既定値はadModeShareDenyNone。 'inCursorLocation :カーソルの管理をどちらがするかを指定する。既定値はadUseServer。 '返り値 '引数の状態を元にOpenされたADODB.Connection。 Function CreateAceExcelConnection( _ inDataSource As String, _ Optional inMode As ADODB.ConnectModeEnum = ADODB.ConnectModeEnum.adModeShareDenyNone, _ Optional inCursorLocation As ADODB.CursorLocationEnum = ADODB.CursorLocationEnum.adUseServer _ ) As ADODB.Connection Dim cnn As ADODB.Connection Set cnn = VBA.Interaction.CreateObject("ADODB.Connection") 'Accessのエンジンを使用する。 cnn.Provider = "Microsoft.ACE.OLEDB.12.0" With cnn.Properties '接続先のファイルパス(この時点で存在していなくてもOK)。 .Item("Data Source").Value = inDataSource 'ISAM形式などを指定する(Excelの場合、ヘッダー行のチェックなども指定できる)。 'ExcelのISAM形式は既存の物を読み込む場合は、多少適当でもいいけど '新規作成時は適したタイプを指定する必要がある。 .Item("Extended Properties").Value = GuessIsamTypeByExtension(inDataSource) End With cnn.CursorLocation = inCursorLocation cnn.Mode = inMode cnn.Open Set CreateAceExcelConnection = cnn End Function 'inDataSourceの拡張子からISAMタイプを推測する。 Function GuessIsamTypeByExtension(inDataSource As String) As String Dim retIsamType As String Dim fso As Object 'As Scripting.FileSystemObject Set fso = VBA.Interaction.CreateObject("Scripting.FileSystemObject") Select Case VBA.Strings.LCase$(fso.GetExtensionName(inDataSource)) Case "xls": retIsamType = "Excel 8.0" Case "xlsx": retIsamType = "Excel 12.0 Xml" Case "xlsm": retIsamType = "Excel 12.0 Macro" Case "xlsb": retIsamType = "Excel 12.0" Case "xlt", "xla", "xltx", "xlam" 'TODO:未確認(新規作成は不可) Err.Raise 5, , "Not supported type." & inDataSource Case Else 'TODO:Accessやcsv、txtなどの対応。 Err.Raise 5, , "Not supported type." & inDataSource End Select Let GuessIsamTypeByExtension = retIsamType End Function
動作イメージ
適当に撮ってみたはいいものの、中の処理の違いを気にしなければ、「ふーん」で終わってしまいそうな感じではありますね
— いみひと (@nukie_53) April 13, 2020
↓Sample.xlsxというExcelファイルを作成して、それにレコードを追加していくイメージです。 pic.twitter.com/EC4CvvTSlS