Comment transférer des données d’un jeu d’enregistrements ADO vers Excel avec l’automatisation

Article
06/20/2023
S’applique à:

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

Résumé

Vous pouvez transférer le contenu d’un jeu d’enregistrements ADO vers une feuille de calcul Microsoft Excel en automatisant Excel. L’approche que vous pouvez utiliser dépend de la version d’Excel que vous automatiserez. Excel 97, Excel 2000 et Excel 2002 ont une méthode CopyFromRecordset que vous pouvez utiliser pour transférer un jeu d’enregistrements vers une plage. CopyFromRecordset dans Excel 2000 et 2002 peut être utilisé pour copier un jeu d’enregistrements DAO ou ADO. Toutefois, CopyFromRecordset dans Excel 97 prend en charge uniquement les jeux d’enregistrements DAO. Pour transférer un jeu d’enregistrements ADO vers Excel 97, vous pouvez créer un tableau à partir du jeu d’enregistrements, puis remplir une plage avec le contenu de ce tableau.

Cet article décrit les deux approches. L’exemple de code présenté illustre comment transférer un jeu d’enregistrements ADO vers Excel 97, Excel 2000, Excel 2002, Excel 2003 ou Excel 2007.

Informations supplémentaires

L’exemple de code fourni ci-dessous montre comment copier un jeu d’enregistrements ADO dans une feuille de calcul Microsoft Excel à l’aide de l’automatisation de Microsoft Visual Basic. Le code vérifie d’abord la version d’Excel. Si Excel 2000 ou 2002 est détecté, la méthode CopyFromRecordset est utilisée, car elle est efficace et nécessite moins de code. Toutefois, si Excel 97 ou version antérieure est détecté, le jeu d’enregistrements est d’abord copié dans un tableau à l’aide de la méthode GetRows de l’objet recordset ADO. Le tableau est ensuite transposé afin que les enregistrements se trouvent dans la première dimension (en lignes) et que les champs se trouvent dans la deuxième dimension (en colonnes). Ensuite, le tableau est copié dans une feuille de calcul Excel en affectant le tableau à une plage de cellules. (Le tableau est copié en une étape au lieu de parcourir en boucle chaque cellule de la feuille de calcul.)

L’exemple de code utilise l’exemple de base de données Northwind inclus dans Microsoft Office. Si vous avez sélectionné le dossier par défaut lorsque vous avez installé Microsoft Office, la base de données se trouve dans :

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

Si la base de données Northwind se trouve dans un autre dossier sur votre ordinateur, vous devez modifier le chemin de la base de données dans le code fourni ci-dessous.

Si la base de données Northwind n’est pas installée sur votre système, vous pouvez utiliser l’option Ajouter/Supprimer pour le programme d’installation de Microsoft Office pour installer les exemples de bases de données.

Note La base de données Northwind n’est pas installée lorsque vous installez Microsoft Office 2007. Pour obtenir Northwind 2007, visitez le site web Microsoft suivant :

Thèmes de modèles & Office

Étapes de création d’un exemple

Démarrez Visual Basic et créez un projet EXE standard. Form1 est créé par défaut.
Ajoutez un CommandButton à Form1.
Cliquez sur Références dans le menu Projet. Ajoutez une référence à la bibliothèque Microsoft ActiveX Data Objects 2.1.

Collez le code suivant dans la section de code 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

Appuyez sur la touche F5 pour exécuter le projet. Form1 s’affiche.
Cliquez sur CommandButton sur Form1 et notez que le contenu de la table Commandes s’affiche dans un nouveau classeur dans Excel.

Utilisation de CopyFromRecordset

Pour l’efficacité et les performances, CopyFromRecordset est la méthode préférée. Étant donné qu’Excel 97 prend en charge uniquement les jeux d’enregistrements DAO avec CopyFromRecordset, si vous tentez de passer un jeu d’enregistrements ADO à CopyFromRecordset avec Excel 97, vous recevez l’erreur suivante :

