listbox data from a closed file

[alexanderd]

i have a listbox in an excel file on my desktop and want to open a
excel file
C:\A1.xls
having found the info on this site i still find difficulties in openin
said file
can any one point me in the correct direction to go forward
**********************************************************
From a closed workbook that is opened and closed again without the use
noticing it
With the macro below you can fill a ListBox control with input fro
another closed workbook.
The macro opens the closed workbook without the user noticing it, get
the values for the ListBox and closes
the source workbook without saving any changes. Copy the macro into th
UserForm's module sheet.
Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ListBox1
.Clear ' remove existing entries from the listbox
' turn screen updating off,
' prevent the user from seeing the source workbook bein
opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB
Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value
' get the values you want
SourceWB.Close False ' close the source workbook without savin
changes
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems

' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select th
first item
Application.ScreenUpdating = True
End With
End Sub

From a closed workbook by using ADO
With the macros below you can fill in an ListBox/ComboBox with inpu
from another workbook without opening it.
Copy the macros into the UserForm's module sheet.
Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an ope
workbook
Dim tArray As Variant
tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls"
"A1:B21")
FillListBox Me.ListBox1, tArray
Erase tArray
End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray A
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

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 i
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 databas
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