Vous êtes actuellement hors ligne, en attente de reconnexion à Internet.

Comment faire pour transférer des données d'un jeu d'enregistrements ADO vers Excel avec l'Automation

Le support d'Office 2003 a pris fin

Microsoft a mis fin au support d'Office 2003 le 8 avril 2014. Cette modification a affecté vos mises à jour logicielles et options de sécurité. Découvrez les implications de ce changement à votre niveau et la marche à suivre pour rester protégé.

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 automatisez. Excel 97, Excel 2000 et Excel 2002 disposent d'une méthode CopyFromRecordset que vous pouvez utiliser pour transférer un jeu d'enregistrements vers une plage. CopyFromRecordset dans Excel 2000 et Excel 2002 peut être utilisé pour copier un jeu d'enregistrements DAO ou ADO. Cependant, CopyFromRecordset dans Excel 97 prend uniquement en charge 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 aborde deux approches. L'exemple de code présenté illustre la procédure à suivre pour transférer un jeu d'enregistrements ADO vers Excel 97, Excel 2000, Excel 2002, Excel 2003 ou Excel 2007.
Plus d'informations
L'exemple de code fourni ci-dessous montre comment vous pouvez copier un jeu d'enregistrements ADO dans une feuille de calcul Microsoft Excel à l'aide de l'automation à partir de Microsoft Visual Basic. Le code vérifie d'abord la version d'Excel. Si Excel 2000 ou Excel 2002 est détecté, la méthode CopyFromRecordset est utilisée car elle est efficace et nécessite moins de code. Cependant, si Excel 97 ou une version antérieure est détectée, le jeu d'enregistrements est d'abord copié dans un tableau à l'aide de la méthode GetRows de l'objet du jeu d'enregistrements ADO. Le tableau est ensuite transposé de sorte que les enregistrements se trouvent dans la première dimension (dans les lignes) et que les champs se trouvent dans la deuxième dimension (dans les colonnes). Le tableau est ensuite copié dans une feuille de calcul Excel en l'attribuant à une plage de cellules. (Le tableau est copié en une seule étape plutôt qu'en parcourant chaque cellule de la feuille de calcul.)

L'exemple de code utilise l'exemple de base de données Les Comptoirs qui est fournie avec Microsoft Office. Si vous avez sélectionné le dossier par défaut lors de l'installation de Microsoft Office, la base de données se trouve dans :

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

Si la base de données Les Comptoirs se trouve dans un autre dossier sur votre ordinateur, vous devez modifier le chemin d'accès à la base de données dans le code fourni ci-dessous.

Si la base de données Les Comptoirs n'est pas installée dans votre système, vous pouvez utiliser l'option Ajout/Suppression du programme d'installation de Microsoft Office pour installer les exemples de bases de données.

Remarque La base de données Les Comptoirs n'est pas installée lors de l'installation de Microsoft Office 2007. Pour obtenir Les Comptoirs 2007, reportez-vous au site Web de Microsoft à l'adresse suivante :

Étapes pour créer l'exemple

  1. Démarrez Visual Basic et créez un nouveau projet EXE standard. Form1 est créé par défaut.
  2. Ajoutez un CommandButton à Form1.
  3. Cliquez sur Références dans le menu Projet. Ajoutez une référence à la Microsoft ActiveX Data Objects 2.1 Library.
  4. Copiez le code suivant dans la section du 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 = NothingEnd SubFunction 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 = tempArrayEnd Function
  5. Appuyez sur la touche F5 pour exécuter le projet. Form1 s'affiche.
  6. Cliquez sur le CommandButton dans Form1 et notez que le contenu de la table Commandes s'affiche dans un nouveau classeur dans Excel.
Utilisation de CopyFromRecordset

De par son efficacité et ses performances, CopyFromRecordset est la méthode préférée. Comme Excel 97 prend uniquement en charge les jeux d'enregistrements DAO avec CopyFromRecordset, si vous essayez de passer un jeu d'enregistrements ADO dans CopyFromRecordset avec Excel 97, le message d'erreur suivant s'affiche :
Erreur d'exécution 430 :
La classe ne gère pas Automation ou l'interface attendue.
Dans l'exemple de code, vous pouvez éviter cette erreur en vérifiant les versions d'Excel afin de ne pas utiliser CopyFromRecordset pour la version 97.

Remarque Lorsque vous utilisez CopyFromRecordset, vous devez garder à l'esprit que le jeu d'enregistrements ADO ou DAO que vous utilisez ne peut pas contenir des champs objet OLE ou des données de tableau comme les jeux d'enregistrements hiérarchiques. Si vous ajoutez des champs de l'un de ces types dans un jeu d'enregistrements, la méthode CopyFromRecordset échoue avec l'erreur suivante :
Erreur d'exécution -2147467259 :
La méthode CopyFromRecordset de l'objet Range a échoué.
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 attribuez le tableau renvoyé par GetRows à une plage de cellules de la feuille de calcul, les données sont réparties dans les colonnes au lieu des lignes. Par exemple, si le jeu d'enregistrements dispose de deux champs et de 10 lignes, le tableau s'affiche sur deux lignes et 10 colonnes. C'est pourquoi vous devez transposer le tableau à l'aide de votre fonction TransposeDim() avant de l'attribuer à une plage de cellules. Lorsque vous attribuez un tableau à une plage de cellules, vous devez être conscient de certaines limitations :

Les limitations suivantes s'appliquent lorsque vous attribuez un tableau à un objet Range d'Excel :
  • Le tableau ne peut pas contenir de champs objet OLE ou de données de tableau comme les jeux d'enregistrements hiérarchiques. Notez que l'exemple de code vérifie cette condition et affiche "Array Field" afin que l'utilisateur soit conscient que le champ ne peut pas être affiché dans Excel.

  • Le tableau ne peut pas contenir les champs Date qui ont une date antérieure à l'année 1900. (Consultez la section "Références" d'un article de la Base de connaissances Microsoft.) Notez que l'exemple de code met en forme les champs Date comme chaînes variantes pour éviter cet éventuel problème.
