Patrón de codificación VBA: Importación de resultados de consultas de base de datos a Excel

Presentamos el patrón de codificación VBA que implementa la importación de resultados de DB Query a Excel.

Artículos relacionados: Patrón de codificación VBA: Crear cadena de conexión ADO DB

1. Pantalla de Excel y método de operación

1.1. pantalla de excel

Ingrese la cadena de conexión para conectarse a la base de datos en la celda "B1", e ingrese la consulta SQL para extraer datos en la celda "B2".

Haga clic en "Herramientas de desarrollo > Insertar > Control ActiveX > Botón de comando" para agregar un botón y cambiar el nombre a "Ejecutar consulta".

Una pantalla de ejemplo es la siguiente.

1.2. Cómo importar resultados de DB Query a Excel

Funciona de la misma manera que la imagen de arriba.

1. Haga clic en el botón "Ejecutar consulta" agregado a Excel
2. Conexión DB desde código VBA
3. Ejecución de consultas en la base de datos conectada
4. Devuelve el resultado de la consulta ejecutada en DB
5. Mostrar el resultado de la consulta en la hoja de Excel

2. Seleccione el proveedor para la conexión a la base de datos

“2. Este es el proveedor utilizado en "Conexión DB en código VBA". Se puede especificar como la propiedad Provider del objeto ADO.Connection o como ConnectionString.

Por lo general, se designa como ConnectionString y un ejemplo es el siguiente.

• Proveedor MS OLE DB para controladores ODBC
  • Proveedor=MSDASQL.1; Contraseña=[contraseña]; Información de seguridad persistente = Verdadero; ID de usuario=[ID de usuario]; Fuente de datos=[DSN establecido en el panel de control ODBC]; Catálogo inicial=[Nombre de base de datos]
• MS OLE DB para SQL Server (Autenticación de SQL Server)
  • Proveedor=SQLOLEDB.1; Contraseña=[contraseña]; Información de seguridad persistente = Verdadero; ID de usuario=[ID de usuario]; Catálogo inicial=[Nombre de base de datos];Fuente de datos=[Dirección del servidor (IP o nombre de host)]
• MS OLE DB para SQL Server (autenticación de Windows)
  • Proveedor=SQLOLEDB.1; Contraseña=[contraseña]; Seguridad Integrada=SSPI; Información de seguridad persistente = Falso; ID de usuario=[ID de usuario]; Catálogo inicial=[Nombre de base de datos];Fuente de datos=[Dirección del servidor (IP o nombre de host)]
• Proveedor de Oracle para OLE DB
  • Proveedor=OraOLEDB.Oracle.1;Contraseña=[contraseña]; Información de seguridad persistente = Verdadero; ID de usuario=[ID de usuario]; Fuente de datos=[Dirección del servidor (IP o nombre de host)]

Si se instala un proveedor adecuado en la PC, puede conectarse a servidores RDBMS como Oracle, MS SQL Server, MySQL, MariaDB y PostgreSQL, y también conectarse a RDBMS locales como MS Access y SQLite.

ConnectionString se puede crear de la siguiente manera.

Cómo crear ConnectionString 1. Usando un archivo UDL

Los archivos Universal Data Link (UDL) son un concepto incluido en Microsoft Data Access Components (MDAC).

Referencia: Configurar enlaces de datos universales (UDL): controlador OLE DB para SQL Server | Documentos de Microsoft

Ejecute el siguiente procedimiento.

1. Haga clic derecho en el escritorio> Nuevo> Documento de texto
2. Cambie el nombre del archivo a test.udl y presione enter
3. Haga doble clic para abrir el archivo test.udl, seleccione un proveedor en la pestaña "Proveedor", ingrese el servidor, nombre de usuario, contraseña, base de datos, etc. en la pestaña "Conexión", "Probar conexión" y luego "Aceptar " ahorrar.
4. ConnectionString se puede obtener abriendo el archivo test.udl guardado con un editor (Bloc de notas, etc.)

En este método, el proveedor se muestra según la plataforma de 32 o 64 bits del sistema operativo Windows. Es decir, solo los proveedores de 64 bits se muestran en el sistema operativo de 64 bits. La forma de mostrar el proveedor de 32 bits en un sistema operativo de 64 bits se publicará en un artículo separado.

Cómo crear ConnectionString 2. Código VBA

Publicación anterior Patrón de codificación VBA: Crear cadena de conexión ADO DB ha sido explicado en detalle en En resumen.

• Se agregó la referencia de la biblioteca Microsoft ActiveX Data Objects 2.8
• Se agregó la referencia de la biblioteca de tipos de Microsoft OLE DB Service Component 1.0
• Crea un ConnectionString con el siguiente código

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

Precauciones relacionadas con el proveedor

Las plataformas de 32 bits y 64 bits del Proveedor a utilizar y Excel deben ser las mismas. Si usa Excel de 32 bits, debe usar un proveedor de 32 bits y si usa Excel de 63 bits, debe usar un proveedor de 64 bits.

En caso de utilizar el sistema operativo Windows de 64 bits y Excel de 32 bits, al intentar conectarse a la base de datos con el ConnectionString creado en el primer método, puede ocurrir un error de "no se puede encontrar el proveedor".

Este caso se puede solucionar comprobando si las plataformas de Excel y el proveedor son los mismos y utilizando un proveedor adecuado.

3. Implementación de la importación de resultados de DB Query a Excel

3.1. Añadir referencia

Ejecute "Herramientas > Agregar referencia" en el menú del editor de código VBA y agregue la referencia "Microsoft ActiveX Data Objects 2.8 Library".

3.2. Código VBA para importar resultados de DB Query a Excel

Escriba el siguiente código en el procedimiento de evento de clic del botón agregado a la hoja de Excel.

Private Sub cmd_RunQuery_Click()
    Dim sSQL As String
    Dim oCon As ADODB.Connection, oRS As ADODB.Recordset
    Dim sConnectionString As String

    sConnectionString = Range("B1").Value2
    sSQL = Range("B2").Value2

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oCon.ConnectionTimeout = 10
    oCon.Open sConnectionString
    oCon.CommandTimeout = 60

    oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly
    
    Dim oSht As Worksheet, lColIdx As Long
    Set oSht = Worksheets.Add(After:=Me)
    For lColIdx = 0 To oRS.Fields.Count - 1
        oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name
    Next lColIdx
    oSht.Range("A2").CopyFromRecordset oRS

    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
End Sub

La explicación del código anterior es la siguiente.

• Línea 6: obtenga el valor de la celda "B1" de la hoja de Excel como la variable sConnectionString.
• Línea 7: Importe el valor de la celda "B2" de la hoja de Excel en la variable sSQL.
• Línea 12: conéctese a la base de datos pasando el valor sConnectionString al objeto ADODB.Connection oCon.
• Línea 15: Ejecute la consulta SQL en la base de datos y obtenga el resultado en el objeto ADODB.Recordset oRS.
• Línea 18: cree una hoja oSht para mostrar el resultado de la ejecución de la consulta SQL.
• Líneas 19-21: Muestra la lista de campos (columnas) del objeto oRS en la primera fila de la hoja oSht.
• Línea 22: muestra el contenido de oRS (resultado de ejecución de consulta SQL) en la celda "A2" de la hoja oSht.

3.3. Descargar archivo de ejemplo

Puede consultar el archivo de Excel de ejemplo que contiene el código descrito anteriormente en el enlace de github a continuación.
Enlace de archivo: github DAToolset/VBACode/Patrones de codificación VBA - DB Query1.xlsm