Microsoft Access database engine could not find the object Worksheet - ACE OLEDB

Question

I am trying to do a Join on 3 tables in Excel through VBA using Microsoft.ACE.OLEDB.12.0. Having lots of issues trying to get the query to run. At this point I get the following error:

Run-time error '-2147217865 (800040e37)':

The Microsoft Access database engine could not find the object 'CustomSheetName1$A$1:$AV$6027'. Make sure the object exists and that you spell its name and the path name correctly. If 'CustomSheetName1$A$1:$AV$6027' is not a local object, check your network connection or contact the server administrator.

The source file is created in the same sub and saved to the macro root folder located locally in C:\Users\localuser\Documents\MacroFolder\. I have full access to the file.

When run the connection string shows as:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\localuser\Documents\MacroFolder\Book4.xlsx;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1';"

Extract of sub below. I have obfuscated the field names and only included code I thought was relevant. Can add more and clarify further if required. Code breaks at the last line when executing the query.

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

Any help would be greatly appreciated. Regards,

Answer 1

When reading Excel worksheets via ADO, the $ sign is appended to the end of the worksheet name, like this:

SELECT * FROM [Sheet1$]

Using the absolute range address adds extra $ signs that cause the worksheet name to be interpreted incorrectly. You need to use non-absolute range addresses to stop this happening. Adding some parameters to UsedRange.Address can fix this:

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