Erreur d’exécution 430 : la classe ne prend pas en charge Automation ou ne prend pas en charge l’interface attendue. Dans l’exemple de code, vous pouvez éviter cette erreur en vérifiant la version d’Excel afin de ne pas utiliser CopyFromRecordset pour la version 97.

Note Lorsque vous utilisez CopyFromRecordset, vous devez savoir que le jeu d’enregistrements ADO ou DAO que vous utilisez ne peut pas contenir de champs d’objet OLE ou de données de tableau telles que des jeux d’enregistrements hiérarchiques. Si vous incluez des champs de l’un ou l’autre type dans un jeu d’enregistrements, la méthode CopyFromRecordset échoue avec l’erreur suivante :

Erreur d’exécution -2147467259 : Échec de la méthode CopyFromRecordset de la plage d’objets.

Utilisation de GetRows

Si Excel 97 est détecté, utilisez la méthode GetRows du jeu d’enregistrements ADO pour copier le jeu d’enregistrements dans un tableau. Si vous affectez le tableau retourné par GetRows à une plage de cellules de la feuille de calcul, les données passent sur les colonnes au lieu de descendre dans les lignes. Par exemple, si le jeu d’enregistrements a deux champs et 10 lignes, le tableau apparaît sous la forme de deux lignes et de 10 colonnes. Par conséquent, vous devez transposer le tableau à l’aide de votre fonction TransposeDim() avant d’affecter le tableau à la plage de cellules. Lors de l’affectation d’un tableau à une plage de cellules, il existe certaines limitations à prendre en compte :

Les limitations suivantes s’appliquent lors de l’affectation d’un tableau à un objet Excel Range :

Le tableau ne peut pas contenir de champs d’objet OLE ou de données de tableau, telles que des jeux d’enregistrements hiérarchiques. Notez que l’exemple de code vérifie cette condition et affiche « Champ de tableau » afin que l’utilisateur soit informé que le champ ne peut pas être affiché dans Excel.
Le tableau ne peut pas contenir de champs Date qui ont une date antérieure à l’année 1900. (Consultez la section « Références » pour obtenir un lien d’article sur la Base de connaissances Microsoft.) Notez que l’exemple de code met en forme les champs date en tant que chaînes de variante pour éviter ce problème potentiel.

Notez l’utilisation de la fonction TransposeDim() pour transposer le tableau avant que le tableau ne soit copié dans la feuille de calcul Excel. Au lieu de créer votre propre fonction pour transposer le tableau, vous pouvez utiliser la fonction Transpose d’Excel en modifiant l’exemple de code pour affecter le tableau aux cellules comme indiqué ci-dessous :

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

Si vous décidez d’utiliser la méthode Transpose d’Excel au lieu de la fonction TransposeDim() pour transposer le tableau, vous devez connaître les limitations suivantes avec la méthode Transpose :

Le tableau ne peut pas contenir un élément supérieur à 255 caractères.
Le tableau ne peut pas contenir de valeurs Null.
Le nombre d’éléments dans le tableau ne peut pas dépasser 5 461.

Si les limitations ci-dessus ne sont pas prises en compte lorsque vous copiez un tableau dans une feuille de calcul Excel, l’une des erreurs d’exécution suivantes peut se produire :

Erreur d’exécution 13 : Incompatibilité de type

Erreur d’exécution 5 : procédure non valide

Erreur d’exécution de l’appel ou de l’argument 1004 : erreur définie par l’application ou définie par l’objet

References

Pour plus d’informations sur les limitations liées à la transmission de tableaux à différentes versions d’Excel, cliquez sur le numéro d’article suivant pour afficher l’article dans la Base de connaissances Microsoft :

177991 XL : Limitations du passage de tableaux à Excel à l’aide d’Automation

247412 INFO : méthodes de transfert de données vers Excel à partir de Visual Basic