Cómo transferir datos desde un conjunto de registros ADO a Excel con automatización

Id. de artículo: 246335 - Ver los productos a los que se aplica este artículo
Traducción automáticaAdvertencia: Artículo de Traducción Automática, vea la exención de responsabilidad.
Expandir todo | Contraer todo

En esta página

Resumen

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

Este artículo describen ambos enfoques. El código de ejemplo presentado ilustra cómo puede transferir un conjunto de registros ADO a Excel 97, Excel 2000, Excel 2002, Excel 2003 o Excel 2007.
Volver al principio | Enviar comentarios

Más información

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

El ejemplo de código utiliza la base de datos Neptuno que se incluye con Microsoft Office. Si ha seleccionado la carpeta predeterminada cuando instaló Microsoft Office, la base de datos se encuentra en:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Si Northwind base de datos se encuentra en una carpeta diferente en el equipo, deberá modificar la ruta de acceso de la base de datos en el código proporcionado por debajo.

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

Nota La base de datos Northwind no está instalado cuando instala Microsoft Office 2007. Para obtener Neptuno 2007, visite el siguiente sitio Web de Microsoft:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx
(http://office.microsoft.com/en-us/templates/TC012289971033.aspx)

Pasos para crear ejemplo

  1. Inicie Visual Basic y cree un nuevo proyecto EXE estándar. Se creará Form1 de manera predeterminada.
  2. Agregue un CommandButton a Form1.
  3. Haga clic en referencias en el menú proyecto . Agregue una referencia a la Biblioteca Microsoft ActiveX Data Objects 2.1 .
  4. Pegar 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
  5. Presione la tecla F5 para ejecutar el proyecto. Aparecerá Form1.
  6. Haga clic en el CommandButton en Form1 y observe que se muestra el contenido de la tabla Pedidos en un nuevo libro en Excel.
mediante CopyFromRecordset

Para la eficiencia y rendimiento, CopyFromRecordset es el método preferido. Puesto que Excel 97 admite sólo la conjuntos de registros DAO con CopyFromRecordset, si se intenta pasar un conjunto de registros ADO a CopyFromRecordset con Excel 97, recibe el error siguiente:
Error en tiempo de ejecución 430:
Clase no admite automatización o no admite interfaz esperada.
En el ejemplo de código, puede evitar este error mediante la comprobación de Excel versión para que no utilizar CopyFromRecordset para la versión 97.

Nota Cuando se utiliza CopyFromRecordset, debe tener en cuenta que no contiene el conjunto de registros DAO o ADO que utiliza los campos de objeto OLE o datos de matriz como conjuntos de registros jerárquicos. Si incluye campos de cualquier tipo de un conjunto de registros, el método CopyFromRecordset se produce el siguiente error:
Se ha producido el error -2147467259 en tiempo de ejecución:
Método Error del CopyFromRecordset del objeto Range.
utilizar GetRows

Si se detecta Excel 97, utilice el método GetRows del conjunto de registros 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 va en las columnas en lugar de en las filas. Por ejemplo, si el conjunto de registros tiene dos campos y diez filas, la matriz aparece como dos filas y 10 columnas. Por lo tanto, deberá transponer la matriz utilizando la función TransposeDim() antes de asignar la matriz en el rango de celdas. Cuando se asignar una matriz a un rango de celdas, hay algunas limitaciones que hay que tener en cuenta:

Las siguientes limitaciones al asignar una matriz a un objeto Range de Excel:
  • La matriz no puede contener campos de objetos OLE o datos de matriz, como los 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 se realiza tenga en cuenta que el campo no puede mostrarse en Excel.

  • La matriz no puede contener campos de fecha que tienen una fecha anterior al año 1900. (Consulte la sección de "Referencias" un vínculo de Microsoft Knowledge Base el artículo). Tenga en cuenta que el código de ejemplo formatos de los campos de fecha como cadenas para evitar este problema potencial.
Nota el uso de la función TransposeDim() para transponer la matriz antes de la matriz se copia en la hoja de cálculo de Excel. En lugar de crear su propia función para transponer la matriz, puede utilizar Excel Transponer función modificando el código de ejemplo asigne 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 utilizar Transponer método Excel en lugar de la función TransposeDim() para transponer la matriz, debe conocer las limitaciones siguientes con el método Transponer:
  • La matriz no puede contener un elemento que es mayor que 255 caracteres.
  • La matriz no puede contener valores nulos.
  • El número de elementos de la matriz no puede superar 5461.
Si las limitaciones anteriores no se toman en consideración al copiar una matriz a una hoja de cálculo de Excel, puede aparecer uno de los siguientes errores de tiempo de ejecución:
Error de tiempo de ejecución 13: Tipo no coincidente
Tiempo de ejecución error 5: Llamada a procedimiento no válido o el argumento
Objeto definido por error o definido por aplicación en la 1004 de error de tiempo de ejecución:
Volver al principio | Enviar comentarios

Referencias

Para obtener información adicional acerca de las limitaciones en pasar matrices a distintas versiones de Excel, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
177991
(http://support.microsoft.com/kb/177991/ )
XL: Limitaciones de pasar matrices a Excel con automatización
Para obtener información adicional, haga clic en los números de artículo correspondientes para verlos en Microsoft Knowledge Base:
146406
(http://support.microsoft.com/kb/146406/EN-US/ )
XL: Cómo recuperar una tabla de Access en Excel con DAO
215965
(http://support.microsoft.com/kb/215965/ )
XL2000: 12: 00: 00 A.M. aparece para fechas anteriores A 1900
243394
(http://support.microsoft.com/kb/243394/ )
Cómo usar MFC para copiar un conjunto de registros a Excel con automatización
247412
(http://support.microsoft.com/kb/247412/ )
INFORMACIÓN: Métodos para transferir datos a Excel desde Visual Basic
Volver al principio | Enviar comentarios

Propiedades

Id. de artículo: 246335 - Última revisión: miércoles, 28 de marzo de 2007 - Versión: 5.1
La información de este artículo se refiere a:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
Palabras clave: 
kbmt kbexpertiseinter kbautomation kbhowto KB246335 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 246335
(http://support.microsoft.com/kb/246335/en-us/ )
Volver al principio | Enviar comentarios

Enviar comentarios

 
Volver al principioVolver al principio