Notez l'utilisation de la fonction TransposeDim() pour transposer le tableau avant qu'il 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 afin d'attribuer le tableau aux cellules comme présenté 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 à la place de la fonction TransposeDim() pour transposer le tableau, gardez à l'esprit les limitations suivantes de la méthode Transpose :
  • Le tableau ne peut pas contenir un élément qui dispose de plus de 255 caractères.
  • Le tableau ne peut pas contenir des valeurs Null.
  • Le nombre d'éléments dans le tableau ne peut pas dépasser 5461.
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 s'afficher :
Erreur d'exécution 13 : Type incompatible
Erreur d'exécution 5 : Argument ou appel de procédure incorrect
Erreur d'exécution 1004 : Erreur définie par l'application ou par l'objet
Références
Pour plus d'informations sur les limitations de transmission de tableaux dans différentes versions d'Excel, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
177991 EXCEL : Limitations de la transmission de tableaux dans Excel à l'aide de l'Automation
Pour plus d'informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft.
146406 XL : Comment faire pour récupérer une table à partir d'Access dans Excel à l'aide de DAO
215965 XL2000 : Affichage de 12:00:00 AM pour les dates antérieures à 1900
243394 Comment faire pour utiliser MFC pour copier un jeu d'enregistrements DAO dans Excel avec l'Automation
247412 INFO : Méthodes de transferts de données vers Excel à partir de Visual Basic
Transpose Mismatch XL2007
Propriétés

ID d'article : 246335 - Dernière mise à jour : 09/26/2007 10:12:00 - Révision : 5.1

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000 Standard, Microsoft Excel 97 Standard, Microsoft Visual Basic 5.0 Édition professionnelle, Microsoft Visual Basic 6.0 Édition professionnelle, Microsoft Visual Basic 5.0 Édition Entreprise, Microsoft Visual Basic 6.0 Édition Entreprise, Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1, Microsoft ActiveX Data Objects 2.5

  • kbhowto kbautomation kbexpertiseinter KB246335
Commentaires
/html>/body>