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…

Accepted Answer

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) & "]`

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

Solution

Problem