¿Cómo llenar listbox desde excel con datos de access con rowsource?
¿Existe alguna forma de llenar el listbox que se encuentre en excel pero a través de un rowsource? Ya que tengo una base de datos en access con 100 mil productos y mediante un loop y additem se vuelve muy lento. Tengo un buscador en access que llena un listbox pero a través de rowsource y es muy rápido. Lo único que pude conseguir por el momento es copiar el recordset en una celda y desde las celdas pasarlo al listbox de excel, pero no es la solución prolija y merma el rendimiento.
Dejo el código
Private Sub CommandButton1_Click()
Dim Base As String
Dim Sql As String
Dim rscopy As String
Dim Conexion As String
Conexion = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE =" & "E:\Dropbox\MIS PROGRAMAS\ACCES\Buscador de Precios 64 bits 9-4-19.accdb" & ";PERSIST SECURITY INFO FALSE;"
uf2 = Sheets("ACCES").Range("I" & Rows.Count).End(xlUp).Row
Sheets("ACCES").Range("B11:I" & uf2).Clear
'LLAMAMOS AL OBJETO ADO
Set cn = New ADODB.Connection
'ABRIMOS LA CONEXION
cn.Open Conexion
'CREAMOS LA CONSULTA
Set Rs = New ADODB.Recordset
With Rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
'Consulta = Consulta & " WHERE " & Me.cmbCampo & " Like '*" & Me.txtBusqueda.Text & "*'"
'Sql = "SELECT * FROM Personas where Descripción like " & "'%" & Range("D6") & "%'"
'Like Replace("'%" & UCase(txtBusqueda.Value) & "%'", " ", "%") And
'Sql = "SELECT * FROM Personas where Descripción like " & "'%" & txtBusqueda & "%'"
Sql = "SELECT * FROM Personas where Descripción like " & Replace("'%" & UCase(txtBusqueda.Value) & "%'", " ", "%")
Rs.Open Sql, Conexion
Range("B11").CopyFromRecordset Rs
uf = Sheets("ACCES").Range("I" & Rows.Count).End(xlUp).Row
Me.Lista.RowSource = "ACCES!B11:I" & uf
cn.Close
Set Rs = Nothing
Set cn = Nothing
End Sub
Antes que nada quiero agradecer a todos los que respondieron, sus respuestas me han servido un montón. A continuación voy a copiar la respuesta que me dieron en otro foro, cortesía del señor AlexanderS:
sql = "Tu consulta" Rs. Open Sql, Conexion ListBox1.Column = Rs. GetRows
Con esto no hay necesidad de utilizar un bucle para llenar el listbox.
2 respuestas más de otros expertos
Prueba con una matriz la cual se carga así,
matriz=range("b11").currentregion
listbox1.list=matriz
Esto es equivalente a una función rowsource, si lo que quieres es cargar un listbox directo de access sin pasar por Excel que es lo que supongo al menos yo no conozco la forma de hacerlo, solo puedo recomendarte Rowsource o la opción por matriz ambas son rápidas yo he manejado un poco más de 100,000 registros y no tarda más de 1 segundo en cargarlo al listbox, el retraso en tu caso supongo que es por el pegado de datos de datos que haces de Access a Excel, la única es que hagas dos macros ligadas una que solo haga la descarga de datos y la segunda que haga la carga de datos al listbox así te darás cuenta cual proceso es el que es lento.
Gracias por responder. No comprendí bien lo de la matriz .currentregion. si es igual a un rango(b11) quiere decir que los datos los tengo que copiar y pegar en la hoja de calculo y después a través del .list se agregaría al listbox. ¿Eso seria más directo que lo que ya estoy utilizando? ¿O cuál seria la ventaja?. ¿Hay una forma de pasar la información de access directamente al listbox a través de alguna variable?
matriz=range("b11"). Currentregion te toma toda la información hacia abajo y hacia la derecha deteniéndose en la primera fila y columna vacía y lo carga en una matriz o contenedor de memoria, con la instrucción listbox1. List=matriz cargas en un solo paso la información al listbox1, la instrucción no tarda más de 1 segundo en hacer la carga, yo el problema que veo es la cantidad de información que tienes que pegar en Excel en mi opinión esa parte es la que consume más tiempo en tu macro, personalmente he programado macros con esta instrucción y en más de 100,000 registros y menos de 200,000 tarda como mencione a lo más 1 segundo, para lo que pides lamento no poder ayudarte no sabría decirte que instrucción te haría carga directa del listbox desde Access sin pasar por excel.
[Hola
Los Listbox de Excel son distintos a los de Access, a pesar de sus varias similitudes. En el caso de Excel, su propiedad "RowSource" solo acepta rangos de celdas y no es posible adaptarle algo como un Recordset.
Las opciones son usar List o AddItem, pero en ambos casos hay que recorrer y agregar registro por registro. La otra opción es la que ya usas tú de pegar todo en una hoja y luego pasarlo al ListBox con "RowSource".
Particularmente yo prefiero hacerlo desde el Recorsset directamente:
Do While Not rst.EOF ListBox1. AddItem rst. Fields(0) Rst. MoveNext Loop
Saludos]
Abraham Valencia
Gracias por responder. Tengo un buscador en access que es un userform que tiene un listbox que se carga a través de un rowsource, funciona bastante bien. ¿Es posible llamar a ese userform desde un userform de excel y que los datos que busque se puedan almacenar en un textbox de excel? Es decir usar el formulario de access en excel.
No es posible usar los formularios de Access en un entorno de Excel pues ambos son programas distintos así sean de la misma corporación. Sí podrías abrir el Access desde el Excel y a su vez uno de sus formularios, pero lograr que ese formulario abierto envíe un dato a un Userform de Excel en esa misma operación, la verdad requeriría de tanta programación (y conocimiento) que no sé si los resultados sean más eficientes que llenar el Lsitbox como te he recomendado y/o como mencionas estar haciéndolo; además de eso, es bastante complicado que alguien se dé el tiempo de programar todo eso aquí o en otro foro solo como ayuda.
Abraham Valencia
Gracias nuevamente por responder.
Entonces no me quedará más remedio que armar el formulario de facturación completo en ACCESS si quiero tener un buscador con esa velocidad. Yo pensaba que la velocidad de búsqueda(En un userform con listbox) iba a mejorar al estar los datos en una base de datos como access o SQL.
Lo que quería era solo mejorar la velocidad de búsqueda en el formulario de excel que tenía y pensé que probando cambiar el origen de los datos iba a mejorar.
Creo haber visto en visual basic traer una especie de cuadro de lista al userform pero de SQL. ¿Es posible en VBA de Excel?
Adjunto el código que tengo en el buscador que se encuentra en access
Option Compare Database
Option Explicit
Private Sub cmbCampo_Click()
Me.txtBusqueda = Null: Me.txtBusqueda.SetFocus
Me.Lista.RowSource = "SELECT Id,Rubro,Descripción,Marca,Precio,Proveedor,CodProv,ACTUALIZADO FROM Personas"
End Sub
Private Sub txtBusqueda_Change()
Dim Consulta As String
If Not IsNull(Me.cmbCampo) Then
Consulta = "SELECT Id,Rubro,Descripción,Marca,Precio,Proveedor,CodProv,ACTUALIZADO"
Consulta = Consulta & " FROM Personas"
Consulta = Consulta & " WHERE " & Me.cmbCampo & " Like '*" & Me.txtBusqueda.Text & "*'"
Me.Lista.RowSource = Consulta
txtCount2.Caption = "Mostrando " & Format(Lista.ListCount - 1, "#,##0") & " registros"
Else
MsgBox "Seleccione el campo de busqueda", vbExclamation, "Aviso"
Me.txtBusqueda = Null
With Me.cmbCampo: .SetFocus: .Dropdown: End With
End If
End Sub
Microsoft Excel no es un sistema gestor de base de datos, es una hoja de cálculo y si bien se pueden manejar grandes cantidades de información, no es comparable a la velocidad (además de otras cosas) que se puede alcanzar, en casos similares, con Access (para seguir hablando de Microsoft).
Sobre tu pregunta de cuadros de lista de SQL en los USerform de Excel, pues no, no hay algo así propiamente dicho.
Abraham Valencia