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

Seleccione idioma Seleccione idioma
Id. de artículo: 246335 - Ver los productos a los que se aplica este artículo
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.

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

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:

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:
177991XL: 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:
146406XL: Cómo recuperar una tabla de Access en Excel con DAO
215965XL2000: 12: 00: 00 A.M. aparece para fechas anteriores A 1900
243394Cómo usar MFC para copiar un conjunto de registros a Excel con automatización
247412INFORMACIÓN: Métodos para transferir datos a Excel desde Visual Basic

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

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com