Como Buscar en Excel Mientras se Escribe y Cargar Listbox con SQL #492

En el presente post se muestra Como Filtrar en Excel Mientras se Escribe en Textbox y Mostrar los Datos Filtrados en Listbox, utilizando SQL – VBA.

La macro de Excel lista los datos filtrados, crea un filtro avanzado en Excel, que permite usar SQL en Excel, lo que crea una búsqueda dinámica en Excel en tiempo real o automática al ir buscando en la filas de la base de datos de Excel, mientras se va escribiendo en un Textobox, lo que vuelve a la macro un buscador inteligente en Excel.

En el playlist Conectar Excel con Excel con SQL de nuestro canal de YuoTube se muestran varias macros de excel que permiten filtrar en Excel con SQL utilizando la clausula Select, Where entre otras sentencias SQL.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

Suscribe a nuestro canal para que YouTube te avise cuando se suba nuevo contenido al canal, en el vídeo encontrarás una explicación gráfica y detallada del ejemplo que se muestra en este post.

El ejemplos de macro de Excel de búsqueda avanzada, se puede descargar desde el final del post, una vez realizada la descarga de la macro, al abrir el archivo de Excel se muestra un formulario de Excel que contiene un Textbox que se donde se escribirán los datos para ejecutar una consulta SQL en una tabla de Excel.

Luego de ingresar un mínimo de tres caracteres en el Textbox de Excel, empieza la ejecución de la macro, la cual va cargando en el listbox todas las coincidencias que encuentre con los caracteres que se van escribiendo, la carga se hace en tiempo real o en forma simultanea a medida que se escribe en el textbox.

La macro que filtra las filas de Excel con SQL, se encuentra en el evento Change del listbox que se usa para el ingreso de datos para luego realizar la búsqueda avanzada en Excel en tiempo real.

Quizás sea de utilidad también

Combinar Excel con Access

Conectar Excel con Excel Obtener Todos los Datos de un Mismo Libro con SQL

Como Filtrar Datos de Otro Libro Excel en Base Criterios con SQL

Como se dijo la macro se encuentra en el evento Change del Textbox que se encuentre en el userform de Excel, lo primero que se debe hacer para trabajar con SQL en Excel VBA es activar la referencia para que no provoque error, ello se hacer desde el Editor de VBA desde el menú HERRAMIENTAS luego REFERENCIAS debiendo activar la referencia denominada «Microsoft ActiveX Data Objects 2.5 Library».

 Habiendo activado la referencia nombrada, se dimensionan variables y crea el objeto conexión y objeto recorset, de la siguiente forma:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String

Set cn = New ADODB.Connection

Set rs = New ADODB.Recordset

Luego y lo más importante es crear la conexión ADODB para poder trabajar con SQL en Excel VBA, en este caso se conectará con el mismo libro, se usa el siguiente código:

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»

Posterior se debe crear la SQL o String de consulta, que es la que tendrá los parámetros para filtrar o consultar los datos requeridos, así:

sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(Descripcion) LIKE Ucase(‘%» & UserForm1.TextBox1 & «%’) ORDER BY ID ASC»

El código anterior dice: seleccione todos los campos o columnas de la tabla1 (Hoja1), cuando el dato de la fila correspondiente y columna «Descripción» (columna C del ejemplo) coincida con los caracteres escritos en el Textbox, sea que los caracteres o palabra escrita se encuentre al principio, medio, final o sea parte de una cadena de caracteres.

Si la consulta SQL en Excel encontró datos coincidentes en al base de datos con lo escrito en el textbox, éstos son cargados en el objeto «rs», teniendo los datos en memoria hasta que sean usados y se liberen las variables, luego carga los datos en el listbox, pero primero limpia el listbox, agrega un item para cargar la cabecera o titulo de las columnas, se realiza un bucle entre el primer dato y el último dato encontrado por el filtro cuyos datos están en memoria, se usa los siguientes códigos:

b.Clear

b.AddItem

rs.MoveFirst

Do While Not rs.EOF

b.AddItem rs.Fields(0).Value

b.List(b.ListCount – 1, 1) = rs.Fields(1).Value

b.List(b.ListCount – 1, 2) = rs.Fields(2).Value

b.List(b.ListCount – 1, 3) = rs.Fields(3).Value

b.List(b.ListCount – 1, 4) = rs.Fields(4).Value

b.List(b.ListCount – 1, 5) = rs.Fields(5).Value

b.List(b.ListCount – 1, 6) = rs.Fields(6).Value

rs.MoveNext

Loop

Para cargar la cabecera o título de columnas se usa el siguiente código:

For ii = 0 To rs.Fields.Count – 1

b.List(0, ii) = rs.Fields(ii).Name

Next ii

Seguidamente se muestra el código completo de la macro denominada Como Filtrar Datos de Excel Mientras se Escribe y Llenar Listbox Utilizando SQL, se muestra el código contenido en el Formulario de Excel y el código contenido en el módulo, para realizar la descarga en forma gratuita se debe realizar desde los link del final del post.

Código que se inserta en un Formulario de Excel

‘——————————————————————————————————-
‘ By marcrodos https:// programarexcel.com*********  https://youtube.com/programarexcel
‘——————————————————————————————————-

