|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Fill a ListBox-control with values from another workbook using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
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 itWith 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 ADOWith 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/linksYou can get input from another closed workbook by creating a (hidden) worksheet with formulas like this: ='C:\FolderName\[SourceWbName.xls]WorkSheetName'!A1 Copy this formula 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. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Preparing the Marketing Plan (Ama Marketing Toolbox Series New Edition)
Writing Excel Macros with VBA, 2nd Edition
The Basics of Finance: Financial Tools for Non Financial Managers
Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
Business Plans Kit for Dummies (With CD-ROM)
The 11 Immutable Laws of Internet Branding | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|