PROGRAMAR EN VBA MACROS DE EXCEL: Conectar Excel con Excel Sentencias SQL
Mostrando las entradas con la etiqueta Conectar Excel con Excel Sentencias SQL. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Conectar Excel con Excel Sentencias SQL. Mostrar todas las entradas

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







Como Buscar en Excel Mientras se Escribe en Textbox y Cargar en Listbox 

Buscar en excel mientras se escribeEn 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

  

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.







 


Como Funciona la macro de búsqueda avanzada en Excel VBA tiempo real


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 Crea la Macro de Búsqueda Avanzada Mientras se Escribe en Textbox


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


Código del ejemplo y descarga del archivo de excel utilizado


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      

Como Filtrar Datos Excel con Consulta SQL Vs Bucle While Wend





En este post se presenta una comparación entre macros, se muestra por un lado una macro que permite filtrar datos mediante criterios utilizando un Bucle con la estructura While ... Wend y por otro lado se usa SQL para buscar datos en Excel, los resultados son los mismos, en base de datos chicas el tiempo de búsqueda es prácticamente el mismo, pero en bases de datos más grandes SQL es mucho más rápido.

Descarga el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Recomiendo leer un excelente libro sobre 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.

  

Mira una explicación detallada en el vídeo, suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.









 


Al descargar el ejemplo y abrir el libro de Excel se podrá observar que se encuentran dos botones, el primero ejecuta un filtro o búsqueda utilizando un bucle con la estructura While...Wend; el segundo botón realiza la misma búsqueda o filtro de datos utilizando SQL.

Los criterios de búsqueda se encuentran en la Hoja1 celda B1 y Celda E1, en el ejemplo que se muestra se busca todos los datos coincidentes en la base de datos que se encuentra en la Hoja2, que sena iguales a la marca "Coca Cola" cuyo precio de venta (Pv) sea mayor a "Cinco".

Con el bucle While... When, se recorre todas las filas buscando los registros que coincidan con los criterios, en este ejemplo los criterios se asignan a variables que son las siguientes:

marca = UCase(b.Range("B1"))
signo = b.Range("D1")
valor = b.Range("E1")

marcabus = UCase(a.Cells(filabus, "D"))
valorbus = a.Cells(filabus, "E")


Las primeras variables son las de la hoja de destino y las últimas dos variables son los datos que va tomando cada vez que se recorra las filas con el bucle While ... Wend

Si la marca ingresada en B1 es coincidente con la marca de la fila correspondiente de la Hoja2 que en cada momento recorre el bucle y además el valor de venta es mayor al valor buscado, entonces el registro cumple la condición, lo copia y pega en la Hoja1 y así sucesivamente con cada uno de los ítem de la base de datos.

If marcabus = marca And valorbus > valor Then
a.Range("A" & filabus & ":G" & filabus).Copy Destination:=b.Range("A" & fila)

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como crear una factura con excel, guardarla y enviarla por mail automáticamente


Como cargar listbox con datos provenientes de varias hojas

Como repetir en Excel un mismo caracter varias veces

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

La otra forma de búsqueda es el Filtro de Datos de Excel a través de SQL, para ello primero hay que realizar una conexión con el Libro Excel, que puede ser el mismo u otro libro, en este caso es el mismo, ya que los datos se encuentran en el mimos libro sobre el cual se trabaja.

La conexión con el mismo libro se realiza con el siguiente código, a continuación la SQL, que es el string de consulta que contiene los criterios de búsqueda o filtro de datos.

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

sql = "SELECT * FROM [" & "Hoja2$" & "] WHERE Ucase(" & a.Range("D1") & ") LIKE Ucase('%" & b.Range("B1") & "%') AND pv " & b.Range("D1") & " " & b.Range("E1") & " ORDER BY pv ASC"

La SQL se podría leer: Selecciones todas las columnas de la Hoja2 cuando la columna Marca coincida con el texto (marca) ingresado en B1 y el precio de venta sea Mayor al precio que figura en la columna "Pv" de la base de datos.

Una vez ejecutada la SQL los datos filtrados o coincidentes se mantienen en memoria (Recorset), hasta tanto sean usado y liberadas las variables.

Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs

