ADO - closed workbook - Named range

[MattShoreson]

Looked in the tips section and found the code at beneath...

..it doesn't return a bean.
It's fine apart from the range part!!! Anyone got any ideas?

Option Explicit

Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an open
workbook
Dim tArray As Variant
tArray = ReadDataFromWorkbook("C:\Test\Broker Disclosure\RD.xls",
"LIST")
FillListBox Me.ListBox1, tArray
Erase tArray
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, _
SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
' this function can only return data from the first worksheet in
SourceFile
' if SourceRange is a defined name reference:
' this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = _
ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls",
"DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
dbConnectionString = _
"DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" &
SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database
connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rs.GetRows
' returns a two dim array with all records in rs
dbConnection.Close ' close the database connection
rs.Close
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Function
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
Set rs = Nothing
Set dbConnection = Nothing
End Function

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As
Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
With lb
..Clear
For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
..AddItem
For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray,
1)
..List(r, c) = RecordSetArray(c, r)
Next c
Next r
..ListIndex = -1 ' no item selected
End With
End Sub

 

[MattShoreson]

sorted it

 

[Aonghus]

Hey Matt, I am doing the same thing here but I have difficulty with the range
name as you did, could you tell me how you fixed it. I want my range to be
"usedrange" of a worksheet. I've only been working with macros for a few
months now so my programming abilities are a bit basic,
Cheers