如何在Excel VBA中使用ADO在.xlsx文件中创建新工作表/表格 [英] How to create a new sheet/table in an .xlsx file using ADO in excel vba

问题描述

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

Hi I'm trying to create a function that will store a range of data selected by a user along with a user's custom name and then using ADO the data will be stored in a new Excel sheet with the user's custom name as the sheet name. So far I've gotten an ADO connection working and can read and write data to the .xlsx file but when I try and create a new sheet by creating a new table I get an error that my sheet name is not correct. I've used test and testName and after digging around I am stumped. Here is a chunk of my code:

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 ，我得到以下查询输出：

So far I've printed out the query and it looks ok before execution. For example if the user chooses the name test I get the following query output:

创建表[test $]（[Col1] Float，[Col2] Float）

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

，然后显示运行时错误

[Microsoft] [ODBC Excel驱动程序]'test $'不是有效名称

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

我也搜索了该错误，但仍然可以找出为什么它不起作用。

I've searched that error also but still can find out why this isn't working. Any help is really appreciated!

推荐答案

下面的示例演示如何使用ADOX创建工作簿和添加工作表：

The below example shows how to create a workbook and add worksheets using 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

一些有用的链接：

关于ADOX

将ADOX与Excel数据配合使用

Microsoft ACE OLEDB 12.0连接字符串

这篇关于如何在Excel VBA中使用ADO在.xlsx文件中创建新工作表/表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！