Unable to query named range on sheet with spaces in name in Excel

Question

I have a workbook with multiple sheets, and each sheet has the same set of named ranges (IE they are scoped to the sheet, not workbook).

I want to query based on a named range on any of the sheets. Some sheets have names with no spaces, and others do have names with spaces.

I can easily do this for the ones with no space, but the syntax for doing this with spaces escapes me (and an hour of google-ing).

The named range is "Ingredients" and one sheet is named "NoSpaces", the other "With Spaces"

Here's the code that works fine for "NoSpaces" sheet:

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dictNewRecipesToCheck(arrKeys(0)) & ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""
strQuery = "Select * from [NoSpaces$Ingredients]"
Set objConn = New ADODB.Connection
Set objRecordSet = New ADODB.Recordset
objConn.Open sConnString
objRecordSet.Open strQuery, objConn

I've tried all the following for the "With Spaces" sheet:

strQuery = "Select * from [With Spaces$Ingredients]"
strQuery = "Select * from ['With Spaces'$Ingredients]"
strQuery = "Select * from ['With Spaces$'Ingredients]"
strQuery = "Select * from [With_Spaces$Ingredients]"

Every time, I'm getting "The Microsoft Access database engine could not find the object ..." error.

As as I mentioned, it works fine for all sheets that don't have spaces in the name.

Any help to get this working on sheets with spaces, would be MUCH appreciated.

Thanks!

UPDATES BASED ON COMMENTS BELOW:

Excel 2007

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""

When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as just "Ingredients" for both named ranges (even though each are scoped to a different sheet).
With this driver, even [NoSpaces$Ingredients] doesn't work.

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1;"""

When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as "NoSpaces$Ingredients" and "'With Spaces'$Ingredients". With this driver, [NoSpaces$Ingredients] works fine (it didn't with ACE driver).
However, using the exact name as reported by schema, ['With Spaces'$Ingredients] doesn't work.

Excel 2013

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileLoc & ";Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"""

When run through the schema code provided by @shahkalpesh it lists TABLE_NAME as "NoSpaces$Ingredients" and "'With Spaces$'Ingredients". With this driver, [NoSpaces$Ingredients] works fine, but ['With Spaces'$Ingredients] doesn't work.

Finally, please refer to http://db.tt/3lEYm2g1 for an example sheet created in Excel 2007 that has this issue on (at least) 2 different machines.

Answer 1

Would it be possible to use an excel range instead of named range? I got the following to work:

SELECT * FROM [Report 1$A4:P]

I'm getting the sheet name from the GetOleDbSchemaTable() method and removing the apostrophes. The sheetname with apostrophes does not work for me with a range.

if (tableName.Contains(' '))
            tableName = Regex.Match(tableName, @"(?<=')(.*?)(?=\$')", RegexOptions.None).Value + "$";