使用带有 VBA 的 ADO 对基本 Excel 2013 工作表作为表执行 SQL 查询会触发错误

Question

我正在客户端XLSm上使用32位2013 Excel开发模块。

我想使用工作表上的数据，就好像它是 Access 表一样。

有很多困难，我认为连接现在还可以。

不过，我有错误：3001 参数类型错误，超出可接受的范围。我无法理解的错误。

以下是VBA行的摘录：

此外，我在标题行下方的数据工作表中添加了 20 行，以允许 Excel 解释每列的类型。

    varCnxStr = "Data Source=" & G_sWBookREINVOICingFilePath & ";" &   "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=15';"
    With conXLdb
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Mode = adModeShareExclusive
        .Open varCnxStr
    End With
    strSQL = "SELECT * "
    strSQL = strSQL & " FROM [ReInvoiceDB$B2B5072] inum "
    strSQL = strSQL & " WHERE inum.InvoiceNum LIKE '1712*' "
    strSQL = strSQL & ";"
    '>> TRIGGERs ERROR with the current Where Clause !!'
    adoXLrst.Open strSQL, conXLdb, dbOpenDynamic, adLockReadOnly, adCmdText
    If adoXLrst.BOF And adoXLrst.EOF Then
        'no records returned'
        GoTo Veloma
    End If
    adoXLrst.MoveFirst
    Do While Not adoXLrst.EOF
        'Doing stuff with row'
        adoXLrst.MoveNext
    Loop
    sHighestSoFar = adoXLrst(1).Value '> just to try for RecordSet : Codes are not completed...
    sPrefixeCURR = Mid(sHighestSoFar, 1, 4)
    Highest = CInt(Mid(sHighestSoFar, 5))
    '> Increment >'
    Highest = Highest + 1
    HighestStr = sPrefixeCURR & Format(Highest, "00")
    strGSFNumber = HighestStr
    adoXLrst.Close
    conXLdb.Close
Veloma: 
    On Error Resume Next
    Set adoXLrst = Nothing
    Set conXLdb = Nothing
Exit Sub

等。

任何想法似乎有什么问题？

谢谢

Answer 1

下面是一个我成功使用的旧例子。请注意，书中的工作表名称是 Sheet1 和 Sheet2，但在查询中我必须使用 sheet1$ 和 sheet2$。我注意到你的工作表名称中间有$符号。也许这就是问题所在？

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$]  inner join [Sheet2$] on [Sheet1$].test1 = [Sheet2$].test1  SET [Sheet1$].test3 = [Sheet2$].test2 ")
    Set rs = Nothing
    Set con = Nothing
End Sub

Answer 2

给出关于要实现的整个模块的更多详细信息：它是执行一个事务单元。

此事务将包括 3 个操作：从列(发票编号(中获取最大值以递增它，在 Access 表中记录新数字(通过 DAO(，相同的 Excel 文件(通过 ADO(并在 HDD 上生成文档。

因此，它的目标是将Excel文件用作表，而不是使用Windows脚本或Excel VBA操作的文件。我的最终用户对 Excel 打开文件操作的弹出窗口感到不安。作为一名开发人员，我对在事务会话中尽可能多地使用 SQL 语句感到更舒服。这也是你的意见吗？