Filtrar datos de un Listbox usando VBA, ADO y SQL

Conectando Excel a bases de datos: 8- Conectando Excel-Excel usando VBA, ADO y SQL. Rangos dinámicos

Conectando Excel a bases de datos: 8- Conectando Excel-Excel usando VBA, ADO y SQL. Rangos dinámicos

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 7- Conectando Excel con Excel usando VBA, ADO y SQL

Conectando Excel a bases de datos: 7- Conectando Excel con Excel usando VBA, ADO y SQL

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 5- Registros hacia Access con VBA, ADO y SQL (bucles y arrays)

Conectando Excel a bases de datos: 5- Registros hacia Access con VBA, ADO y SQL (bucles y array

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 4- Enviando registros hacia Access con VBA, ADO y SQL

Conectando Excel a bases de datos: 4- Enviando registros hacia Access con VBA, ADO y SQL

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 3- Conectándonos con SQL Server Express

Conectando Excel a bases de datos: 3- Conectándonos con SQL Server Express. ¡No dejes de verlo!

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 2- Conectándonos con MySQL (Local y web)

Conectando Excel a bases de datos: 2- Conectándonos con MySQL (Local y web). ¡No dejes de verlo!

¡Saludos!

Abraham Valencia
Lima, Perú

Conectando Excel a bases de datos: 1- Distintos modos de conectarse con Microsoft Access

Conectando Excel a bases de datos: 1- Distintos modos de conectarse con Microsoft Access. ¡No dejes de verlo!

¡Saludos!

Abraham Valencia
Lima, Perú

Excel y Access (IV): El uso de las fechas con WHERE y Between (SQL)

Tal y como comenté hace algunas semanas, hoy continuamos con SQL y Where, aplicado a fechas para importar datos a Excel desde Access. El archivo de ejemplo seguirá siendo el mismo que adjunté en el segundo artículo de esta serie, solo entren al enlace y descárguenlo: Enlace.

Lo primero es comentarle que cuando de fechas se trata, casi siempre hay complicaciones debido a que no es que todos los países usen el mismo tipo de formato; por ejemplo, en Perú el usado habitualmente es “dd/mm/yyyy” (entiéndase el “yyyy” como “aaaa”) pero en EE. UU. usan “mm/dd/yyyy”. Entonces, cuando usamos SQL ¿Cuál es el formato ideal? Pues sin duda yo les diría que es el siguiente: “yyyy-mm-dd” ¿Por qué? Porque SQL usa los estándares de la norma ISO 8601y respecto a fechas es la que les menciono. Entonces, por decirlo de un modo, con ese formato de fecha, no hay pierde.

Yendo al primer ejemplo, si queremos los datos de aquellas personas cuya fecha de nacimiento es menor al 01 de enero de 1978, basta colocar la sentencia de este modo:

sql = "SELECT * FROM Vendedores Where Nacimiento < #1978/01/01#"

Si se dan cuenta, coloqué la fecha en el formato que les mencioné previamente y he rodeado dicha fecha de almohadillas (#), eso último es importante para que el valor enviado se reconozca justamente como fecha. En nuestra hoja tendremos esto:

Ahora vamos más bien a obtener las fechas que son mayores al 01 de enero de 1978, es decir, aquellas posteriores. Basta cambiar el signo menor (<) por el signo mayor (>).

sql = "SELECT * FROM Vendedores Where Nacimiento > #1978/01/01#"

En esta ocasión tendremos esto en la hoja:

No olviden que también se puede usar el signo igual (=) junto con mayor o menor para tomar en cuenta la fecha que se usa en el criterio. Pero ¿qué pasas si lo que queremos obtener son los registros en un rango determinado de fechas? No hay problema, para eso tenemos a Between que es un operador que permite seleccionar valores entre un rango de datos, justamente. Between funciona del siguiente modo:

sql = "SELECT * FROM Vendedores Where Nacimiento BetWeen #1970-01-01# And #1979-12-01#" 

Para ese caso, estamos seleccionado las fechas entre el 01 de enero de 1970 y el 01 de diciembre de 1979. Al correr la macro este será el resultado:

Ojo con el uso del “And” y que la fecha inicial siempre va primero. Si quieren ir un poquito más allá y ordenar por fecha de naciemiento, basta agregar la cláusula “Order By” que justaente ordena los registros de una consulta de forma ascendente (por defecto) o descendente. Si queremos agregar eso, se hace de la siguiente forma:

sql = "SELECT * FROM Vendedores Where Nacimiento BetWeen #1970-01-01# And #1979-12-01# Order by Nacimiento"

Si queremos los datos de forma descendente, basta agregar lo siguiente:

sql = "SELECT * FROM Vendedores Where Nacimiento BetWeen #1970-01-01# And #1979-12-01# Order by Nacimiento Desc"

No olviden tomar muy en cuenta que, después de la clausula Order By se agrega el nombre del campo por le cual se quiere ordenar y, de ser necesario, se agrega el “Asc” o “Desc” antes del nombre del campo. Amigos/as, en esta ocasión vamos a llegar hasta ahí, en el próximo artículo usaremos variables y datos de celdas para construir nuestra cadena SQL, así como seleccionaremos solo algunos de los campos de la tabla del archivo Access.

¡Hasta la próxima!

Abraham Valencia
Lima, Perú

Excel y Access (II): Nuestro primer RecordSet. Extrayendo datos de Access

Hoy vamos con la segunda parte de como trabajar en Access desde Excel. Como ya se explicó la forma de lograr una conexión a Access desde Excel (Enlace), esta vez iremos un paso más allá, es decir, vamos a extraer datos de Access.

Como en esta ocasión vamos a comenzar con algo relativamente simple, supondremos que en nuestra base de datos solo tenemos una tabla con cinco campos.

Para extraer los datos vamos a usar un RecordSet. El objeto RecordSet de ADO se utiliza para mantener un conjunto de registros de una tabla de base de datos. Es importante aclarar que dicho objeto puede manejarse con dos métodos, Execute y Open del Recordset, ambos tienen tiene sus pros y/o sus contras; yo creo que es relativamente complicado decir cuál es mejor que el otro y diría, a su vez, que lo que hay que saber es cuándo es mejor usar un método u otro. Por ejemplo, para el caso de solo eliminar registros yo me inclinaría por Execute, pero para manejar algunas propiedades del RecordSet y sus datos, es mejor Open. A lo largo de estos artículos trataré, en lo posible, de poner ambos y/o si uso solo uno, aclarar porque es el método elegido e igual queda como tarea de ustedes leer más sobre dichos temas.

Volviendo a lo nuestro, como se supone que ya sabemos cómo lograr la conexión, vamos a ver lo del RecordSet. Declaramos la variable adecuada y creamos el objeto RecordSet del siguiente modo:

Dim rst As ADODB.Recordset 
Set rst = New ADODB.Recordset 

Como se dijo antes, para manipular, extraer, modificar, etc., los datos, también vamos a usar SQL y como las sentencias de dichos lenguajes se mandan como texto, vamos a crear una variable del tipo String para ello.

Dim SQL as String

Como en esta ocasión supondremos que queremos todos los registros de la única tabla, usamos las sentencias respectivas: Select, para seleccionar los campos, usamos asterisco (*) para indicar que son todos los campos los que nos interesan, From para seleccionar la tabla, y por último el nombre de la tabla.

SQL = “Select * From Vendedores”

Como ya está creado el RecordSet y tenemos la sentencia SQL requerida, solo falta abrir dicho objeto con los datos.

rst.Open Sql, cnn

No olvidar que cnn es la variable de la conexión que usamos en el artículo anterior. Por cierto, aquí estamos yendo al grano, si quieren profundizar más en los RecorsSet, verán que hay algunas propiedades del método Open que podrían usar, de ser necesario.

Finalmente, en el entendido que necesitamos todos los datos y no solo algunos y/o recorrer por algún motivo todos los registros, pegamos directamente los registros a la hoja con una sola instrucción, el método CopyFromRecordset.

Range("A2").CopyFromRecordset rst

Si todo estuvo bien, en su hoja tendrán lo siguiente:

Ah, como ven, no están los nombres de los campos, pero eso lo dejaremos para la siguiente vez. Por cierto, todo junto se vería así:

Option Explicit
'Todo Sobre Excel 
'Abraham Valencia 
'https://abrahamexcel.blogspot.com/ 
'https://www.facebook.com/TodosobreExcelAV/ 
'https://twitter.com/Todosobre_Excel 
'https://www.youtube.com/channel/UCxEe3aA5uGrtYDdboBT_ptg 
'Lima, Perú 
'Julio del 2022 

Sub MiPrimerRecordSet() 

Dim cnn As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim sql As String 

Set cnn = New ADODB.Connection 
Set rst = New ADODB.Recordset 

With cnn
	.Provider = "Microsoft.ACE.OLEDB.12.0"     
	.ConnectionString = "data source=" & ThisWorkbook.Path & "\Ejemplo.accdb"     
	.Open 
End With 

sql = "Select * From Vendedores" 

rst.Open sql, cnn 

Range("A2").CopyFromRecordset rst 

'Cerrar el RecordSet y la conexión no necesariamente es obligatorio 
rst.Close 
cnn.Close 

'Como les he dicho varias veces, vaciar las variables no es obligatorio, pero lo pongo para conocimiento 
sql = vbNullString 
Set rst = Nothing 
Set cnn = Nothing 

End Sub

Para conocimiento, una forma corta de lograr lo mismo sería esta:

With CreateObject("ADODB.Recordset")     
	.Open "Select * From Vendedores", _         "
		Data Source=" & ThisWorkbook.Path & "\Ejemplo.accdb;Provider=Microsoft.ACE.OLEDB.12.0"  
	Range("A2").CopyFromRecordset .DataSource 
End With

Y eso es todo por hoy, espero les haya gustado. Esta historia continuará.

Abraham Valencia
Lima, Perú

Descargue el ejemplo aquí: Enlace

Excel y Access (I): Realizando nuestra primera conexión a un archivo Access

En muchas ocasiones, y durante años, he leído/visto preguntas relacionadas a cómo usar datos almacenados en Access, pero a través de Excel, así que hoy comenzaremos a ver cómo se hace eso paso a paso.

Lo primero es comentar que usaremos ADO para lo mencionado. Por si aún no saben lo que es ADO, son las iniciales de ActiveX Data Objects, que es un mecanismo para comunicarse con bases de datos y así poder trabajar con sus datos. Una de las ventajas es su compatibilidad con VBA y, además, permite conexiones con Access. También vamos a usar los Recordset, que son estructuras de datos cuya utilidad es la de almacenar información desde una tabla. Por último, para el manejo de datos usaremos SQL (por sus siglas en inglés Structured Query Language), que es un lenguaje de consulta estructurada que sirve para administrar y recuperar información de sistemas de gestión de bases de datos relacionales. Ojo, hay otras formas de hacer conexiones o utilizar otras propiedades y/o formas de manipular registros, pero yo prefiero las recomendadas y que usaré ahora. Ah, si bien ya he colocado ejemplos de todo esto en el blog e incluso he publicado un par de artículos sobre conexiones de Excel con MySQL, lo que haré a partir de hoy con Access será mucho más secuencial, estructurado y detallado a través de más de un artículo; de ese modo incluso podrían aplicarlo a otros tipos de bases de datos.

Ahora sí, regresando a Excel y Access, en esta ocasión, por ser la primera, básicamente vamos a establecer la conexión a una base de datos de Access, para eso lo que haremos ahora es activar la referencia a “Microsoft ActiveX Data Object 6.1 Library”, en donde el 6.1 puede variar dependiendo de tu versión de Office y/o la librería que desees usar.

Lo primero que haremos será declarar la variable (de objeto) que usaremos para la conexión:

Dim cnn As ADODB.Connection

Luego daremos valor a esa variable asignándole una conexión:

Set cnn = New ADODB.Connection

Del objeto conexión de ADO, lo que nos interesa son dos propiedades y un método: ConnectionString, Provider y Open, respectivamente (para ver otras puede entrar aquí: Enlace).

Cuando hablamos de Provider o proveedor, hacemos referencia a un conjunto de bibliotecas que se utiliza para comunicarse con una fuente de datos. En este caso vamos a usar un archivo Access del tipo *.accdb llamado "Ejemplo", por lo que necesitaremos tener el instalado “Microsoft.ACE.OLEDB.12.0”. Si de casualidad no lo tienen, pueden descargarlo de aquí: Enlace. A la propiedad Provider vamos a darle justamente dicho valor.

Para la propiedad ConnectionString podemos fácilmente recurrir a esta web para adaptar a nuestra necesidad: Enlace.

En el caso de método Open, basta llamarlo para establecer la conexión con el origen de datos.

Entonces, dicho eso, y en el supuesto que vamos a trabajar con el archivo Excel en la misma carpeta que nuestro archivo Access, esta parte quedaría así:

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "data source=" & ThisWorkbook.Path & "\Ejemplo.accdb"
    .Open
End With

La cantidad de líneas incluso podrían ser menos, pero eso ya queda como tarea para ustedes ya que ko importante hoy es aprender y que se entienda todo.

Podríamos tener todo junto de este modo:

Option Explicit 
'Todo Sobre Excel 
'Abraham Valencia 
'https://abrahamexcel.blogspot.com/ 
'https://www.facebook.com/TodosobreExcelAV/ 
'https://twitter.com/Todosobre_Excel 
'https://www.youtube.com/channel/UCxEe3aA5uGrtYDdboBT_ptg 
'Lima, Perú 
'Julio del 2022 

Sub MiPrimeraConexion()

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "data source=" & ThisWorkbook.Path & "\Ejemplo.accdb"
    .Open
End With

MsgBox "Conexión realizada con exito", vbOKOnly, "Todo Sobre Excel"

cnn.Close 'Esto cierra la conexión pero no es obligatorio ponerlo para este caso

Set cnn = Nothing 'Esto descarga la variable, pero en realidad el End Sub también lo hace
 
End Sub

Entonces amigos y amigas, hoy hemos aprendido a conectar, sé que probablemente estén con ansiedad de seguir, pero vamos paso a paso, aspí que eso es todo por hoy. Hasta la próxima!

Abraham Valencia
Lima, Perú