DetailPage-MSS-KB

Base de connaissances

Numéro d'article: 246335 - Dernière mise à jour: mercredi 26 septembre 2007 - Version: 5.1

Sommaire

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 :
http://office.microsoft.com/fr-fr/templates/TC012289971036.aspx (http://office.microsoft.com/fr-fr/templates/TC012289971036.aspx)

É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 = 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. 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  (http://support.microsoft.com/kb/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  (http://support.microsoft.com/kb/146406/ ) XL : Comment faire pour récupérer une table à partir d'Access dans Excel à l'aide de DAO
215965  (http://support.microsoft.com/kb/215965/ ) XL2000 : Affichage de 12:00:00 AM pour les dates antérieures à 1900
243394  (http://support.microsoft.com/kb/243394/ ) Comment faire pour utiliser MFC pour copier un jeu d'enregistrements DAO dans Excel avec l'Automation
247412  (http://support.microsoft.com/kb/247412/ ) INFO : Méthodes de transferts de données vers Excel à partir de Visual Basic

Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • 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
Mots-clés : 
kbhowto kbautomation kbexpertiseinter KB246335
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.
Partager
Options de support supplémentaire
Forums du support Microsoft Community
Nous contacter directement
Trouver un partenaire Microsoft Certified Partner
Microsoft Store