ADO Connection.Excuteでxlsx出力

メモ

ファイルがなければ新規に作成される。
シート名をテーブルとして扱う。

Option Compare Database
Option Explicit

Public Const strCn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                               "Data Source={0};" & _
                               "Extended Properties=Excel 12.0 Xml;"

Function outputXlsxByADO(strFileFullName As String, _
                         strSheetName As String)
On Error GoTo ErrHnd
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .ConnectionString = Replace(strCn, "{0}", strFileFullName)
        .Open
        .Execute "CREATE TABLE " & _
                  strSheetName & _
                 "(F01 TEXT, F02 DATE)"
        .Execute "INSERT INTO " & _
                  strSheetName & _
                 "(F01, F02) VALUES ('abc', #12/31/2011#)"
    End With
Done:
On Error Resume Next
    cn.Close
    Set cn = Nothing
    Exit Function
ErrHnd:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume Done
End Function