How to Query Named Range on Sheet with Spaces in Name in Excel

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

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

Syntax for excel source specific range with spaces in sheet name

It could be a source problem of setting, in Excel Source Editor choose Data acces mode: SQL Command and write in SQL command text:

SELECT * FROM [Report Data 1$A3:K] 

Query sheet names with spaces using ADODB in VBA

As far as the space goes if Sheet 1 has a space between Sheet and 1 use

"select * from [Sheet 1$A1:A50]"

If there is a space in front of the sheet 1 aka. Chr(32) & Sheet 1 then it's impossible to select anything from that sheet using the [] syntax

however, if you don't want to change the original name of the spreadsheet you can create a temporary named range for the Range you want to pull the data from.

For example:

' add a temporary name
ThisWorkbook.Names.Add name:="tmp", RefersTo:=Sheets(" Sheet 1").Range("A1:C4")

' create your sql including the named range
sql = "SELECT * FROM tmp"

' open recordset
rs.Open sql, cn, adOpenUnspecified, adLockUnspecified

' remove the temporary name
ThisWorkbook.Names.Item("tmp").Delete

' copy rs to spreadsheet
ActiveSheet.Range("F2").CopyFromRecordset rs

The second question I don't understand so can you elaborate and I will update the answer?

How can I run SQL statements on a named range within an excel sheet?

You can just use the name.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range

rs.Open strSQL, cn

Debug.Print rs.GetString

In response to question part 2

I notice that you only want today's records, so you should be able to modify the sql to:

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _
& "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _
& Format(Date(),"yyyy/mm/dd") & "#"

You have not closed the connection:

cn.Close

And then

 Set rs=Nothing
 Set cn=Nothing

Trying to convert an excel named range of strings (text with spaces) into a string array

You are very close. Just a couple of quick things:

Sub test()
    Dim theRange As Variant
    Dim sArray() As String
    Dim i As Long

    Application.ScreenUpdating = False
    Application.Calculation = -xlCalculationManual
    Application.DisplayAlerts = False

    theRange = Sheets("Sheet2").Range("SecurityID").Value

    ReDim sArray(1 To 5)
    For i = 1 To 5
        sArray(i) = CStr(theRange(1, i))
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
End Sub

• Specify the sheet name with the Range (ActiveWorksheet is a reserved term)
• You only need to ReDim your destination array once, before your loop
• Setting the size = UBound(theRange) is not necessary
• Don't forget to re-enable screen updates, calcs, and alerts at the end

---

Related Topics