Rellenar un control ListBox con valores de libro cerrado con VBA en Microsoft Excel
En este artículo, obtendremos datos del libro de trabajo cerrado al cuadro de lista en forma de usuario usando VBA.
Los datos sin procesar para este ejemplo están en el rango A2: B10 en el libro de trabajo «23SampleData.xls», que se coloca en la ruta del archivo «D: \ Excelforum \ ExcelForum office \ excel tip código antiguo \ Shared Macro \ 23 \».
Hemos creado dos botones de comando en la hoja de trabajo principal para ejecutar dos formas de usuario diferentes. Cada botón de comando está vinculado a diferentes formas de usuario.
Explicación lógica
En este ejemplo, se utilizan dos formas diferentes para recuperar datos del libro cerrado. Estos son:- . Abra el libro cerrado y obtenga los datos. Usando la conexión ADODB
Abra el libro cerrado y obtenga los datos
Es posible establecer la propiedad RowSource de un control ListBox para obtener datos de otro libro asignando valor a la propiedad RowSource de la siguiente manera:
‘[Filename.xls] Sheet1?! $ B $ 1: $ B $ 15 ListBox Control mostrará valores solo si el otro libro está abierto.
Entonces, para obtener los datos del libro de trabajo cerrado, crearemos una macro para abrir el otro libro de trabajo sin que el usuario lo note y obtenga datos del libro de trabajo para agregar elementos en el Cuadro de lista y cerrar el libro de trabajo.
Al hacer clic en el botón «Seleccionar», se activará el formulario de usuario «UserForm1».
El evento Initialize del formulario de usuario se usa para agregar elementos en el cuadro de lista. Este evento primero abre el libro cerrado y luego asigna el valor en el rango a la variante «ListItems». Después de asignar el valor, el libro se cierra y los elementos se agregan al cuadro de lista.
El cuadro de lista se utiliza para seleccionar el nombre de los valores de lista existentes. Al presionar el botón «Aceptar» se mostrará el nombre seleccionado.
Usando la conexión ADODB
ActiveX Data Objects (ADO) es una interfaz de alto nivel fácil de usar para la conexión OLE DB. Es una interfaz de programación para acceder y manipular datos en una base de datos.
Para crear una conexión ADODB, necesitaremos agregar la biblioteca ADO al proyecto.
Para agregar una referencia, elija en el menú Herramientas> Referencia.
Al hacer clic en el botón «Conexión ADODB» en la hoja de trabajo, se activará el formulario de usuario «UFADODB». En el evento de inicialización de este formulario de usuario, hemos utilizado la conexión ADODB para obtener datos del libro cerrado. Hemos creado una función definida por el usuario (UDF) personalizada «ReadDataFromWorkbook» para establecer la conexión y obtener los datos del libro de trabajo cerrado a la matriz.
Hemos utilizado otra UDF «FillListBox» para agregar elementos en el cuadro de lista durante la inicialización del formulario de usuario. El cuadro de lista mostrará los datos en dos columnas, una columna contiene el nombre y la segunda columna contiene la edad.
Al presionar el botón «Aceptar» después de seleccionar el elemento en el cuadro de lista, se mostrará el mensaje de información sobre el elemento seleccionado.
Siga el código a continuación
Option Explicit
Sub running()
UserForm1.Show
End Sub
Sub ADODBrunning()
UFADODB.Show
End Sub
'Add below code in UFADODB userform
Option Explicit
Private Sub CommandButton1_Click()
Dim name1 As String
Dim age1 As Integer
Dim i As Integer
'Assign the selected value in list box to variable name1 and age1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
name1 = ListBox1.Value
age1 = ListBox1.List(ListBox1.ListIndex, 1)
Exit For
End If
Next
'Unload the userform
Unload Me
'Displaying output
MsgBox "You have selected " & name1 & ". His age is " & age1 & " yrs."
End Sub
Private Sub UserForm_Initialize()
'Filling ListBox1 with data from a closed workbook
Dim tArray As Variant
'Calling function ReadDataFromWorkbook for getting data from specified range to array
'Change path according to your requirement, "Sample_data" is named defined range
tArray = ReadDataFromWorkbook("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data")
'Calling function FillListBox for adding items in List Box
'Assign List box object and tarray as parameter
FillListBox Me.ListBox1, tArray
'Releasing array variables and deallocate the memory used for their elements.
Erase tArray
End Sub
Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
'Filling List box lb with data from RecordSetArray
Dim r As Long, c As Long
With lb
.Clear
'Assigning value to listbox
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
'Selecting no item in the List box by default
.ListIndex = -1
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)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
'Declaring a connection string and the driver require for establishing connection
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
'Creating a new ADODB connection
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
'Open the database connection
dbConnection.Open dbConnectionString
'Getting the recordset from defined named range
Set rs = dbConnection.Execute("[" & SourceRange & "]")
On Error GoTo 0
'Returns a two dimensional array with all records in rs
ReadDataFromWorkbook = rs.GetRows
'Close the recordset and database connection
rs.Close
dbConnection.Close
Set rs = Nothing
Set dbConnection = Nothing
Exit Function
'Code for handling error
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Function
'Add below code in UserForm1
Option Explicit
Private Sub CommandButton1_Click()
Dim name1 As String
Dim i As Integer
'Assign the selected value to variable name1
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
name1 = ListBox1.Value
Exit For
End If
Next
'Unload the userform
Unload Me
'Display the selected name
MsgBox "You have selected " & name1 & "."
End Sub
Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
'Turning screen updates off
Application.ScreenUpdating = False
With Me.ListBox1
'Remove existing entries from the listbox
.Clear
'Open the source workbook as ReadOnly
Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _
False, True)
'Get the range of values you want
ListItems = SourceWB.Worksheets(1).Range("A2:A10").Value
'Close the source workbook without saving changes
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
'Convert values to a vertical array
ListItems = Application.WorksheetFunction.Transpose(ListItems)
For i = 1 To UBound(ListItems)
'Populate the listbox
.AddItem ListItems(i)
Next i
'Selecting no items by default, set to 0 to select the first item
.ListIndex = -1
End With
End Sub
Si te gustó este blog, compártelo con tus amigos en Facebook y Facebook.
Nos encantaría saber de usted, háganos saber cómo podemos mejorar nuestro trabajo y hacerlo mejor para usted. Escríbanos a [email protected]