excel - Microsoft Access 数据库引擎找不到对象工作表 - ACE OLEDB

Question

我正在尝试使用 Microsoft.ACE.OLEDB.12.0 通过 VBA 对 Excel 中的 3 个表进行联接。尝试运行查询时遇到很多问题。此时我收到以下错误:

运行时错误“-2147217865 (800040e37)”:

Microsoft Access 数据库引擎找不到对象“CustomSheetName1$A$1:$AV$6027”。确保该对象存在并且其名称和路径名称拼写正确。如果“CustomSheetName1$A$1:$AV$6027”不是本地对象，请检查您的网络连接或联系服务器管理员。

源文件在同一个子目录中创建，并保存到本地位于C:\Users\localuser\Documents\MacroFolder\ 的宏根文件夹中。我拥有该文件的完全访问权限。

运行时连接字符串显示为:

“Provider=Microsoft.ACE.OLEDB.12.0;数据源=C:\Users\localuser\Documents\MacroFolder\Book4.xlsx;扩展属性='Excel 12.0 Xml;HDR=Yes;IMEX=1 ';"

以下子节摘录。我混淆了字段名称，只包含我认为相关的代码。如果需要，可以添加更多内容并进一步澄清。执行查询时代码在最后一行中断。

Dim wbTarget As Workbook, wsTarget As Worksheet
Dim wb As Workbook, ws As Worksheet
Set wbTarget = Workbooks.Add

Set wsTarget = wbTarget.Sheets.Add(After:=wbTarget.Sheets(wbTarget.Sheets.Count))
wsTarget.Name = "CustomSheetName1"

varFilePathElements = Split(ThisWorkbook.Path, "\")
strFileName = varFilePathElements(UBound(varFilePathElements))
Dim strWBTargetFullFileName As String
strWBTargetFullFileName = Replace(ThisWorkbook.Path, "strfilename", "") & "\" & wbTarget.Name & ".xlsx"

Dim cn As Object
Dim rs As Object
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strWBTargetFullFileName _
    & ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';"

strSQL = "SELECT " _
            & "sh1.company_name, " _
            & "sh1.company_type, " _
            & "sh1.customer_no, " _
            & "sh1.fk1, " _
            & "SUM(sh3.total_stat) as total_stat, " _
            & "FROM ( [CustomSheetName1" & wbTarget.Sheets("CustomSheetName1").UsedRange.Address & "] sh1 " _
            & "LEFT JOIN [CustomSheetName2" & wbTarget.Sheets("CustomSheetName2").UsedRange.Address & "] sh2 " _
            & "ON sh2.fk1 = sh1.fk1 ) " _
            & "LEFT JOIN [CustomSheetName3" & wbTarget.Sheets("CustomSheetName3").UsedRange.Address & "] sh3 " _
            & "ON sh3.fk2 = sh2.fk2 AND sh3.fk3 = sh2.fk3 " _
            & "GROUP BY sh1.customer_no, sh1.company_name, sh1.company_type, sh1.fk1 " _
            & "ORDER BY total_stat"

wbTarget.Sheets(1).Range("A1").Value2 = strSQL
wbTarget.SaveAs (strWBTargetFullFileName)
wbTarget.Close

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

rs.Open strSQL, cn, 3, 3

任何帮助将不胜感激。问候，

Answer 1

通过 ADO 读取 Excel 工作表时，$ 符号会附加到工作表名称的末尾，如下所示:

从 [Sheet1$] 中选择 *

使用绝对范围地址会添加额外的 $ 符号，从而导致工作表名称被错误解释。您需要使用非绝对范围地址来阻止这种情况发生。向 UsedRange.Address 添加一些参数可以解决此问题:

[CustomSheetName1$"& wbTarget.Sheets("CustomSheetName1").UsedRange.Address(False, False) & "]