vba - 如何在 excel vba 中使用 ADO 在 .xlsx 文件中创建新工作表/表格

Question

您好我正在尝试创建一个函数，该函数将存储用户选择的一系列数据以及用户的自定义名称，然后使用 ADO 将数据存储在一个新的 Excel 工作表中，并将用户的自定义名称作为工作表名称。到目前为止，我已经使 ADO 连接正常工作，并且可以读取数据并将数据写入 .xlsx 文件，但是当我尝试通过创建新表来创建新工作表时，我收到一个错误，表明我的工作表名称不正确。我用过test和 testName在四处挖掘之后，我被难住了。这是我的一段代码:

Sub AddSheet()

    Dim DataName As String, SRange As Variant, qry As String, SCols As Integer, SRows As Integer

    DataName = InputBox("Enter Your Data Name:")
    Set SRange = Application.Selection
    Set SRange = Application.InputBox("Select your data to be saved:", xTitleId, SRange.Address, Type:=8)
    SCols = SRange.Columns.Count 'new
    SRows = SRange.Rows.Count 'new
    'creates the query to create a new sheet/table for the data
    qry = "CREATE TABLE [" & DataName & "$] ("
    For i = 1 To SCols
        qry = qry & "[Col" & i & "] Float"
        If i <> SCols Then
            qry = qry & ", "
        End If
    Next i
    qry = qry + ")"
    SQLUpdateData qry

End Sub

'function that executes the SQL query
Function SQLUpdateData(qry As String) As Variant

    Dim FileName As String, sconnect As String
    Dim cnn As New ADODB.Connection
    Dim objMyCmd As ADODB.Command

    Set cnn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    FileName = "c:\Users\" & Environ("Username") & "\AppData\Roaming\Microsoft\AddIns\DataStorage.xlsx"
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & FileName & ";HDR=Yes';"
    cnn.Open sconnect
    objMyCmd.CommandType = adCmdText
    objMyCmd.CommandText = qry
    objMyCmd.ActiveConnection = cnn
    MsgBox qry
    objMyCmd.Execute
    Set objMyCmd = Nothing
    Set cnn = Nothing

End Function

到目前为止，我已经打印出查询，并且在执行之前看起来还可以。例如，如果用户选择名称 test我得到以下查询输出:

CREATE TABLE [test$] ([Col1] Float, [Col2] Float)

然后是运行时错误说明

[Microsoft] [ODBC Excel Driver] 'test$' is not a valid name

我也搜索了那个错误，但仍然可以找出为什么这不起作用。非常感谢任何帮助!

Answer 1

下面的示例展示了如何使用 ADOX 创建工作簿和添加工作表:

Option Explicit

Sub Test()

    ' Add reference
    ' Microsoft ADO Ext. 6.0 for DDL and Security

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.xlsx;Extended Properties=Excel 12.0 Xml"
    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adVarWChar
    End With
    tbl.Columns.Append col
    cat.Tables.Append tbl

End Sub

一些有用的链接:

About ADOX

Using ADOX with Excel Data

Microsoft ACE OLEDB 12.0 connection strings