how to use VBA ADODB connection to connect a closed workbook to get named range with character \

Question

I have a workbook with a named range. Its name is A\B.

I would like to use Adodb connection and recordset to get the value of the named range. In order to do that, I need to first open recordset with that named range.

This method works fine on other named range if their names do not include \.

Public Sub test()
    Dim Path As String
    Path = "D:\New.xlsx"
    Dim cn As New ADODB.Connection
    cn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
    Dim rst As New ADODB.Recordset
    Dim namedRange As String
    namedRange = "A\B"
    'namedRange = Replace(namedRange, "\", ".")
    With rst
        .Open Source:="SELECT * FROM [" & namedRange & "]", _
              ActiveConnection:=cn, _
              CursorType:=adOpenStatic, _
              LockType:=adLockReadOnly, _
              Options:=adCmdText
    End With
End Sub

I tried to replace \ with other characters to test if it is caused by illegal character. The characters I tested are ~!@#$%^&*()_+-={}|[]:";'<>?,./, space and double \.

I know nothing of SQL.

If someone can tell me how it can be done, it is very much appreciated.

Thank you.

Answer 1

EDIT: I apologize, I found a reference from Microsoft that clearly says, "don't do that": https://learn.microsoft.com/en-us/office/troubleshoot/access/error-using-special-characters

ADODB driver is using MSAccess libraries so any rules you find for MSAccess apply to your situation. If you look at the section titled Problem 3 where the slash character is cited, the answer is "To work around this problem, do not use special characters."

Try adding single quotes around the name:

With rst
    .Open Source:="SELECT * FROM ['" & namedRange & "']", _
          ActiveConnection:=cn, _
          CursorType:=adOpenStatic, _
          LockType:=adLockReadOnly, _
          Options:=adCmdText
End With