En el código anterior los datos filtrados son copiados a partir de la fila 1 columna 1 es decir celda A3, ya que en la fila 2 van los encabezados de columna.

Para liberar las variables y por ende liberar recursos, se usa:

Set rs = Nothing
cn.Close
Set cn = Nothing


El ejemplos denominado Como Filtrar Datos Excel con Consulta SQL vs. Bucle While ... Wend, se puede descargar desde el link del final que esta en forma posterior a la codificación del ejemplo que se encuentra a continuación.


Código que se inserta en un módulo

Sub ConsutaSQLExcel()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ctl As Object
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 = Sheets("Hoja1")
Set a = Sheets("Hoja2")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"""
sql = "SELECT * FROM [" & "Hoja2$" & "] WHERE Ucase(" & a.Range("D1") & ") LIKE Ucase('%" & b.Range("B1") & "%') AND pv " & b.Range("D1") & " " & b.Range("E1") & " ORDER BY pv ASC"

uf = b.Range("A" & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range("A3:G" & uf).Clear
a.Range("A1:G1").Copy Destination:=b.Range("A2")
Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs
b.Range("B:B").NumberFormat = "dd/mm/yyyy"
Set rs = Nothing
cn.Close
Set cn = Nothing
If b.Range("A3") <> Empty Then
MsgBox ("La busqueda se realizó con éxito"), vbInformation, "AVISO"
Else
MsgBox ("No se encontraron regisgros para el criterio de búsqueda"), vbInformation, "AVISO"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub ConsutaBucleWhileWend()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'On Error Resume Next
Set b = Sheets("Hoja1")
Set a = Sheets("Hoja2")
filabus = 2
fila = 3
uf = b.Range("A" & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range("A3:G" & uf).Clear
a.Range("A1:G1").Copy Destination:=b.Range("A2")

While a.Cells(filabus, "A") <> Empty
marca = UCase(b.Range("B1"))
signo = b.Range("D1")
valor = b.Range("E1")

marcabus = UCase(a.Cells(filabus, "D"))
valorbus = a.Cells(filabus, "E")
If marcabus = marca And valorbus > valor Then
a.Range("A" & filabus & ":G" & filabus).Copy Destination:=b.Range("A" & fila)
fila = fila + 1
End If
filabus = filabus + 1
Wend

b.Range("B:B").NumberFormat = "dd/mm/yyyy"
If b.Range("A3") <> Empty Then
MsgBox ("La busqueda se realizó con éxito"), vbInformation, "AVISO"
Else
MsgBox ("No se encontraron regisgros para el criterio de búsqueda"), vbInformation, "AVISO"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
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      

Como buscar datos en Excel metodo Find Vs SQL






En el post se muestra una macro que permite buscar datos en Excel, la búsqueda de datos en Excel se hace de dos formas con el método Find y a través de SQL (Structured Query Language), Lenguaje de Consulta Estructurada). Es decir la macro busca un dato determinado en una base de datos de Excel, una vez encontrado el dato, copia y pega todos los registros relacionados con el dato buscado en la hoja de Excel.

Desde el final del post se puede descargar el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre 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.

  

El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.









 


El objetivo del post es mostrar como se puede buscar datos en Excel utilizando dos formas diferentes, llegando a un mismo resultado, a mi entender para bases de datos chicas con el método find basta y sobra, pero en bases de datos más grandes SQL se comporta muy bien en cuanto a la rápidez de búsqueda.

Una ventaja que tiene SQL es que no solo se puede buscar datos sino se puede insertar datos, modificar los mismos, como así también usar criterios complejos para el filtro de datos acá tienes más ejemplos que muestran como se usa SQL en Excel.


⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como crear una factura con excel, guardarla y enviarla por mail automáticamente


Como enviar mail con archivo Excel y PDF mediante Outlook con Excel

Como hacer un link o hiperlink a google maps con Excel

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

En el ejemplo se debe ingresar el número a buscar en la celda B1, la macro buscará en la columna A de la hoja "URL MACRO EJEMPLOS" el número ingresado en la celda B1, escribiendo los registros asociados de las distintas columnas en la fila 4.

Si se requiere buscar con el método Find se presionara el primero botón, el cual ejecuta para buscar el dato ingresado en la celda B1 en la Columna A de la hoja "URL MACRO EJEMPLOS", con el siguiente código:

busco = Cells(1, "B")
Set codigo = a.Range(r).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)

Si el datos es encontrado es decir no es vacío el objeto código creado a los fines de la búsqueda, determina cual es la fila donde encontró el dato y lo carga en la varible: código.row; que luego se utiliza para obtener el restos de registros relacionados con el dato buscado y que se encuentran en esa fila, el siguiente es el código empleado:

If Not codigo Is Nothing Then
Cells(4, "A") = a.Cells(codigo.Row, 1)


Para realizar la misma búsqueda, pero utilizando SQL se debe proceder a realizar una conexión con el libro de Excel, en este caso es una hoja distinta del mismo libro de Excel, pero puede ser otro libro distinto también, el código es el siguiente:

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

Seguidamente se crea la consulta SQL que es la que se debe ejecutar para poder filtrar el o los datos, es la siguiente:

sql = "SELECT * FROM [" & "URL MACRO EJEMPLOS$" & "] WHERE " & b.Range("A1") & " LIKE " & a.Range("B1") & " ORDER BY N ASC"


La SQL dice: Seleccione todas las columnas de la hoja (FROM) "URL MACRO EJEMPLOS" cuando el dato de la columna A de dicha hoja sea igual a la celda B1 de la hoja Hoja1, luego ordena por la columna denominada "N", aunque en este caso el orden no es necesario ya que es uno solo el dato coincidente, para filtrar los datos se debe ejecutar la SQL, de la siguiente forma:

Set rs = cn.Execute(sql)

Una vez ejecutada la SQL los datos obtenidos o filtrados  (Recordset) se conservan en memoria para ser usados, en este ejemplo los datos encontrados son escritos en la fila 4 de la Hoja1, el código es el siguiente:

a.Cells(4, 1).CopyFromRecordset Data:=rs

A continuación se encuentra el código completo que permite buscar datos en Excel con el Metodo Find y con SQL, seguidamente está el link de descarga del archivo de ejemplo, considera la posibilidad de aportar para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Código que se inserta en un módulo

Sub BuscarMetodoFind()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim uf As String
Set a = Sheets("URL MACRO EJEMPLOS")
pf = 2
uf = a.Range("A" & Rows.Count).End(xlUp).Row
r = "A" & pf & ":A" & uf
busco = Cells(1, "B")
Set codigo = a.Range(r).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)

If Not codigo Is Nothing Then
Cells(4, "A") = a.Cells(codigo.Row, 1)
Cells(4, "B") = a.Cells(codigo.Row, 2)
Cells(4, "C") = a.Cells(codigo.Row, 3)
Cells(4, "D") = a.Cells(codigo.Row, 4)
Cells(4, "E") = a.Cells(codigo.Row, 5)
Cells(4, "F") = a.Cells(codigo.Row, 6)
Else
Range("4:4").Clear
Cells(4, "A") = "No se encontraron registros en la base de datos"
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Sub FiltroSQLExcel()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ctl As Object
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 = Sheets("URL MACRO EJEMPLOS")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"""
sql = "SELECT * FROM [" & "URL MACRO EJEMPLOS$" & "] WHERE " & b.Range("A1") & " LIKE " & a.Range("B1") & " ORDER BY N ASC"
Set rs = cn.Execute(sql)
a.Cells(4, 1).CopyFromRecordset Data:=rs
Set rs = Nothing
cn.Close
Set cn = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub limpiar()
Range("A4:F4").ClearContents
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      