Private Sub CommandButton3_Click()
Unload UserForm1
End Sub

Private Sub TextBox1_Change()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String
On Error Resume Next

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set a = Sheets(«Hoja1»)
Set b = UserForm1.ListBox1
cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»

If Trim(UserForm1.TextBox1.Value) = «» Then
sql = «SELECT * FROM [» & «Hoja1$» & «]»
Set rs = cn.Execute(sql)
b.Clear
rs.MoveFirst
Do While Not rs.EOF
b.AddItem rs.Fields(0).Value
b.List(b.ListCount – 1, 1) = rs.Fields(1).Value
b.List(b.ListCount – 1, 2) = rs.Fields(2).Value
b.List(b.ListCount – 1, 3) = rs.Fields(3).Value
b.List(b.ListCount – 1, 4) = rs.Fields(4).Value
b.List(b.ListCount – 1, 5) = rs.Fields(5).Value
b.List(b.ListCount – 1, 6) = rs.Fields(6).Value
rs.MoveNext
Loop
Exit Sub
End If

If Len(UserForm1.TextBox1) > 2 Then
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Ucase(Descripcion) LIKE Ucase(‘%» & UserForm1.TextBox1 & «%’) ORDER BY ID ASC»
Set rs = cn.Execute(sql)
UserForm1.ListBox1 = Clear

If rs.EOF = True Then
b.Clear
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub

Else

b.Clear
b.AddItem
rs.MoveFirst
Do While Not rs.EOF
b.AddItem rs.Fields(0).Value
b.List(b.ListCount – 1, 1) = rs.Fields(1).Value
b.List(b.ListCount – 1, 2) = rs.Fields(2).Value
b.List(b.ListCount – 1, 3) = rs.Fields(3).Value
b.List(b.ListCount – 1, 4) = rs.Fields(4).Value
b.List(b.ListCount – 1, 5) = rs.Fields(5).Value
b.List(b.ListCount – 1, 6) = rs.Fields(6).Value
rs.MoveNext
Loop
End If

‘Carga los datos de la cabecera en listbox
For ii = 0 To rs.Fields.Count – 1
b.List(0, ii) = rs.Fields(ii).Name
Next ii

Set rs = Nothing
cn.Close
Set cn = Nothing
b.ColumnWidths = «30 pt;50 pt;190 pt;50 pt;50 pt;50 pt;50 pt»
End If
End Sub

Private Sub UserForm_Initialize()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String
On Error Resume Next
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set b = UserForm1.ListBox1

cn.Open «Provider=Microsoft.ACE.OLEDB.12.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 12.0;HDR=Yes;»»»
sql = «SELECT * FROM [» & «Hoja1$» & «]»

Set rs = cn.Execute(sql)

rs.MoveFirst
Do While Not rs.EOF
b.AddItem rs.Fields(0).Value
b.List(b.ListCount – 1, 1) = rs.Fields(1).Value
b.List(b.ListCount – 1, 2) = rs.Fields(2).Value
b.List(b.ListCount – 1, 3) = rs.Fields(3).Value
b.List(b.ListCount – 1, 4) = rs.Fields(4).Value
b.List(b.ListCount – 1, 5) = rs.Fields(5).Value
b.List(b.ListCount – 1, 6) = rs.Fields(6).Value
rs.MoveNext
Loop

b.ColumnCount = 7
b.ColumnWidths = «30 pt;50 pt;190 pt;50 pt;50 pt;50 pt;50 pt»

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
End Sub

Código que se inserta en un módulo

‘——————————————————————————————————-

‘ By marcrodos   https:// programarexcel.com *********  https://youtube.com/programarexcel

‘——————————————————————————————————-

#If VBA7 And Win64 Then

‘Si es de 64 bits

Public Declare PtrSafe Function ShellExecute Lib «shell32.dll» Alias «ShellExecuteA» (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr

Public Declare PtrSafe Function FindWindow Lib «USER32» Alias «FindWindowA» (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Public Declare PtrSafe Function GetWindowLongPtr Lib «USER32» Alias «GetWindowLongPtrA» (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr

Public Declare PtrSafe Function SetWindowLongPtr Lib «USER32» Alias «SetWindowLongPtrA» (ByVal hwnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr

Public Declare PtrSafe Function DrawMenuBar Lib «USER32» (ByVal hwnd As Long) As LongPtr

Public Declare PtrSafe Function RegOpenKeyA Lib «advapire32.dll» (ByVal hKey As LongPtr, ByVal lpSubKey As String, phkResult As LongPtr) As LongPtr

#Else

‘Si es de 32 bits

Public Declare Function ShellExecute Lib «shell32.dll» Alias «ShellExecuteA» (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Declare Function FindWindow Lib «USER32» Alias «FindWindowA» (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Declare Function GetWindowLong Lib «USER32» Alias «GetWindowLongA» (ByVal hwnd As Long, ByVal nIndex As Long) As Long

Public Declare Function SetWindowLong Lib «USER32» Alias «SetWindowLongA» (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Public Declare Function DrawMenuBar Lib «USER32» (ByVal hwnd As Long) As Long

Public Declare Function RegOpenKeyA Lib «advapire32.dll» (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long

#End If

Sub muestra1()

UserForm1.Show

End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends