Algo muy solicitado es poder consultar datos entre un rango de fechas, en este ejemplo muestro como consultar datos en un rango de fechas de una base de datos que está en el mismo Libro de Excel, en ejemplo posterior se hará buscando en otro Libro de Excel, suscribe para recibir notificación.
Anteriormente se expuso una variante, donde los datos encontrados entre fecha y fecha eran cargados en un listbox usando código VBA solamente, en esta ocasión la consulta de datos entre una fecha inicial y una fecha final en Excel se realiza a través de sentencias SQL, esta forma agiliza la obtención y muestra de datos, por lo que recomiendo su uso, en otras palabras se usa código de VBA para el resto de la macro y para la obtención de datos se utiliza SQL, que agiliza muchísimo el manejo de grandes bases de datos.
Descarga desde el final del post 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.
La maco está asignada al botón buscar que se encuentra en la hoja, presionando el botón se ejecuta la macro, que en primero lugar creará una conexión a Excel ADODB – OLEDB con el mismo Libro de Excel, utilizando este código:
cn.Open «Provider=Microsoft.Jet.OLEDB.4.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 8.0;HDR=Yes;»»»
Estoy usando Excel 2016, si tienes que adaptar a otras versiones de Excel, mira el siguiente link para poder establecer la conexión de Excel con otro Libro Excel.
Realizada la conexión con el paso anterior se debe crear la SQL o string de consulta que luego se ejecutará para obtener los datos, en este ejemplo es la siguiente:
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Fecha >= #» & Format(CDate(a.Range(«I1»)), «mm/dd/yyyy») & «# AND Fecha <= #» & Format(CDate(a.Range(«K1»)), «mm/dd/yyyy») & «# ORDER BY fecha ASC»
Basicamente el código anterior dice:
Select *: significa que seleccione todas las columnas, si quisiéramos especificar una en particular se debe colocar el nombre en otros ejemplos haremos consultas con columnas especificas, deja en los comentarios de nuestro canal de YouTube que ejemplo requieres y se hará el ejemplo con ello.
⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también
Como hacer un cronometro den VBA para Excel con macros
Como dar formato en negrita solo a las palabras en mayúsculas
Como dar formato a filas dependiendo del valor de una celda
⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
From: significa desde que tabla se deben obtener los datos, en este tipo de conexiones, cada hoja es tomada como una tabla de base de datos, si en la conexión se especificó «HDR=Yes» entonces la primer fila es la cabecera o nombre de las columnas y todos los datos de cada fila están relacionados con el registro de la clave principal puede ser un numero de ID, es decir no podrá repetirse el dato. En este ejemplo From hace referencia a la hoja1 que es donde está la base de datos, se puede adaptar y hacer referencia al nombre de la hoja donde tenemos realmente la base de datos que requerimos consultar.
Where: acá se establecen los criterios de búsqueda de datos, si no se establece criterio el recordset serán todos los datos de la base datos, es decir al ejecutar la SQL se obtendran todos los datos de la base de datos, manteniéndose en memoria hasta que sean usados, recuerden que consume recursos de memoria de la PC por eso deberíamos acotar la búsqueda.
En este caso especifico para consultar entre un rango de fechas se debe usar # para especificar que se está buscando fechas, ya que si no agregamos no buscará nada, así:
Fecha >= #» & Format(CDate(a.Range(«I1»)), «mm/dd/yyyy») & «#
En este ejemplo los criterios de búsqueda es decir la fecha inicial y la fecha final o rango de fechas por la que requerimos buscar en Excel están en la celda I1 y K1 respectivamente, por ende la SQL hace referencia a esas celdas para obtener del dato fecha inicial y fecha final.
Fíjense que el campo o cabecera que contiene las fecha en los registros precisamente de denomina fecha por eso está SQL toma la forma anterior donde:
Fecha: es el nombre del campo en el que se debe aplicar el criterio de búsqueda, en este caso dice que los registros a filtrar deben ser mayores o iguales a la fecha que está en I1, se utiliza CDate para convertir el dato en fecha y Format para que tome el formato dia/mes/año (por ejemplo 14/06/2020).
AND: se utiliza para excluir todos los registros que estén fuera del rango especificado de fechas así:
AND Fecha <= #» & Format(CDate(a.Range(«K1»)), «mm/dd/yyyy») & «#
Entonce con la primer parte del Where de la SQL se estableció el rango de inicio de la fecha y con la parte del párrafo anterior la última parte del rango de fechas.
Order By: especifica el orden en que se requieren los datos, fijense lo simple que es para ordenar por uno o varios criterios a diferencia de los códigos que se deben usar en VBA para ordenar los datos, en este ejemplo se solicita que se ordene por fecha en forma ascendente, si se omite los datos se muestran en el orden en que están registrados.
ORDER BY fecha ASC
Si se quisiera ordenar en forma descendente se usa:
ORDER BY fecha DESC
Si se quisiera ordenar por mas criterios se usa, suponiendo que tengamos un campo denominado Cliente, ordenamos primero por Fecha en forma descendente y luego por Cliente en forma ascendente:
ORDER BY fecha DESC, Cliente By ASC
Para aprender más sobre SQL y VBA EXCEL sigue el link, acá otros ejemplos que usan SQL con VBA, acá SQL y ACCESS, que SQL es lo mismo.
La SQL se leería así:
Seleccione todas las columnas de la tabla hoja1 cuando los registros de la columna llamada Fecha sea mayor o igual a la fecha de la celda I1 (fecha inicial del rango) y sea menor o igual a la fecha de la celda K1 (fecha final del rango); ordenando los datos por fecha en forma ascendente.
Siguiendo con el ejemplo, una vez realizada la conexión con el libro de Excel, hecha la SQL, se procede a crear un objeto integrados por los datos que se obtienen al ejecutar la SQL, con el siguiente código:
Set rs = cn.Execute(sql)
Los datos obtenidos se graban en el recordset osea se mantienen en memoria hasta que sean usados, esto consume recursos, por lo que se recomienda que la conexión y consulta de datos se realice al momento de que la macro los utilice e inmediatamente liberar la memoria.
Luego para pasar los daos del recordset o que están en memoria producto de la consulta realizada al ejecutar la SQL se utiliza el siguiente código:
b.Cells(2, 1).CopyFromRecordset Data:=rs
Los datos se copiaran en la hoja 2 fila 2 columna 1 es decir en la Celfa A2 de la hoja2, solamente los datos coincidente s con el rango de fecha que se ingreso en I1 y K1.
Luego esto es lo que había mencionado de liberar la memoria una vez usados los datos, se realiza con los siguientes códigos que permiten cerrar la conexión, borrar los datos del objeto rs, cerrar la conexión y borrar datos del objeto cn.
Por cierto si luego en la macro necesitamos hacer otra consulta no es conveniente cerrar la conexión sino solo borrar los datos del recordset, cuando ejecutemos una nueva SQL en la mima macro no se debe realizar la conexión de nuevo.
La macro denominada Como Conectar Excel con Excel Consulta SQL sobre un Rango de Fechas con datos en el mismo libro Excel se puede descargar desde el final y el siguiente es el código completo del ejemplo que se presentó.
Código que se inserta en un módulo
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»)
cn.Open «Provider=Microsoft.Jet.OLEDB.4.0;» & «Data Source=» & ThisWorkbook.FullName & «;Extended Properties=»»Excel 8.0;HDR=Yes;»»»
sql = «SELECT * FROM [» & «Hoja1$» & «] WHERE Fecha >= #» & Format(CDate(a.Range(«I1»)), «mm/dd/yyyy») & «# AND Fecha <= #» & Format(CDate(a.Range(«K1»)), «mm/dd/yyyy») & «# ORDER BY fecha ASC»
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 registros para el criterio de búsqueda»), vbInformation, «AVISO»
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub borrar()
Sheets(«Hoja2»).Cells.Clear
End Sub
.
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