Conectar Excel con Excel Consulta SQL Varios Criterio con Datos Otro Libro





La macro permite importar datos de otro libro de Excel filtrando en base a crierios con SQL, no se abre el libro sino que se crea una conexión para extraer los datos filtrando por la condiciones o criterios dados, los datos son pegados en el libro que contiene la macro.

Click en el link siguiente para todos los vídeos de la saga Conectar Excel con Excel Sentencias SQL entre otro existe uno que permite filtrar por un rango de fechas o otro por un rango de números y mucho más.

Desde el final del post se puede descargar el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre 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.

  

El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.









 


Para este ejemplo se necesita descargar un archivo comprimido, que puedes hacerlo desde el final del post, se debe descomprimir y guardar en cualquier directorio de nuestra PC con la única condición que deben guardarse juntos, porque el libro que contiene la macro necesita el otro libro para poder buscar los datos y llevar a cabo el ejemplo.

El libro con la macro buscará en el libro de Excel que se proporciona, todos los datos que coincidan con los criterios o condiciones expuestas en las celdas I1, K1 y L1, la macro no abre el libro sino que se conecta al libro para filtrar y extraer la formación necesaria, luego esta información es pegada o presentada en la hoja2 del Libro de Excel que contiene la macro y sobre el cual estamos trabajando.

