How to grab the value of a Named Range (Named Cell) from another worksheet using ADO?

Hello,

I am using Excel 2013. I need to grab the value of a named cell from another workbook (without opening it). I've searched and read A LOT! I've tried, but I cannot get it to work. Here's what I have so far:

Sub ConnectToExcelUsingSheetName()


On Error GoTo ConnectToExcelUsingSheetName_Err

' Declare variables
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim query As String

' Open the connection then open the recordset
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Temp\PM Workbook - Test.xlsm;" & _
"Extended Properties=""Excel 12.0 Xml;IMEX=1"";"

' Notice I dropped "HDR" to see if that would make a difference as the named cell does not use not require a header.
' "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
' "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"



query = "SELECT * FROM Named_Cell_A1" 'this should work according to what I've read online
'query = "SELECT * FROM [Named_Cell_A1]" 'tried this is case I needed the brackets... still does not work. No trailing "$" so it's not a sheet... it's a named cell


rs.Open query, cnn, adOpenStatic, adLockReadOnly
'rs.Open "SELECT * FROM Named_Cell_A1;", cnn, adOpenStatic, adLockReadOnly
'rs.Open query, cnn

'The following part does NOT WORK. Seems the named range was not ready??

'Worksheets("Sheet1").Range("B1").CopyFromRecordset rs
Sheet1.Range("B1").CopyFromRecordset rs
MsgBox "B1 = " & Sheet1.Range("B1").Value



'Debug.Print rs

' Tidy up
ConnectToExcelUsingSheetName_Exit:
On Error Resume Next
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub

ConnectToExcelUsingSheetName_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume ConnectToExcelUsingSheetName_Exit

End Sub



I know others are using this ADO technique. I must be doing something basic incorrectly. Any help is greatly appreciated! Thank you!

NJDevil