Transferencia de datos de un conjunto de registros de ADO a Excel con automatización

Artículo
06/20/2023
Se aplica a:

Excel 2007, Excel 2003, Excel 2002, Excel 2000, Excel 97

Resumen

Puede transferir el contenido de un conjunto de registros de ADO a una hoja de cálculo de Microsoft Excel mediante la automatización de Excel. El enfoque que puede usar depende de la versión de Excel que esté automatizando. Excel 97, Excel 2000 y Excel 2002 tienen un método CopyFromRecordset que puede usar para transferir un conjunto de registros a un rango. CopyFromRecordset en Excel 2000 y 2002 se puede usar para copiar un DAO o un conjunto de registros de ADO. Sin embargo, CopyFromRecordset en Excel 97 solo admite conjuntos de registros DAO. Para transferir un conjunto de registros de ADO a Excel 97, puede crear una matriz a partir del conjunto de registros y, a continuación, rellenar un rango con el contenido de esa matriz.

En este artículo se describen ambos enfoques. El código de ejemplo presentado muestra cómo se puede transferir un conjunto de registros de ADO a Excel 97, Excel 2000, Excel 2002, Excel 2003 o Excel 2007.

Más información

El ejemplo de código que se proporciona a continuación muestra cómo copiar un conjunto de registros de ADO en una hoja de cálculo de Microsoft Excel mediante la automatización de Microsoft Visual Basic. El código comprueba primero la versión de Excel. Si se detecta Excel 2000 o 2002, se usa el método CopyFromRecordset porque es eficaz y requiere menos código. Sin embargo, si se detecta Excel 97 o una versión anterior, el conjunto de registros se copia primero en una matriz mediante el método GetRows del objeto recordset de ADO. A continuación, se transpone la matriz para que los registros estén en la primera dimensión (en filas) y los campos estén en la segunda dimensión (en columnas). A continuación, la matriz se copia en una hoja de cálculo de Excel mediante la asignación de la matriz a un rango de celdas. (La matriz se copia en un paso en lugar de recorrer en bucle cada celda de la hoja de cálculo).

El ejemplo de código usa la base de datos de ejemplo Northwind que se incluye con Microsoft Office. Si seleccionó la carpeta predeterminada al instalar Microsoft Office, la base de datos se encuentra en:

\Archivos de programa\Microsoft Office\Office\Samples\Northwind.mdb

Si la base de datos Northwind se encuentra en una carpeta diferente del equipo, debe editar la ruta de acceso de la base de datos en el código proporcionado a continuación.

Si no tiene la base de datos Northwind instalada en el sistema, puede usar la opción Agregar o quitar para el programa de instalación de Microsoft Office para instalar las bases de datos de ejemplo.

Nota La base de datos Northwind no se instala al instalar 2007 Microsoft Office. Para obtener Northwind 2007, visite el siguiente sitio web de Microsoft:

Temas de plantillas de & Office

Pasos para crear ejemplo

Inicie Visual Basic y cree un nuevo proyecto EXE estándar. Form1 se crea de forma predeterminada.
Agregue un control CommandButton a Form1.
Haga clic en Referencias en el menú Proyecto. Agregue una referencia a la biblioteca microsoft ActiveX Data Objects 2.1.

Pegue el código siguiente en la sección de código de Form1:

Private Sub Command1_Click()
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset

Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

Dim recArray As Variant

Dim strDB As String
    Dim fldCount As Integer
    Dim recCount As Long
    Dim iCol As Integer
    Dim iRow As Integer

' Set the string to the path of your Northwind database
    strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"

' Open connection to the database
    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDB & ";"

''When using the Access 2007 Northwind database
    ''comment the previous code and uncomment the following code.
    'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    '    "Data Source=" & strDB & ";"

' Open recordset based on Orders table
    rst.Open "Select * From Orders", cnt

' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True

' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next

' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
        xlWs.Cells(2, 1).CopyFromRecordset rst
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets

Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel

' Copy recordset to an array
        recArray = rst.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array

' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For iCol = 0 To fldCount - 1
            For iRow = 0 To recCount - 1
                ' Take care of Date fields
                If IsDate(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = "Array Field"
                End If
            Next iRow 'next record
        Next iCol 'next field

' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
            TransposeDim(recArray)
    End If

' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

Set xlApp = Nothing

End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

TransposeDim = tempArray

End Function

Presione la tecla F5 para ejecutar el proyecto. Aparece Form1.
Haga clic en commandbutton en Form1 y tenga en cuenta que el contenido de la tabla Orders se muestra en un nuevo libro en Excel.

Uso de CopyFromRecordset

Para mejorar la eficacia y el rendimiento, CopyFromRecordset es el método preferido. Dado que Excel 97 solo admite conjuntos de registros DAO con CopyFromRecordset, si intenta pasar un conjunto de registros de ADO a CopyFromRecordset con Excel 97, recibirá el siguiente error:

Error en tiempo de ejecución 430: la clase no admite Automation o no admite la interfaz esperada. En el ejemplo de código, puede evitar este error comprobando la versión de Excel para que no use CopyFromRecordset para la versión 97.

Nota Al usar CopyFromRecordset, debe tener en cuenta que el conjunto de registros de ADO o DAO que usa no puede contener campos de objeto OLE ni datos de matriz, como conjuntos de registros jerárquicos. Si incluye campos de cualquier tipo en un conjunto de registros, el método CopyFromRecordset producirá el siguiente error:

Error en tiempo de ejecución -2147467259: Error del método CopyFromRecordset del objeto Range.

Uso de GetRows

Si se detecta Excel 97, use el método GetRows del conjunto de registros de ADO para copiar el conjunto de registros en una matriz. Si asigna la matriz devuelta por GetRows a un rango de celdas de la hoja de cálculo, los datos atraviesan las columnas en lugar de bajar las filas. Por ejemplo, si el conjunto de registros tiene dos campos y 10 filas, la matriz aparece como dos filas y 10 columnas. Por lo tanto, debe transponer la matriz mediante la función TransposeDim() antes de asignar la matriz al rango de celdas. Al asignar una matriz a un rango de celdas, hay algunas limitaciones que debe tener en cuenta:

Las limitaciones siguientes se aplican al asignar una matriz a un objeto Range de Excel:

La matriz no puede contener campos de objeto OLE ni datos de matriz, como conjuntos de registros jerárquicos. Observe que el ejemplo de código comprueba esta condición y muestra "Campo de matriz" para que el usuario tenga en cuenta que el campo no se puede mostrar en Excel.
La matriz no puede contener campos Date que tengan una fecha anterior al año 1900. (Consulte la sección "Referencias" para obtener un vínculo de artículo de Microsoft Knowledge Base). Tenga en cuenta que el ejemplo de código da formato a los campos Date como cadenas variantes para evitar este posible problema.

Observe el uso de la función TransposeDim() para transponer la matriz antes de copiar la matriz en la hoja de cálculo de Excel. En lugar de crear su propia función para transponer la matriz, puede usar la función Transponer de Excel modificando el código de ejemplo para asignar la matriz a las celdas como se muestra a continuación:

   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)

Si decide usar el método Transpose de Excel en lugar de la función TransposeDim() para transponer la matriz, debe tener en cuenta las siguientes limitaciones con el método Transpose:

La matriz no puede contener un elemento que tenga más de 255 caracteres.
La matriz no puede contener valores Null.
El número de elementos de la matriz no puede superar los 5461.

Si las limitaciones anteriores no se tienen en cuenta al copiar una matriz en una hoja de cálculo de Excel, puede producirse uno de los siguientes errores en tiempo de ejecución:

Error 13 en tiempo de ejecución: error de coincidencia de tipo

Error 5 en tiempo de ejecución: procedimiento no válido

call o argumento Error en tiempo de ejecución 1004: error definido por la aplicación o definido por el objeto

Referencias

Para obtener información adicional sobre las limitaciones al pasar matrices a varias versiones de Excel, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

177991 XL: Limitaciones de pasar matrices a Excel mediante automatización

247412 INFO: métodos para transferir datos a Excel desde Visual Basic