Como se dijo para conectarse al libro se usa el siguiente código:

mybook = ThisWorkbook.Path & "\414 Conectar Excel con Excel Consulta SQL Base Datos.xlsx"
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & mybook & ";Extended Properties=""Excel 12.0;HDR=Yes;"""

Luego se debe crear el string de consulta o SQL que se hace de la siguiente forma:

sql = "SELECT * FROM [" & "Hoja1$" & "] WHERE Ucase(" & a.Range("H1") & ") LIKE Ucase('%" & a.Range("I1") & "%') AND pv " & a.Range("K1") & " " & a.Range("L1") & " ORDER BY ID ASC"

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como contar celdas vacías en un rango


Como rellenar un pagaré en forma automática Conectando Excel con Word

Como buscar en listbox mientras se escribe en texto o a medida que se escribe

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Luego se debe ejecutar la SQL con el siguiente código:

Set rs = cn.Execute(sql)

Con el código anterior lo que se hace es crear un objeto recordset, que es el objeto que contiene todos los datos filtrados de acuerdo con los criterios brindados, estos datos al ejecutar la sql se mantienen en memoria de la PC hasta que se liberen las variables.

Para pegar o mostrar los datos en la hoja de Excel se usa:

b.Cells(2, 1).CopyFromRecordset Data:=rs

En este caso los datos comienzan a copiarse desde la celda A2 en adelante, el código completo del ejemplo que hemos denominado Importar datos de Excel en Base a Criterios con SQL y Base de Datos Contenidos en Otro Libro, se presenta a continuación seguido al mismo se encuentra el link de descarga del archivo de ejemplo.


Código que se inserta en un módulo

Sub ConsutaSQLExcel()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ctl As Object
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 = Sheets("Hoja2")

mybook = ThisWorkbook.Path & "\414 Conectar Excel con Excel Consulta SQL Base Datos.xlsx"
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & mybook & ";Extended Properties=""Excel 12.0;HDR=Yes;"""

If a.CheckBox1 = False Then
sql = "SELECT * FROM [" & "Hoja1$" & "] WHERE Ucase(" & a.Range("H1") & ") LIKE Ucase('%" & a.Range("I1") & "%') AND pv " & a.Range("K1") & " " & a.Range("L1") & " ORDER BY ID ASC"
Else
sql = "SELECT * FROM [" & "Hoja1$" & "] WHERE Ucase(" & a.Range("H1") & ") LIKE Ucase('" & a.Range("I1") & "')AND pv " & a.Range("K1") & " " & a.Range("L1") & " ORDER BY ID ASC"
'sql = "SELECT * FROM [" & "Hoja1$A1:V65000" & "] WHERE Ucase(" & a.Range("H1") & ") LIKE Ucase('" & Range("H2") & "') ORDER BY ID ASC"
End If

b.Cells.Clear
a.Range("A1:G1").Copy Destination:=b.Range("A1")
Set rs = cn.Execute(sql)
b.Cells(2, 1).CopyFromRecordset Data:=rs
b.Range("B:B").NumberFormat = "dd/mm/yyyy"
Set rs = Nothing
cn.Close
Set cn = Nothing
If b.Range("A2") <> Empty Then
MsgBox ("La busqueda se realizó con éxito"), vbInformation, "AVISO"
Else
MsgBox ("No se encontraron regisgros para el criterio de búsqueda"), vbInformation, "AVISO"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
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      
4.70/5 – 1379