Llene un control ListBox con valores de un libro cerrado usando VBA en Microsoft Excel

Tabla de contenido

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 obtener datos del libro cerrado. Estos son:-

  1. Abra el libro cerrado y obtenga los datos

  2. 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 un valor a la propiedad RowSource de la siguiente manera:

"[Nombre de archivo.xls] Hoja1?! $ 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 de trabajo 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 a continuación para obtener el código

 Opción Sub explícito en ejecución () UserForm1.Show End Sub Sub ADODBrunning () UFADODB.Show End Sub 'Agregue el código a continuación en el formulario de usuario de UFADODB Opción Subcomando privado explícito Botón1_Click () Dim name1 As String Dim age1 As Integer Dim i As Integer' Asigne el seleccionado valor en el cuadro de lista a la variable nombre1 y edad1 Para i = 0 A ListBox1.ListCount - 1 Si ListBox1.Selected (i) Entonces name1 = ListBox1.Value age1 = ListBox1.List (ListBox1.ListIndex, 1) Salir para final si es siguiente ' Descargue el formulario de usuario Unload Me 'Visualizando el MsgBox de salida "Ha seleccionado" & name1 & ". Su edad es" & age1 & "yrs". End Sub Private Sub UserForm_Initialize () 'Llenando ListBox1 con datos de un libro de trabajo cerrado Dim tArray As Variant' Llamando a la función ReadDataFromWorkbook para obtener datos del rango especificado a la matriz 'Cambie la ruta de acuerdo con sus requisitos, "Sample_data" se denomina rango definido tArray = ReadDataFromWorkbook ("D: \ Excelforum \ ExcelForum office \ excel tip código antiguo \ Shared Macro \ 23 \ 23SampleData.xls", "Sample_Data") 'Llamando a la función FillListBox para agregar elementos en List Box' Asignar objeto List box y tarray como parámetro FillListBox Me .ListBox1, tArray 'Liberar variables de matriz y desasignar la memoria utilizada para sus elementos. Borrar tArray End Sub Private Sub FillListBox (lb As MSForms.ListBox, RecordSetArray como variante) 'Llenar el cuadro de lista lb con datos de RecordSetArray Dim r As Long, c As Long With lb .Clear' Asignar valor a listbox para r = LBound (RecordSetArray , 2) A UBound (RecordSetArray, 2) .AddItem Para c = LBound (RecordSetArray, 1) A UBound (RecordSetArray, 1) .List (r, c) = RecordSetArray (c, r) Siguiente c Siguiente r 'No seleccionar ningún elemento en el cuadro de lista de forma predeterminada .ListIndex = -1 End With End Sub Private Function ReadDataFromWorkbook (SourceFile As String, _ SourceRange As String) As Variant 'requiere una referencia a la biblioteca de Microsoft ActiveX Data Objects' (menú Herramientas> Referencias en el VBE ) Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset Dim dbConnectionString As String 'Declarar una cadena de conexión y el controlador requieren para establecer la conexión dbConnectionString = "DRIVER = {Microsoft Excel Driver (* .xls)}; ReadOnly = 1; DBQ = "& SourceFile 'Creando una nueva conexión ADODB Establecer dbConnection = New ADODB.Connection On Error GoTo InvalidInput 'Abrir la conexión de base de datos dbConnection.Open dbConnectionString' Obteniendo el conjunto de registros del rango con nombre definido Set rs = dbConnection.Execute ("[" & SourceRange & "]") On Error GoTo 0 'Devuelve un dos matriz dimensional con todos los registros en rs ReadDataFromWorkbook = rs.GetRows 'Cerrar el conjunto de registros y la conexión de la base de datos rs.Close dbConnection.Close Set rs = Nothing Set dbConnection = Nothing Exit Function' Código para manejar el error InvalidInput: MsgBox "El archivo fuente o rango fuente no es válido! ", _ vbExclamation," Obtener datos del libro de trabajo cerrado "Fin de la función 'Agregar el siguiente código en UserForm1 Option Explicit Private Sub CommandButton1_Click () Dim name1 As String Dim i As Integer' Asignar el valor seleccionado a la variable name1 Para i = 0 To ListBox1.ListCount - 1 Si ListBox1.Selected (i) Entonces name1 = ListBox1.Value Salir para el final Si sigue 'Descargar el formulario de usuario Descargar Me' Muestra el nombre seleccionado MsgBox "Ha seleccionado" & name1 & ". End Sub Private Sub UserForm_Initialize () Dim ListItems As Variant, i As Integer Dim SourceWB As Workbook 'Desactivando las actualizaciones de pantalla Application.ScreenUpdating = False With Me.ListBox1' Elimina las entradas existentes del listbox .Clear 'Abre el libro de origen como ReadOnly Set SourceWB = Workbooks.Open ("D: \ Excelforum \ ExcelForum office \ excel tip código antiguo \ Shared Macro \ 23 \ 23SampleData.xls", _ False, True) 'Obtenga el rango de valores que desea ListItems = SourceWB.Worksheets (1 ) .Range ("A2: A10"). Value 'Cerrar el libro de trabajo de origen sin guardar los cambios SourceWB.Close False Set SourceWB = Nothing Application.ScreenUpdating = True' Convertir valores en una matriz vertical ListItems = Application.WorksheetFunction.Transpose (ListItems) For i = 1 To UBound (ListItems) 'Rellena el listbox .AddItem ListItems (i) Next i' No selecciona ningún elemento de forma predeterminada, establezca en 0 para seleccionar el primer elemento .ListIndex = -1 End With End Sub 

Si te gustó este blog, compártelo con tus amigos en Facebook. Además, puede seguirnos en Twitter y Facebook.

Nos encantaría saber de usted, háganos saber cómo podemos mejorar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico

Va a ayudar al desarrollo del sitio, compartir la página con sus amigos

wave wave wave wave wave