Usando Excel VBA para consultar una base de datos de SQL Server

Aunque podemos crear conjuntos de datos externos en Excel que tiran de la información de una base de datos de SQL Server hay veces que se quiere comprobar un valor o buscar un valor de una base de datos de SQL Server subyacente directamente desde una fórmula de Excel.

Vamos a ver un ejemplo que nos permite buscar el valor de los ingresos totales de un cliente de una fórmula de Excel.

El VBA para consultar una base de datos de SQL Server

La siguiente función VBA se puede utilizar desde un proyecto de Excel VBA. Sin embargo, tendrá que añadir la biblioteca ADODB al proyecto primero.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnString As String If intID = 0 Then LookupAWCustomerRevenue = 0 Else strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" Set conn = New ADODB.Connection conn.Open strConnString Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID) If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0 Else LookupAWCustomerRevenue = rs.Fields("CustRev").Value rs.Close End If End If End Function

En este ejemplo utilizamos una conexión ADO para conectarnos a una instancia de SQL Server (DBSRV\SQL2014):

 strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _ & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• El parámetro Proveedor indica que se establecerá una conexión OLDEB y el parámetro Origen de datos apunta a la instancia de SQL Server.
• El parámetro Initial Catalog identifica la base de datos a consultar (AdventureWorks2014)
• El parámetro Integrated Security indica que se utilizará Windows Authemtication para autenticarse con SQL Server.

Un objeto RecordSet (rs) se utiliza para crear un conjunto de registros a partir de una sentencia SELECT:

 Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

La sentencia SELECT se construye a partir de una cadena literal y el valor de la variable intID que se pasa a la función cuando se llama.

La sentencia If al principio comprueba si el valor de intID es 0. Las variables enteras tienen por defecto el valor cero si no se inicializan (en otras palabras, si no se proporciona ningún valor cuando se llama a la función). Si no se pasa ningún valor a la función, se devuelve el valor 0 como valor de los ingresos.

 If intID = 0 Then LookupAWCustomerRevenue = 0

La segunda sentencia If comprueba si la sentencia SELECT devuelve un valor no numérico. Si el ID del cliente que se pasa a la función es válido, pero no ha realizado ningún pedido, la expresión SUM(TotalDue) devolverá un valor NULL. Si esto sucede, entonces la función devolverá un valor de 0 en su lugar.

 If Not IsNumeric(rs.Fields("CustRev").Value) Then LookupAWCustomerRevenue = 0

El escenario del valor NULL se puede ver en la siguiente captura de pantalla.

Puse un punto de interrupción en la línea de código VBA que contiene la sentencia If y abrí la Ventana de Locales para poder ver todos los valores de las variables en ese punto de la ejecución.

Probé la función ejecutándola desde la Ventana Inmediata en el editor de VBA:

 ?LookupAWCustomerRevenue(1)

Con un punto de interrupción colocado la ejecución del código se detiene automáticamente en la línea marcada y nos permite ver el entorno en ese punto de ejecución.

La Ventana de Locales en la captura de pantalla anterior muestra la variable del objeto Recordset rs, y específicamente el valor para el primer campo de rs, «CustRev». Podemos ver que está establecido como Null. Esto se debe a que un cliente con el valor de CustomerID 1 no ha realizado ningún pedido y, por lo tanto, no tiene ningún valor de ingresos resultante.

La siguiente captura de pantalla muestra que la consulta devuelve NULL cuando se ejecuta directamente en la instancia de SQL Server:

Suponiendo que un CustomerID válido se pasa a la función y un valor no NULL es devuelto por la sentencia SELECT, la función devolverá el total de ingresos por ventas para ese cliente como un valor de moneda.

 LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Llamar a la función VBA desde una fórmula de Excel

Llamar a una función VBA desde una fórmula de Excel es sencillo. Cuando usted construye una Fórmula de Excel escribiéndola en una celda, intellisense mostrará las funciones VBA correspondientes, así como las funciones incorporadas. La siguiente captura de pantalla muestra esto:

Puede ver que hay dos funciones VBA listadas arriba que comienzan con Lo: LookupAWCustomerRevenue y LookupPersonName.

El siguiente ejemplo muestra la función LookupAWCustomerRevemue que se utiliza en una celda para calvular el total de ingresos para el valor de CustomerID sepcified en la columna A de la hoja de trabajo:

Summary

En este artículo hemos visto un poco de codificación VBA, algunas técnicas de manejo de errores y depuración de VBA, y hemos visto cómo podemos llamar a las funciones VBA de una fórmula de Excel. No dude en enviarnos un correo electrónico a [email protected] si tiene alguna pregunta relacionada con este artículo. También hay una versión de acceso de este artículo.

Usted puede encontrar que estos cursos vienen en una oferta de disponibilidad tardía de vez en cuando, ofreciendo un ahorro del 30%.