Fill a ListBox control with values from another workbook
2000-09-16 Dialogs 0 203
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$15The 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'!A1Copy 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.