Geek asks and answers

Cannot query named range on worksheet with spaces in name in Excel

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

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

I can easily do this for those who don’t have a place, but the syntax for this with spaces eludes me (and google-ing hour).

The named range is “Ingredients,” and one sheet is called “NoSpaces,” the other is “With Spaces”

Here's the code that works great for the No Spaces 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 tried all of 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]"

" Microsoft Access ...".

, , .

, , .

!

, :

Excel 2007

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

, @shahkalpesh, TABLE_NAME "" ( ). [NoSpaces $Ingredients] .

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

, @shahkalpesh, TABLE_NAME "NoSpaces $Ingredients" " " ". [NoSpaces $Ingredients] ( ACE).
, , , ['With Spaces' $Ingredients] .

Excel 2013

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

, @shahkalpesh, TABLE_NAME "NoSpaces $Ingredients" "With Spaces $" Ingredients ". [NoSpaces $Ingredients] , ['With Spaces' $Ingredients] .

, , http://db.tt/3lEYm2g1 , Excel 2007, ( ) .

+5
5

excel ? :

SELECT * FROM [Report 1$A4:P]

GetOleDbSchemaTable() . .

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

, , ['My Sheet $' MyData]

,

1) ,

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:

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

EDIT2: , . 1 TABLE_NAME . TABLE_NAME ( ).

, , . , .

0

. , . .

strQuery = " * [ $Ingredients]"

strQuery = " * [" Chr (32) " $" "

0

...

- , , ( - POList) ,

UPDATE [POList] SET..... etc

, , $, .

, ( ).

Excel 2002 (!)

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

, , , , - ...

0

. , , , . ...

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

More articles:


All Articles