From another open workbook

It's possible to set the RowSource-property for a ListBox- or ComboBox-control to get data from another workbook by filling in something like this:

'[Filename.xls]Sheet1'!$B$1:$B$15

The other workbook must be open for the control to display any values. With the macro below you  can fill a ListBox-control with data from another closed workbook. The macro opens the other workbook without the user noticing it, fills the ListBox with data and closes the workbook without saving any changes. This macro is supposed to be saved in the UserForm's modulesheet.

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 being 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 saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        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 the first item
    End With
End Sub

 

From a closed workbook that is opened and closed again without the user noticing it

With the macro below you can fill a ListBox control with input from another closed workbook. The macro opens the closed workbook without the user noticing it, gets the values for the ListBox and closes the source workbook without saving any changes. Copy the macro into the 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 being 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 saving 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 the 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 input 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 open 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 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

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

 

From a closed workbook by using worksheet formulas/links

You can get input from another closed workbook by creating a (hidden) worksheet with formulas like this:

='C:\FolderName\[SourceWbName.xls]WorkSheetName'!A1

Copy the formula above as far down and to the right as necessary to get the input values you want from the closed workbook.

Set the UserForm's RowSource property to the worksheet range (in the hidden worksheet) containing the values from the closed (or open) workbook. The disadvantage with this method is that the user will be prompted to update the links every time the workbook containing the formula links is opened.

Like this post