none
Problem using named range with ADODB recordset RRS feed

  • Question

  • I'm trying to query Excel file via ADODB. Some worksheets in this Excel file contain (.) in their name (i.e. "ABC." vs "DEF"). Also, all worksheets contain the same named ranges (i.e. MyDate). The following query works correctly.

    select * from [DEF$MyDate]

    but, this one doesn't

    select * from [ABC#$MyDate]

    I'm getting Microsoft Access database engine could not find object 'ABC.$MyDate'

    I can not rename worksheets because I'm not creating this Excel file in the first place.

    What syntax should I use to make the query work?

    Thursday, May 11, 2017 7:32 PM

All replies

  • The following code example which writes the sheet/named range to an array should help:

    Option Explicit
    Const strWorkbook As String = "E:\Path\Example.xlsx"        'The path of the workbook'
    Const strSheet As String = "Sheet1"        'The name of the worksheet/Range'
    
    Private Function xlFillArray(strWorkbook As String, _
                                 strRange As String, _
                                 bWB As Boolean) As Variant
    'Graham Mayor - http://www.gmayor.com - Last updated - 12 May 2017'
    Dim RS As Object
    Dim CN As Object
    Dim iRows As Long
    
        If bWB = True Then
            strRange = strRange & "$]"    'Use this to work with a named worksheet'
        Else
            strRange = strRange & "]"    'Use this to work with a named range'
        End With
        
        Set CN = CreateObject("ADODB.Connection")
    
        'Set HDR=NO for no header row'
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
        Set RS = CreateObject("ADODB.Recordset")
        RS.Open "SELECT * FROM [" & strRange, CN, 2, 1
    
        With RS
            .MoveLast
            iRows = .RecordCount
            .MoveFirst
        End With
        xlFillArray = RS.GetRows(iRows)
        If RS.State = 1 Then RS.Close
        Set RS = Nothing
        If CN.State = 1 Then CN.Close
        Set CN = Nothing
    lbl_Exit:
        Exit Function
    End Function
    


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, May 12, 2017 4:42 AM
  • Thank you Graham. You example helped me realize that I was using adLockOptimistic instead of adLockReadOnly. My code works fine once I switch to the latter.
    Friday, May 12, 2017 5:54 PM
  • Separately, would you happen to know if there is a way to get the address of a table (named range)? The named ranges that I am working with don't include a header row. Thus, I am trying to retrieve a header row separately, But, the named ranges can be anywhere on a worksheet. Thank you in advance.
    Friday, May 12, 2017 7:16 PM
  • The following will write the named range names into a list or combo box, but I suspect that is not what you require. If you want the physical addresses of the named range then I don't know if that is possible using ADODB

    Function xlListNamedRanges(strWorkbook As String, _
                          ListOrComboBox As Object, _
                          Optional PromptText As String = "[Select Range]") As String
    'Graham Mayor - http://www.gmayor.com - Last updated - 13 May 2017 '
    Dim CN As Object
    Dim RS As Object
    Dim tbl As Object
    Dim iTbl As Long
    Dim strTable As String
        Set CN = CreateObject("ADODB.Connection")
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        Set RS = CN.OpenSchema(20, Array(Empty, Empty, Empty, "Table"))
        ListOrComboBox.Clear
        Do While Not RS.EOF
            strTable = RS!TABLE_NAME
            If Not Right(strTable, 1) = "$" Then
                ListOrComboBox.AddItem RS!TABLE_NAME
            End If
            RS.MoveNext
        Loop
        If TypeName(ListOrComboBox) = "ComboBox" Then
            ListOrComboBox.AddItem PromptText, 0
            ListOrComboBox.ListIndex = 0
        End If
        CN.Close
        Set CN = Nothing
        Set RS = Nothing
        Set tbl = Nothing
    lbl_Exit:
        Exit Function
    End Function


    Graham Mayor - Word MVP
    www.gmayor.com


    Saturday, May 13, 2017 6:25 AM