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.

Peppery

Using Excel 2013 I have solved the problem by introducing a regular range (not a named range) AND employing a colon (":"). E.g. [Report 1$A15] would not work, but [Report 1$A15:A15] works. – Pedicab

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 + "$";

Homoousian

Thank you. I was having the same issue as the OP and I can confirm removing the surrounding quotes in cases where you're selecting a range works. – Bosk

Answer 2

The name of the sheet with spaces followed by named range can be written as ['My Sheet$'MyData]

Here is how you get to list the tables contained in the workbook

1) Code to get the list of tables in the workbook

dim i as Integer

Set objRecordSet = objConn.OpenSchema(adSchemaTables)
Do While Not objRecordSet.EOF
    i = 1
    For i = 0 To objRecordSet.Fields.Count - 1
        Debug.Print objRecordSet.Fields(i).Name, objRecordSet.Fields(i).Value
    Next

    objRecordSet.MoveNext
Loop

EDIT: For your scenario, it will be

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

EDIT2: I am sorry, I pasted the wrong code the first time. Please use the above code in the listing 1 and look for TABLE_NAME in the immediate window. The listing of named ranges prefixed with sheet name will be shown against TABLE_NAME (on which you can query).

Also, make sure that the named range is scoped to the worksheet. Make sure that the casing of the sheet name and range name matches with query.

Creatural

That is what I would assume to be correct. However, it still throws the same error when I use that SQL. Same with the ' before the $. I'm starting to think this is actually a bug in the driver. If I rename the sheet to "WithSpaces" and use "Select * from ['WithSpaces$'Ingredients]" it works fine. Rename to "With Spaces" and use "Select * from ['With Spaces$'Ingredients]" it throws the error. – Peppery

@user2229491: Check the modified answer and add your comments, if it didn't help. Thanks. – Creatural

Using the code above (or through adox.catalog.tables) it gives the name you would expect 'With Spaces$'Ingredients. But using that in the query still throws the error. Also, I'm sure it's not an issue with scoping or mistyping, etc, because I can remove the space (in actual sheet name and in my code) and it works fine. Put it back in and it throws error. – Peppery

@user2229491: Goto "Name Manager" and check if the Ingredients named range is scoped to worksheet – Creatural

it is scoped to the sheet. as I mentioned, I can get the named range out of the adox.catalog object and schema, so it's certainly there. – Peppery

@user2229491: I don't know what else could be wrong. The last thing you should check is whether the uppercase/lowercase thing is taken care of. i.e. the name of the sheet is the same case as the variable. Also, see if there is any leading/trailing spaces in the name of the sheet etc. I am sorry, I cant help much because I tried the code and it worked on XL 2010. – Creatural

Please see example spreadsheet linked in comments in answer below. I created this on Excel 2007, and have the issue as well. When I run schema example above to extract tables, I see two tables with "Ingredients" as the name. Neither refer to the sheet even though in name manager it shows scope to be worksheet for each. – Peppery

Which method didnt work? What ADO library are you referring to in "References.."? – Creatural

What version of ADO is it? 2.8? Also, take a look at connectionstrings.com/excel-2007. – Creatural

there are comments in the example I linked, but the method of particular interest is "Excel2007_WithSpaces_DoesntWork_AndDoesntWorkInExcel2013Either". I've tried late binding with no references, and specifically referencing ADO 2.8 Library, ADO Ext 2.8 for DLL and Security Library, and Recordset 2.8 Library. connectionstrings.com is where I got the connection string I'm using. – Peppery

The code of "Excel2007_WithSpaces_DoesntWork_AndDoesntWorkInExcel2013Either" method runs & shows a message box with value 10. The last thing to check would be OLE DB drivers. – Creatural

Answer 3

Below query would work. Just make sure the named range Ingredients exist in sheet With Space. Also save the workbook.

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

Alternatively you can use below

strQuery = "Select * from [With" & Chr(32) & "Spaces$Ingredients]"

Americanize

that was the first thing I tried...see list of queries from my original post. Chr(32) doesn't make a difference unfortunately. – Peppery

@Peppery Its working at my end. Which version of excel you are using? – Americanize

The microsoft access database engine could not find the object ''With Blanks$'Ingredients'. Make sure the object exists and that you spell its name and the path name correctly. – Peppery

@Peppery the error message itself is self explanatory.Can u convert the file in 2010 version and try it. Also can u upload the file and give us the link. – Americanize

I created another example on my computer at work (which uses Excel 2007). Still having the issue (and oddly, same issue with NoSpaces). Created easy to run examples of each, just download and edit the file location. Please report back with any feedback. db.tt/3lEYm2g1 – Peppery

@Peppery I tried on excel 2007 and excel 2010 and its working. Try using late binding for Connection and recordset object like CreateObject("ADODB.Connection") . If its not working try replacing your ado files from other machhine C:\Program Files\Common Files\System\ado. You may also run the code on other machine which may help you to figure out the issue. – Americanize

I tried it on 2 different machines with excel 2007 and it doesn't work on either for me. Late binding made no difference. If my example sheet works for all functions for you, then I'm at a loss. – Peppery

Answer 4

Another late entrance to the party...

I couldn't get any of the responses here to work for the entire sheet, so I made a named range for the whole sheet (select all cells and give them a name - I called them POList) and referred to that thus:

UPDATE [POList] SET..... etc

So no single quotes, no backticks, no $ sign, not even the sheet name.

Having said that, the workbook in question only has the one sheet (which DOES have spaces in the name).

This works using Excel 2002 (!) and the following connection code

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=C:\Purchase Req No. List.xls; Extended Properties=Excel 8.0;"
    .Open
End With

Obviously this won't work for everyone's situation and is a bit of a kludgey workaround, but maybe someone will find it useful...

Acklin

Answer 5

I was having this same issue and was able to solve without a named range. In addition, as a two-fold part of what my issue was, make sure there are no trailing spaces in the worksheet name. Try...

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

Parette

Answer 6

If anyone is using this in query sql this work for me

select * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0 Xml;Database=YourPath\YourFileName.xlsx;',['name name$']);

sheet name is "name name" just put name between single quote

Turnout