ADO - closed workbook - Named range

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

sorted it.

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

"MattShoreson" wrote:

>
> sorted it.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=376003
>
>