Méthodes de transfert de données vers Excel à partir de Visual Basic
Résumé
Cet article décrit de nombreuses méthodes de transfert de données vers Microsoft Excel à partir de votre application Microsoft Visual Basic. Cet article présente également les avantages et les inconvénients de chaque méthode afin que vous puissiez choisir la solution qui vous convient le mieux.
Informations supplémentaires
L’approche la plus couramment utilisée pour transférer des données vers un classeur Excel est Automation. Automation vous offre la plus grande flexibilité pour spécifier l’emplacement de vos données dans le classeur, ainsi que la possibilité de mettre en forme le classeur et d’effectuer différents paramètres au moment de l’exécution. Avec Automation, vous pouvez utiliser plusieurs approches pour transférer vos données :
- Transférer des données cellule par cellule
- Transférer des données d’un tableau vers une plage de cellules
- Transférer des données d’un jeu d’enregistrements ADO vers une plage de cellules à l’aide de la méthode CopyFromRecordset
- Créer un QueryTable sur une feuille de calcul Excel qui contient le résultat d’une requête sur une source de données ODBC ou OLEDB
- Transférer des données dans le Presse-papiers, puis coller le contenu du Presse-papiers dans une feuille de calcul Excel
Il existe également des méthodes que vous pouvez utiliser pour transférer des données vers Excel qui ne nécessitent pas nécessairement Automation. Si vous exécutez un serveur d’applications côté serveur d’applications, il peut s’agir d’une bonne approche pour retirer la majeure partie du traitement des données à vos clients. Les méthodes suivantes peuvent être utilisées pour transférer vos données sans Automation :
- Transférer vos données vers un fichier texte délimité par des tabulations ou des virgules qu’Excel peut analyser ultérieurement dans des cellules d’une feuille de calcul
- Transférer vos données vers une feuille de calcul à l’aide d’ADO
- Transférer des données vers Excel à l’aide de Dynamic Data Exchange (DDE)
Les sections suivantes fournissent plus de détails sur chacune de ces solutions.
Note Lorsque vous utilisez Microsoft Office Excel 2007, vous pouvez utiliser le nouveau format de fichier Classeur Excel 2007 (*.xlsx) lors de l’enregistrement des classeurs. Pour ce faire, recherchez la ligne de code suivante dans les exemples de code suivants :
oBook.SaveAs "C:\Book1.xls"
Remplacez ce code par la ligne de code suivante :
oBook.SaveAs "C:\Book1.xlsx"
En outre, la base de données Northwind n’est pas incluse dans Office 2007 par défaut. Toutefois, vous pouvez télécharger la base de données Northwind à partir de Microsoft Office Online.
Utiliser Automation pour transférer des données cellule par cellule
Avec Automation, vous pouvez transférer des données vers une feuille de calcul une cellule à la fois :
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Add data to cells of the first worksheet in the new workbook
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Le transfert de données cellule par cellule peut être une approche parfaitement acceptable si la quantité de données est faible. Vous avez la possibilité de placer des données n’importe où dans le classeur et de mettre en forme les cellules de manière conditionnelle au moment de l’exécution. Toutefois, cette approche n’est pas recommandée si vous avez une grande quantité de données à transférer vers un classeur Excel. Chaque objet Range que vous obtenez au moment de l’exécution génère une demande d’interface afin que le transfert de données de cette manière puisse être lent. En outre, Microsoft Windows 95 et Windows 98 ont une limitation de 64 Ko sur les demandes d’interface. Si vous atteignez ou dépassez cette limite de 64 000 demandes d’interface, le serveur Automation (Excel) peut cesser de répondre ou vous pouvez recevoir des erreurs indiquant une mémoire insuffisante.
Une fois de plus, le transfert de données cellule par cellule n’est acceptable que pour de petites quantités de données. Si vous avez besoin de transférer des jeux de données volumineux vers Excel, vous devez envisager l’une des solutions présentées ultérieurement.
Pour plus d’exemples de code pour l’automatisation d’Excel, consultez Guide pratique pour automatiser Microsoft Excel à partir de Visual Basic.
Utiliser l’automatisation pour transférer un tableau de données vers une plage d’une feuille de calcul
Un tableau de données peut être transféré vers une plage de plusieurs cellules à la fois :
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows
Dim DataArray(1 To 100, 1 To 3) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = "ORD" & Format(r, "0000")
DataArray(r, 2) = Rnd() * 1000
DataArray(r, 3) = DataArray(r, 2) * 0.7
Next
'Add headers to the worksheet on row 1
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
'Transfer the array to the worksheet starting at cell A2
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Si vous transférez vos données à l’aide d’un tableau plutôt que cellule par cellule, vous pouvez réaliser un énorme gain de performances avec une grande quantité de données. Considérez cette ligne du code ci-dessus qui transfère les données vers 300 cellules dans la feuille de calcul :
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Cette ligne représente deux demandes d’interface (une pour l’objet Range renvoyé par la méthode Range et l’autre pour l’objet Range renvoyé par la méthode Resize). En revanche, le transfert de la cellule de données par cellule nécessite des demandes pour 300 interfaces vers des objets Range. Dans la mesure du possible, vous pouvez tirer parti du transfert de vos données en bloc et de la réduction du nombre de demandes d’interface que vous effectuez.
Utiliser l’automatisation pour transférer un jeu d’enregistrements ADO vers une plage de feuilles de calcul
Excel 2000 a introduit la méthode CopyFromRecordset qui vous permet de transférer un jeu d’enregistrements ADO (ou DAO) vers une plage d’une feuille de calcul. Le code suivant illustre comment automatiser Excel 2000, Excel 2002 ou Office Excel 2003 et transférer le contenu de la table Orders dans l’exemple de base de données Northwind à l’aide de la méthode CopyFromRecordset.
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'Close the connection
rs.Close
conn.Close
Note Si vous utilisez la version Office 2007 de la base de données Northwind, vous devez remplacer la ligne de code suivante dans l’exemple de code :
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Remplacez cette ligne de code par la ligne de code suivante :
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 fournit également une méthode CopyFromRecordset, mais vous pouvez l’utiliser uniquement avec un jeu d’enregistrements DAO. CopyFromRecordset avec Excel 97 ne prend pas en charge ADO.
Pour plus d’informations sur l’utilisation d’ADO et de la méthode CopyFromRecordset, voir Guide pratique pour transférer des données d’un jeu d’enregistrements ADO vers Excel avec automation.
Utiliser l’automatisation pour créer un QueryTable sur une feuille de calcul
Un objet QueryTable représente une table générée à partir de données retournées à partir d’une source de données externe. Lors de l’automatisation de Microsoft Excel, vous pouvez créer un QueryTable en fournissant simplement un chaîne de connexion à une source de données OLEDB ou ODBC avec une chaîne SQL. Excel assume la responsabilité de générer le jeu d’enregistrements et de l’insérer dans la feuille de calcul à l’emplacement que vous spécifiez. L’utilisation de QueryTables offre plusieurs avantages par rapport à la méthode CopyFromRecordset :
- Excel gère la création du jeu d’enregistrements et son placement dans la feuille de calcul.
- La requête peut être enregistrée avec le QueryTable afin qu’elle puisse être actualisée ultérieurement pour obtenir un jeu d’enregistrements mis à jour.
- Lorsqu’un nouveau QueryTable est ajouté à votre feuille de calcul, vous pouvez spécifier que les données déjà présentes dans les cellules de la feuille de calcul doivent être décalées pour prendre en charge les nouvelles données (voir la propriété RefreshStyle pour plus d’informations).
Le code suivant montre comment automatiser Excel 2000, Excel 2002 ou Office Excel 2003 pour créer un queryTable dans une feuille de calcul Excel à l’aide des données de l’exemple de base de données Northwind :
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'Create the QueryTable
Dim sNWind As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim oQryTable As Object
Set oQryTable = oSheet.QueryTables.Add( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";", oSheet.Range("A1"), "Select * from Orders") oQryTable.RefreshStyle = xlInsertEntireRows
oQryTable.Refresh False
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Utiliser le Presse-papiers
Le Presse-papiers Windows peut également être utilisé comme mécanisme de transfert de données vers une feuille de calcul. Pour coller des données dans plusieurs cellules d’une feuille de calcul, vous pouvez copier une chaîne où les colonnes sont délimitées par des caractères de tabulation et les lignes sont délimitées par des retours chariot. Le code suivant illustre comment Visual Basic peut utiliser son objet Presse-papiers pour transférer des données vers Excel :
'Copy a string to the clipboard
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _ & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Paste the data
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
Créer un fichier texte délimité qu’Excel peut analyser en lignes et colonnes
Excel peut ouvrir des fichiers délimités par des tabulations ou des virgules et analyser correctement les données dans des cellules. Vous pouvez tirer parti de cette fonctionnalité lorsque vous souhaitez transférer une grande quantité de données vers une feuille de calcul tout en utilisant peu, voire pas, Automation. Il peut s’agir d’une bonne approche pour une application client-serveur, car le fichier texte peut être généré côté serveur. Vous pouvez ensuite ouvrir le fichier texte sur le client, en utilisant Automation lorsque cela est approprié.
Le code suivant illustre la façon dont vous pouvez créer un fichier texte délimité par des virgules à partir d’un recordset ADO :
'Create a Recordset from all the records in the Orders table
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'Close the connection
rs.Close
conn.Close
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _ Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Remarque Si vous utilisez la version Office 2007 de la base de données Northwind, vous devez remplacer la ligne de code suivante dans l’exemple de code :
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Remplacez cette ligne de code par la ligne de code suivante :
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Si votre fichier texte a une extension .CSV, Excel ouvre le fichier sans afficher l’Assistant Importation de texte et suppose automatiquement que le fichier est délimité par des virgules. De même, si votre fichier a une extension .TXT, Excel analyse automatiquement le fichier à l’aide de délimiteurs de tabulation.
Dans l’exemple de code précédent, Excel a été lancé à l’aide de l’instruction Shell et le nom du fichier a été utilisé comme argument de ligne de commande. Aucune automatisation n’a été utilisée dans l’exemple précédent. Toutefois, si vous le souhaitez, vous pouvez utiliser une quantité minimale d’Automation pour ouvrir le fichier texte et l’enregistrer au format de classeur Excel :
'Create a new instance of Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'Open the text file
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'Save as Excel workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit
Transférer des données vers une feuille de calcul à l’aide d’ADO
À l’aide du fournisseur OLE DB Microsoft Jet, vous pouvez ajouter des enregistrements à un tableau dans un classeur Excel existant. Un « tableau » dans Excel est simplement une plage avec un nom défini. La première ligne de la plage doit contenir les en-têtes (ou noms de champs) et toutes les lignes suivantes contiennent les enregistrements. Les étapes suivantes illustrent comment créer un classeur avec une table vide nommée MyTable.
Excel 97, Excel 2000 et Excel 2003
Démarrez un nouveau classeur dans Excel.
Ajoutez les en-têtes suivants aux cellules A1 :B1 de sheet1 :
A1 : FirstName B1 : LastName
Mettez en forme la cellule B1 comme alignée à droite.
Sélectionnez A1 :B1.
Dans le menu Insérer, choisissez Noms, puis sélectionnez Définir. Entrez le nom MyTable et cliquez sur OK.
Enregistrez le nouveau classeur en tant que C:\Book1.xls et quittez Excel.
Pour ajouter des enregistrements à MyTable à l’aide d’ADO, vous pouvez utiliser du code similaire à ce qui suit :
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
Excel 2007
Dans Excel 2007, démarrez un nouveau classeur.
Ajoutez les en-têtes suivants aux cellules A1 :B1 de sheet1 :
A1 : FirstName B1 : LastName
Mettez en forme la cellule B1 comme alignée à droite.
Sélectionnez A1 :B1.
Dans le ruban, cliquez sur l’onglet Formules , puis sur Définir un nom. Tapez le nom MyTable, puis cliquez sur OK.
Enregistrez le nouveau classeur en tant que C:\Book1.xlsx, puis quittez Excel.
Pour ajouter des enregistrements à la table MyTable à l’aide d’ADO, utilisez un code semblable à l’exemple de code suivant.
'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close
Lorsque vous ajoutez des enregistrements à la table de cette manière, la mise en forme du classeur est conservée. Dans l’exemple précédent, les nouveaux champs ajoutés à la colonne B sont mis en forme avec un alignement à droite. Chaque enregistrement ajouté à une ligne emprunte le format de la ligne située au-dessus.
Notez que lorsqu’un enregistrement est ajouté à une ou plusieurs cellules de la feuille de calcul, il remplace toutes les données précédemment présentes dans ces cellules ; en d’autres termes, les lignes de la feuille de calcul ne sont pas « poussées » quand de nouveaux enregistrements sont ajoutés. Vous devez garder cela à l’esprit lors de la conception de la disposition des données sur vos feuilles de calcul.
Remarque
La méthode de mise à jour des données dans une feuille de calcul Excel à l’aide d’ADO ou de DAO ne fonctionne pas dans l’environnement Visual Basic pour Application dans Access après avoir installé Office 2003 Service Pack 2 (SP2) ou après avoir installé la mise à jour pour Access 2002 incluse dans l’article 904018 de la Base de connaissances Microsoft. La méthode fonctionne bien dans l’environnement Visual Basic pour Application à partir d’autres applications Office, telles que Word, Excel et Outlook.
Pour plus d’informations, consultez l’article suivant :
Pour plus d’informations sur l’utilisation d’ADO pour accéder à un classeur Excel, consultez Comment interroger et mettre à jour des données Excel à l’aide d’ADO à partir d’ASP.
Utiliser DDE pour transférer des données vers Excel
DDE est une alternative à Automation comme moyen de communiquer avec Excel et de transférer des données. Toutefois, avec l’avènement d’Automation et de COM, DDE n’est plus la méthode préférée pour communiquer avec d’autres applications et ne doit être utilisé que lorsqu’aucune autre solution n’est disponible.
Pour transférer des données vers Excel à l’aide de DDE, vous pouvez utiliser la méthode LinkPoke pour envoyer des données à une plage spécifique de cellules, ou vous utilisez la méthode LinkExecute pour envoyer des commandes qu’Excel exécutera.
L’exemple de code suivant montre comment établir une conversation DDE avec Excel afin que vous puissiez envoyer des données à des cellules d’une feuille de calcul et exécuter des commandes. À l’aide de cet exemple, pour qu’une conversation DDE soit correctement établie dans LinkTopic Excel |MyBook.xls, un classeur portant le nom MyBook.xls doit déjà être ouvert dans un instance en cours d’exécution d’Excel.
Remarque
Lorsque vous utilisez Excel 2007, vous pouvez utiliser le nouveau format de fichier .xlsx pour enregistrer les classeurs. Veillez à mettre à jour le nom de fichier dans l’exemple de code suivant. Dans cet exemple, Text1 représente un contrôle Zone de texte sur un formulaire Visual Basic :
'Initiate a DDE communication with Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1
'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke
'Execute commands to select cell A1 (same as R1C1) and change the font format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
Text1.LinkMode = 0
Lorsque vous utilisez LinkPoke avec Excel, vous spécifiez la plage en notation de colonne de ligne (R1C1) pour l’objet LinkItem. Si vous entrez des données dans plusieurs cellules, vous pouvez utiliser une chaîne dans laquelle les colonnes sont délimitées par des tabulations et les lignes sont délimitées par des retours chariot.
Lorsque vous utilisez LinkExecute pour demander à Excel d’exécuter une commande, vous devez donner à Excel la commande dans la syntaxe xlM (Excel Macro Language). La documentation XLM n’est pas incluse dans Excel versions 97 et ultérieures.
DDE n’est pas une solution recommandée pour communiquer avec Excel. L’automatisation offre la plus grande flexibilité et vous donne plus d’accès aux nouvelles fonctionnalités qu’Excel a à offrir.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour