Utilisation d'ADO avec des données Excel à partir de Visual Basic ou de VBA

Numéro d'article: 257819 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article décrit l'utilisation des objets ADO (ActiveX Data Objects) avec une feuille de calcul Microsoft Excel comme source de données. Il attire également l'attention sur les problèmes de syntaxe et les limitations propres à Excel. Les technologies OLAP ou de tableaux croisés dynamiques, de même que d'autres utilisations spécialisées des données Excel, ne sont pas abordées dans cet article.

Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
303814
(http://support.microsoft.com/kb/303814/FR/ )
Comment faire pour utiliser ADOX avec des données Excel à partir de Visual Basic ou de VBA
Retour au début | Envoyer des commentaires

Plus d'informations

INTRODUCTION

Les lignes et les colonnes d'une feuille de calcul Microsoft Excel ressemblent beaucoup à celles d'une table de base de données. Tant que les utilisateurs gardent à l'esprit que Microsoft Excel n'est pas un système de gestion de bases de données relationnelles et que ce simple fait impose certaines limitations, ils peuvent mettre à profit les fonctionnalités et les outils d'Excel pour stocker et analyser des données.

Les objets Microsoft ADO permettent de travailler avec un classeur Excel comme avec une base de données. Cet article décrit cette utilisation de Microsoft Excel dans les sections suivantes : REMARQUE les tests utilisés dans cet article ont été effectués avec Microsoft Data Access Components (MDAC) 2.5 sur Microsoft Windows 2000 avec le Service Pack 3 Visual Basic 6.0 et Excel 2000. Les utilisateurs observeront peut-être d'autres comportements avec des versions différentes de MDAC, de Microsoft Windows, de Visual Basic ou de Microsoft Excel. Cet article ne présente pas nécessairement ces différences.

Connexion à Excel à l'aide d'ADO

ADO permet de se connecter à un fichier de données Excel à l'aide de l'un des deux fournisseurs OLE DB compris dans MDAC :
  • le fournisseur OLE DB Microsoft Jet - ou -

  • le fournisseur Microsoft OLE DB pour pilotes OBDC

Utilisation du fournisseur OLE DB Microsoft Jet

Le fournisseur Jet requiert uniquement deux informations pour se connecter à une source de données Excel : le chemin, y compris le nom de fichier, et la version du fichier Excel.

Fournisseur Jet utilisant une chaîne de connexion
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
	.Open
End With
Version du fournisseur : Vous devez utiliser la version 4.0 du fournisseur Jet ; la version 3.51 ne prend pas en charge les pilotes ISAM Jet. Si vous spécifiez la version 3.51 du fournisseur Jet, le message d'erreur suivant s'affiche lors de l'exécution :
Pilote ISAM introuvable.
Version de Microsoft Excel : Spécifiez Excel 5.0 pour un classeur Excel 95 (version 7.0 d'Excel) et Excel 8.0 pour un classeur Excel 97, Excel 2000 ou Excel 2002 (XP) (versions 8.0, 9.0 et 10.0 d'Excel).

Fournisseur Jet utilisant la boîte de dialogue Propriétés des liaisons de données

Si vous utilisez le contrôle de données ou l'environnement de données ADO dans votre application, la boîte de dialogue Propriétés des liaisons de données est affichée pour recueillir les paramètres de connexion nécessaires.
  1. Sous l'onglet Fournisseur, sélectionnez le fournisseur Jet 4.0 ; la version 3.51 du fournisseur Jet ne prend pas en charge les pilotes ISAM Jet. Si vous spécifiez la version 3.51 du fournisseur Jet, le message d'erreur suivant s'affiche lors de l'exécution :
    Pilote ISAM introuvable.
  2. Sous l'onglet Connexion, naviguez vers votre fichier de classeur. Ignorez les entrées « Nom d'utilisateur » et « Mot de passe », car elles ne s'appliquent pas à une connexion Excel. (Vous ne pouvez pas ouvrir un fichier Excel protégé par mot de passe en tant que source de données. Vous trouverez des informations supplémentaires à ce sujet plus bas dans cet article.)
  3. Sous l'onglet Tous, sélectionnez Extended Properties dans la liste, puis cliquez sur Modifier la valeur. Entrez Excel 8.0;, en le séparant des entrées existantes par un point-virgule (;). Si vous n'effectuez pas cette étape, un message d'erreur s'affiche lorsque vous testez votre connexion, car le fournisseur Jet s'attend à une base de données Microsoft Access sauf spécification contraire.
  4. Retournez à l'onglet Connexion et cliquez sur Tester la connexion. Une boîte de message s'affiche indiquant que le processus a réussi.
Autres paramètres de connexion du fournisseur Jet

En-têtes de colonnes : Par défaut, le programme suppose que la première ligne de votre source de données Excel contient des en-têtes de colonnes pouvant être utilisés comme noms de champs. Si ce n'est pas le cas, vous devez désactiver ce paramètre ; sinon, votre première ligne de données « disparaîtra » pour être utilisée comme noms de champs. Ceci est accompli en ajoutant le paramètre facultatif HDR= aux propriétés Extended Properties de la chaîne de connexion. Le paramètre par défaut, qu'il n'est pas nécessaire de spécifier, est HDR=Yes. Si vous n'avez pas d'en-têtes de colonnes, vous devez spécifier HDR=No ; le fournisseur attribue à vos champs les noms F1, F2, etc. Étant donné que la chaîne Extended Properties contient désormais plusieurs valeurs, elle doit être entourée de guillemets, avec en plus une seconde paire de guillemets indiquant à Visual Basic que la première paire de guillemets doit être traitée comme une valeur littérale, comme dans l'exemple ci-dessous. (Des espaces supplémentaires ont été ajoutés pour des raisons de clarté visuelle.) 
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "

Utilisation du fournisseur Microsoft OLE DB pour pilotes ODBC

Le fournisseur pour pilotes ODBC (auquel cet article se réfère en tant que « fournisseur ODBC » pour des raisons de concision) ne requiert, lui aussi, que deux (2) informations pour se connecter à une source de données Excel : le nom du pilote et le chemin et nom de fichier du classeur.

IMPORTANT : par défaut, la connexion ODBC à Excel est en lecture seule. Votre paramètre de propriété LockType du jeu d'enregistrements ADO ne remplace pas ce paramètre au niveau de la connexion. Vous devez attribuer à ReadOnly la valeur False dans votre chaîne de connexion ou dans votre configuration DSN si vous souhaitez modifier vos données. Sinon, le message d'erreur suivant s'affiche :
L'opération doit utiliser une requête qui peut être mise à jour.
Fournisseur ODBC utilisant une chaîne de connexion sans DSN
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
	.Open
End With
Fournisseur ODBC utilisant une chaîne de connexion avec un DSN
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "DSN=MyExcelDSN;"
	.Open
End With
Fournisseur ODBC utilisant la boîte de dialogue Propriétés des liaisons de données

Si vous utilisez le contrôle de données ou l'environnement de données ADO dans votre application, la boîte de dialogue Propriétés des liaisons de données est affichée pour recueillir les paramètres de connexion nécessaires.
  1. Sous l'onglet Fournisseur, sélectionnez Fournisseur Microsoft OLE DB pour pilotes ODBC.
  2. Sous l'onglet Connexion, sélectionnez le DSN existant à utiliser ou choisissez Utiliser la chaîne de connexion. Ceci affiche la boîte de dialogue de configuration DSN standard pour recueillir les paramètres de connexion nécessaires. N'oubliez pas de désactiver le paramètre par défaut de lecture seule le cas échéant, comme indiqué plus haut.
  3. Retournez à l'onglet Connexion et cliquez sur Tester la connexion. Une boîte de message s'affiche indiquant que le processus a réussi.
Autres paramètres de connexion du fournisseur ODBC

En-têtes de colonnes : Par défaut, le programme suppose que la première ligne de votre source de données Excel contient des en-têtes de colonnes pouvant être utilisés comme noms de champs. Si ce n'est pas le cas, vous devez désactiver ce paramètre ; sinon, votre première ligne de données « disparaîtra » pour être utilisée comme noms de champs. Ceci est accompli en ajoutant le paramètre facultatif FirstRowHasNames= à la chaîne de connexion. Le paramètre par défaut, qu'il n'est pas nécessaire de spécifier, est FirstRowHasNames=1, où 1 = True. Si vous n'avez pas d'en-têtes de colonnes, vous devez spécifier FirstRowHasNames=0, où 0 = False ; le pilote attribue à vos champs les noms F1, F2, etc. Cette option n'est pas disponible dans la boîte de dialogue de configuration DSN.

Toutefois, en raison d'un bogue du pilote ODBC, le fait de spécifier le paramètre FirstRowHasNames n'a aucun effet à l'heure actuelle. En d'autres termes, le pilote ODBC Excel (MDAC 2.1 et version ultérieure) traite toujours la première ligne de la source de données spécifiée en tant que noms de champs. Pour plus d'informations sur ce bogue, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
288343
(http://support.microsoft.com/kb/288343/FR/ )
BOGUE : Le pilote ODBC Excel ignore les paramètres FirstRowHasNames et Header
Lignes à balayer : Contrairement à une base de données relationnelle, Excel ne donne pas à ADO des informations de schéma détaillées sur les données qu'il contient. Par conséquent, le pilote doit analyser (balayer) au moins quelques lignes des données existantes pour tenter de déterminer le type de données de chaque colonne. La valeur par défaut du paramètre "Lignes à balayer" est de huit (8) lignes. Vous pouvez spécifier une valeur entière de une (1) à seize (16) lignes ou spécifier zéro (0) pour analyser toutes les lignes existantes. Ceci est accompli en ajoutant le paramètre facultatif MaxScanRows= à la chaîne de connexion ou en modifiant le paramètre Lignes à balayer de la boîte de dialogue de configuration DSN.

Toutefois, en raison d'un bogue du pilote ODBC, le fait de spécifier le paramètre Lignes à balayer (MaxScanRows) n'a aucun effet à l'heure actuelle. En d'autres termes, le pilote ODBC Excel (MDAC 2.1 et version ultérieure) analyse toujours les 8 premières lignes de la source de données spécifiée pour déterminer le type de données de chaque colonne.

Pour plus d'informations sur le bogue du paramètre Lignes à balayer, y compris une solution de contournement simple, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
189897
(http://support.microsoft.com/kb/189897/FR/ )
XL97 : Les données sont tronquées en 255 caractères avec le pilote ODBC Excel
Autres paramètres : Si vous construisez votre chaîne de connexion à l'aide de la boîte de dialogue Propriétés des liaisons de données, vous observerez peut-être que certains autres paramètres non indispensables des propriétés Extended Properties ont été ajoutés à la chaîne de connexion, notamment : 
... DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
Message d'erreur « Ordre de tri » dans Visual Basic Editor

Dans l'environnement de création Visual Basic avec certaines versions de MDAC, le message d'erreur suivant peut s'afficher la première fois que votre programme se connecte à une source de données Excel au moment de la conception :
L'ordre de tri sélectionné n'est pas géré par le système d'exploitation.
Ce message apparaît uniquement dans l'environnement de développement et n'est pas affiché dans la version compilée du programme. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
246167
(http://support.microsoft.com/kb/246167/FR/ )
PROBLÈME : Erreur de l'ordre de tri lors de l'ouverture d'un jeu d'enregistrements ADODB pour la première fois sur un fichier XLS Excel

Considérations pertinentes pour les deux fournisseurs OLE DB

Avertissement concernant les types de données mixtes

Comme mentionné plus haut, ADO doit tenter de déterminer le type de données pour chaque colonne de votre plage ou feuille de calcul Excel. (Les paramètres de mise en forme des cellules Excel n'ont aucune incidence sur ce processus.) Un problème grave peut se produire si une colonne contient à la fois des valeurs numériques et du texte. Les fournisseurs Jet et ODBC renvoient tous deux les données du type majoritaire dans la colonne, mais renvoient des valeurs NULL (vides) pour le type de données minoritaire. Si les deux types sont représentés en proportions égales dans la colonne, le fournisseur choisit le type numérique.

Par exemple :
  • Dans vos huit (8) lignes balayées, si la colonne contient cinq (5) valeurs numériques et trois (3) valeurs de texte, le fournisseur renvoie cinq (5) nombres et trois (3) valeurs NULL.
  • Dans vos huit (8) lignes balayées, si la colonne contient trois (3) valeurs numériques et cinq (5) valeurs de texte, le fournisseur renvoie trois (3) valeurs NULL et cinq (5) valeurs de texte.
  • Dans vos huit (8) lignes balayées, si la colonne contient quatre (4) valeurs numériques et quatre (4) valeurs de texte, le fournisseur renvoie quatre (4) nombres et quatre (4) valeurs NULL.
Par conséquent, si votre colonne contient des valeurs mixtes, votre seule option est de stocker les valeurs numériques sous forme de texte, puis de les reconvertir en nombres dans l'application cliente le cas échéant à l'aide de la fonction VAL de Visual Basic ou d'une fonction équivalente.

Pour contourner ce problème dans le cas de données en lecture seule, activez l'option Mode d'importation à l'aide du paramètre "IMEX=1" dans la section Extended Properties de la chaîne de connexion. Ceci applique le paramètre de Registre ImportMixedTypes=Text. Toutefois, notez que les mises à jour peuvent donner des résultats inattendus dans ce mode. Pour plus d'informations concernant ce paramétrage, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
194124
(http://support.microsoft.com/kb/194124/FR/ )
PROBLÈME : Valeurs Excel retournées comme NULL en utilisant DAO OpenRecordset
Impossible d'ouvrir un classeur protégé par mot de passe

Si le classeur Excel est protégé par un mot de passe, vous ne pouvez pas l'ouvrir pour accéder aux données, même en entrant le mot de passe correct avec vos paramètres de connexion, à moins que le fichier ne soit déjà ouvert dans l'application Microsoft Excel. Si vous tentez de l'ouvrir, le message d'erreur suivant s'affiche :
Impossible de déchiffrer le fichier.
Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
211378
(http://support.microsoft.com/kb/211378/FR/ )
XL2000 : Erreur « Impossible de décoder le fichier » avec un fichier protégé par un mot de passe

Récupération et modification des données Excel à l'aide d'ADO

Cette section aborde deux aspects du travail avec des données Excel :
  • Sélection des données - et -

  • Modification des données

Sélection des données

Vous disposez de plusieurs méthodes pour sélectionner des données. Vous pouvez :

  • sélectionner les données Excel à l'aide de code ;
  • sélectionner les données Excel à l'aide du contrôle de données ADO ;
  • sélectionner les données Excel à l'aide des commandes de l'environnement de données.

Sélection de données Excel à l'aide de code

Vos données Excel peuvent être contenues dans l'un des éléments suivants de votre classeur :

  • une feuille de calcul entière ;
  • une plage de cellules nommée sur une feuille de calcul ;
  • une plage de cellules non nommée sur une feuille de calcul.
Spécification d'une feuille de calcul

Pour spécifier une feuille de calcul comme source d'enregistrements, utilisez le nom de la feuille de calcul suivi du symbole $ et entouré par des crochets. Par exemple : 
	strQuery = "SELECT * FROM [Sheet1 $]"
Vous pouvez également délimiter le nom de la feuille de calcul à l'aide de l'accent grave (`) situé sur la touche du 7 de la rangée des chiffres sur le clavier français. Par exemple : 
	strQuery = "SELECT * FROM `Sheet1$`"
Microsoft préfère les crochets, qui représentent la convention en vigueur pour les noms d'objets de bases de données problématiques.

Si vous oubliez d'ajouter le symbole $ et les crochets, ou juste le symbole $, le message d'erreur suivant s'affiche :
... Le moteur de base de données Microsoft Jet n'a pas pu trouver l'objet spécifié
Si vous utilisez le symbole $ sans crochets, le message d'erreur suivant s'affiche :
Erreur de syntaxe dans la clause FROM.
Si vous utilisez des apostrophes plutôt que l'accent grave, le message d'erreur suivant s'affiche :
Erreur de syntaxe dans la requête. La clause de la requête est incomplète.
Spécification d'une plage nommée

Pour spécifier une plage de cellules nommée comme source d'enregistrements, utilisez simplement le nom défini de la plage. Par exemple : 
	strQuery = "SELECT * FROM MyRange"
Spécification d'une plage non nommée

Pour spécifier une plage de cellules non nommée comme source d'enregistrements, ajoutez la notation de ligne/colonne Excel standard à la fin du nom de la feuille entre crochets. Par exemple : 
	strQuery = "SELECT * FROM [Sheet1$A1:B10]"
Avertissement concernant la spécification des feuilles de calcul : Le fournisseur suppose que votre table de données débute à la première cellule non vide en haut à gauche de la feuille de calcul spécifiée. En d'autres termes, votre table de données peut débuter à la ligne 3, colonne C. Toutefois, vous ne pouvez pas tapez un titre de feuille de calcul en haut et à gauche des données, par exemple dans la cellule A1.

Avertissement concernant la spécification des plages : Lorsque vous spécifiez une feuille de calcul comme source d'enregistrements, le fournisseur ajoute les nouveaux enregistrements au-dessous des enregistrements existants de la feuille de calcul, selon l'espace disponible. Lorsque vous spécifiez une plage (nommée ou non), le fournisseur Jet ajoute également les nouveaux enregistrements au-dessous des enregistrements existants de la plage, selon l'espace disponible. Toutefois, si vous effectuez une nouvelle requête sur la plage d'origine, le jeu d'enregistrements obtenu n'inclut pas les enregistrements que vous venez d'ajouter en dehors de la plage.

Dans les versions de MDAC antérieures à 2.5, lorsque vous spécifiez une plage nommée, vous ne pouvez pas ajouter de nouveaux enregistrements au-delà des limites définies pour la plage ; sinon, le message d'erreur suivant s'affiche :
Impossible d'agrandir la plage nommée.

Sélection de données Excel avec le contrôle de données ADO

Après avoir spécifié les paramètres de connexion de votre source de données Excel sous l'onglet Général de la boîte de dialogue Propriétés ADODC, cliquez sur l'onglet Recordsource. Si vous choisissez adCmdText comme type de commande, vous pouvez entrer une requête SELECT dans la boîte de dialogue Texte de commande à l'aide de la syntaxe décrite plus haut. Si vous choisissez adCmdTable comme type de commande et que vous utilisez le fournisseur Jet, la liste déroulante affiche à la fois les plages nommées et les noms des feuilles de calcul disponibles dans le classeur sélectionné, en commençant par les plages nommées.

Cette boîte de dialogue ajoute correctement le symbole $ aux noms des feuilles de calcul, mais n'ajoute pas les crochets requis. Par conséquent, si vous sélectionnez simplement un nom de feuille de calcul et cliquez sur OK, le message d'erreur suivant s'affiche par la suite :
Erreur de syntaxe dans la clause FROM.
Vous devez ajouter les crochets manuellement autour du nom de la feuille de calcul. (Cette zone de liste déroulante permet d'effectuer les modifications nécessaires.) Si vous utilisez le fournisseur ODBC, seules les plages nommées sont répertoriées dans cette liste déroulante. Toutefois, vous pouvez entrer manuellement un nom de feuille de calcul avec les délimiteurs appropriés.

Sélection de données Excel à l'aide de commandes de l'environnement de données

Après avoir configuré la connexion d'environnement de données pour votre source de données Excel, créez un nouvel objet Command. Si vous choisissez Instruction SQL comme Source de données, vous pouvez entrer une requête dans la zone de texte à l'aide de la syntaxe décrite précédemment. Si vous choisissez Objet de base de données comme Source de données, que vous sélectionnez Table dans la première liste déroulante et que vous utilisez le fournisseur Jet, la liste déroulante affiche à la fois les plages nommées et les noms des feuilles de calcul disponibles dans le classeur sélectionné, en commençant par les plages nommées. (Si vous choisissez un nom de feuille de calcul dans cette liste, il n'est pas nécessaire d'ajouter manuellement des crochets autour du nom de la feuille de calcul comme pour le contrôle de données ADO.) Si vous utilisez le fournisseur ODBC, seules les plages nommées sont répertoriées dans cette liste déroulante. Toutefois, vous pouvez entrer manuellement un nom de feuille de calcul.

Modification des données Excel : Editer, Ajouter et Supprimer

Editer

Vous pouvez modifier des données Excel à l'aide des méthodes ADO courantes. Les champs de jeux d'enregistrements qui correspondent aux cellules dans la feuille de calcul Excel contenant des formules Excel (commençant par "=") sont en lecture seule et ne peuvent pas être modifiés. N'oubliez pas que par défaut, la connexion ODBC à Excel est en lecture seule, sauf spécification contraire dans vos paramètres de connexion. Pour plus d'informations, reportez-vous à la section "Utilisation du fournisseur Microsoft OLE DB pour pilotes ODBC" plus haut dans cet article.

Ajouter

Vous pouvez ajouter des enregistrements à votre source d'enregistrements Excel, selon l'espace existant. Toutefois, si vous ajoutez de nouveaux enregistrements en dehors de la plage spécifiée à l'origine, ils ne seront pas visibles si vous effectuez une nouvelle requête sur la plage d'origine. Pour plus d'informations, reportez-vous à la section "Avertissement concernant la spécification des plages".

Dans certaines circonstances, lorsque vous utilisez les méthodes AddNew et Update de l'objet ADO Recordset pour insérer de nouvelles lignes de données dans une table Excel, ADO peut insérer les valeurs de données dans les colonnes incorrectes dans Excel. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
314763
(http://support.microsoft.com/kb/314763/FR/ )
CORRECTIF : ADO insère les données dans des colonnes incorrectes dans Excel
Delete

La suppression de données Excel est soumise à plus de restrictions que celle de données d'une source de données relationnelle. Dans une base de données relationnelle, « ligne » n'a pas de sens en dehors d'« enregistrement » ; dans une feuille de calcul Excel, ceci n'est pas vrai. Vous pouvez supprimer des valeurs dans des champs (cellules). Toutefois, vous ne pouvez pas :
  1. Supprimer un enregistrement complet d'un coup ; sinon le message d'erreur suivant s'affiche :
    La suppression de données dans une table attachée n'est pas gérée par le pilote ISAM.
    Vous ne pouvez supprimer un enregistrement qu'en effaçant le contenu de chaque champ individuel.
  2. Supprimer la valeur d'une cellule contenant une formule Excel ; sinon, le message d'erreur suivant s'affiche :
    L'opération demandée n'est pas autorisée dans ce contexte.
  3. Vous ne pouvez pas supprimer les lignes vides de la feuille de calcul qui contenaient les données supprimées et votre jeu d'enregistrements continuera à afficher des enregistrements vides correspondant à ces lignes vides.
Avertissement concernant la modification de données Excel à l'aide d'ADO : Lorsque vous insérez des données texte dans Excel à l'aide d'ADO, la valeur de texte est précédée d'une apostrophe. Ceci peut provoquer des problèmes par la suite lors du travail avec les nouvelles données.

Récupération de la structure de la source de données (métadonnées) d'Excel

Vous pouvez récupérer des informations sur la structure de votre source de données Excel (tables et champs) à l'aide d'ADO. Les résultats seront légèrement différents selon le fournisseur OLE DB employé, mais les deux fournisseurs renvoient au moins le même petit nombre de champs utiles d'information. Ces métadonnées peuvent être récupérées à l'aide de la méthode OpenSchema de l'objet ADO Connection, qui renvoie un objet ADO Recordset. Vous pouvez également utiliser la puissante Bibliothèque Microsoft ADOX (ActiveX Data Objects Extensions for Data Definition Language and Security) dans ce but. Toutefois, dans le cas d'une source de données Excel, dans laquelle une « table » est soit une feuille de calcul, soit une plage nommée, et un champ est l'un d'un petit nombre de types de données génériques, cette puissance supplémentaire n'est pas utile.

Requête sur les informations de table

De tous les objets disponibles dans une base de données relationnelle (tables, vues, procédures stockées, etc.), une source de données Excel n'expose que les équivalents de table, qui consistent en des feuilles de calcul et des plages nommées définies dans le classeur spécifié. Les plages nommées sont traitées en tant que « Tables » et les feuilles de calcul en tant que « Tables système », et vous ne pourrez pas récupérer beaucoup d'informations utiles sur la table en dehors de cette propriété « table_type ». Pour demander une liste des tables disponibles dans le classeur, utilisez le code suivant : 
Set rs = cn.OpenSchema(adSchemaTables)
Le fournisseur Jet renvoie un jeu d'enregistrements de neuf (9) champs, dont il ne remplit que quatre (4) :

  • table_name
  • table_type (« Table » ou « Table système »)
  • date_created
  • date_modified
Les deux champs de date pour une table donnée affichent toujours la même valeur, qui semble être la « date de la dernière modification ». En d'autres termes, « date_created » n'est pas une information fiable.

Le fournisseur ODBC renvoie, lui aussi, un jeu d'enregistrements de neuf (9) champs, dont il ne remplit que trois (3) :

  • table_catalog, le dossier dans lequel est situé le classeur
  • table_name
  • table_type, comme mentionné plus haut
Selon la documentation ADO, il est possible de récupérer une liste de feuilles de calcul uniquement, par exemple en spécifiant les critères supplémentaires suivants dans la méthode OpenSchema : 
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "System Table"))
Malheureusement, cette méthode ne fonctionne pas dans une source de données Excel avec une version de MDAC ultérieure à 2.0, quel que soit le fournisseur utilisé.

Informations sur les champs de requête

Chaque champ (colonne) d'une source de données Excel est l'un des types de données suivants :

  • numérique (type de données ADO 5, adDouble)
  • monétaire (type de données ADO 6, adCurrency)
  • logique ou booléen (type de données ADO 11, adBoolean)
  • date (type de données ADO 7, adDate, avec le fournisseur Jet ; 135, adDBTimestamp, avec le fournisseur ODBC)
  • texte (un type ADO ad...Char, tel que 202, adVarChar, 200, adVarWChar ou semblable)
L'information numeric_precision d'une colonne numérique est toujours renvoyée avec la valeur 15 (qui est la précision maximale dans Excel) ; l'information character_maximum_length d'une colonne de texte est toujours renvoyée avec la valeur 255 (qui est la largeur d'affichage maximale de texte, mais pas la longueur maximale, dans une colonne Excel). Vous ne pouvez pas obtenir beaucoup d'informations utiles sur les champs en dehors de la propriété data_type. Pour demander une liste des champs disponibles dans une table, utilisez le code suivant : 
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, "TableName", Empty))
Le fournisseur Jet renvoie un jeu d'enregistrements contenant 28 champs, dont il remplit huit (8) pour les champs numériques et neuf (9) pour les champs de texte. Les champs utiles sont généralement les suivants :

  • table_name
  • column_name
  • ordinal_position
  • data_type
Le fournisseur ODBC renvoie un jeu d'enregistrements contenant 29 champs, dont il remplit dix (10) pour les champs numériques et onze (11) pour les champs de texte. Les champs utiles sont les mêmes que ceux notés précédemment.

Énumération des tables et champs et de leurs propriétés

Le code Visual Basic (tel que l'exemple suivant) peut être utilisé pour énumérer les tables et les colonnes dans une source de données Excel et les champs d'information disponibles sur chacune d'elles. Le résultat de cet exemple est situé dans une zone de liste, List1, sur le même formulaire. 
Dim cn as ADODB.Connection
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=" & App.Path & _
"\ExcelSrc.xls;Extended Properties=Excel 8.0;"
	'.Provider = "MSDASQL"
	'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc.xls; "
	.CursorLocation = adUseClient
	.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
List1.AddItem "Tables:	" & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
	strTbl = rsT.Fields("TABLE_NAME").Value
	List1.AddItem vbTab & "Table #" & t & ":	" & strTbl
	List1.AddItem vbTab & "--------------------"
	For f = 0 To intTblFlds - 1
		List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
	Suivant
	List1.AddItem "--------------------"
	Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))
	intColCnt = rsC.RecordCount
	intColFlds = rsC.Fields.Count
	For c = 1 To intColCnt
		strCol = rsC.Fields("COLUMN_NAME").Value
		List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
		List1.AddItem vbTab & vbTab & "--------------------"
		For f = 0 To intColFlds - 1
			List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value
		Suivant
		List1.AddItem vbTab & vbTab & "--------------------"
		rsC.MoveNext
		Suivant
		rsC.Close
		List1.AddItem "--------------------"
		rsT.MoveNext
Suivant
rsT.Close
cn.Close

Utilisation de la fenêtre Données

Si vous créez une liaison de données vers une source de données Excel dans la fenêtre Données de Visual Basic, la fenêtre Données affiche les mêmes informations que celles que vous pouvez récupérer par programmation comme décrit plus haut. En particulier, notez que le fournisseur Jet répertorie à la fois les feuilles de calcul et les plages nommées sous « Tables », tandis que le fournisseur ODBC n'affiche que les plages nommées. Si vous utilisez le fournisseur ODBC et que vous n'avez pas défini de plages nommées, la liste « Tables » restera vide.

Limitations de Microsoft Excel

L'utilisation d'Excel comme source de données est soumise aux limitations internes des classeurs et des feuilles de calcul Excel. Celles-ci sont notamment :

  • Taille de feuille de calcul : 65 536 lignes par 256 colonnes
  • Contenu des cellules (texte) : 32 767 caractères
  • Nombre de feuilles dans un classeur : limité par la mémoire disponible
  • Noms dans un classeur : limité par la mémoire disponible
Retour au début | Envoyer des commentaires

Références

Pour plus d'informations sur l'utilisation d'ADO.NET pour récupérer et modifier des enregistrements dans un classeur Excel avec Visual Basic .NET, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
316934
(http://support.microsoft.com/kb/316934/FR/ )
Comment faire pour utiliser ADO.NET pour récupérer et modifier des enregistrements dans un classeur Excel avec Visual Basic .NET
Pour plus d'informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft :
295646
(http://support.microsoft.com/kb/295646/FR/ )
Procédure de transfert des données d'une source de données ADO vers Excel avec ADO
246335
(http://support.microsoft.com/kb/246335/FR/ )
Comment faire pour transférer des données d'un jeu d'enregistrements ADO vers Excel avec l'Automation
247412
(http://support.microsoft.com/kb/247412/FR/ )
Méthodes de transferts de données vers Excel à partir de Visual Basic
278973
(http://support.microsoft.com/kb/278973/FR/ )
ExcelADO montre comment utiliser ADO pour lire et écrire des données dans des classeurs Excel
318373
(http://support.microsoft.com/kb/318373/FR/ )
Récupérer des métadonnées d'Excel à l'aide de la méthode GetOleDbSchemaTable dans Visual Basic .NET
Retour au début | Envoyer des commentaires

Propriétés

Numéro d'article: 257819 - Dernière mise à jour: lundi 22 mars 2010 - Version: 4.6
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft Excel 2000 Standard
  • Microsoft Visual Basic 6.0 Édition initiation
  • Microsoft Visual Basic 6.0 Édition professionnelle
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 6.0 Édition Entreprise Service Pack 3
  • Microsoft Visual Basic pour Applications 6.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Excel 2002
  • Microsoft Excel 97 Standard
  • Microsoft Excel 95 Standard
Mots-clés : 
kbhowto kbiisam KB257819
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.
Retour au début | Envoyer des commentaires

Envoyer des commentaires

 
Retour au débutRetour au début