はじめに

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)を構成する機能の一つであり、データベース内のテーブルなどの構成の確認や変更を行えるライブラリです。

docs.microsoft.com

しかし、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

動作イメージ

適当に撮ってみたはいいものの、中の処理の違いを気にしなければ、「ふーん」で終わってしまいそうな感じではありますね

↓Sample.xlsxというExcelファイルを作成して、それにレコードを追加していくイメージです。 pic.twitter.com/EC4CvvTSlS
— いみひと (@nukie_53) April 13, 2020

imihitoのブログ

ADODB・ADOXを使ってxlsxファイルを作成してみるサンプル