Excel et les bases de donn�es

Travailler avec des bases Access

Avec Excel, il est �galement possible de travailler avec de v�ritables bases de donn�es, par exemple de type Access.

Le grand avantage des bases de donn�es sur les simples fichiers de donn�es est qu'elles disposent de la puissance du langage SQL et qu'elles permettent de mieux structurer l'information en travaillant sur plusieurs tables de donn�es.

Nous n'�tudierons ici que les bases de donn�es Access, ce logiciel �tant int�gr�, comme Excel, � la suite Microsoft Office.

Nous nous concentrerons sur les relations entre Excel et Access et nous supposerons que la base de donn�es ainsi que les tables qui la composent ont �t� cr��es directement dans Access.

Les connexions ADO

Pour travailler sur une base Access � partir de Visual Basic pour Excel, il faut d'abord pouvoir s'y connecter. Nous utiliserons ici un type de connexion disponible sur Excel, les connexions ADO.

Dans le programme Visual Basic nous devons cr�er une connexion d�finie par une cha�ne de connexion, c'est-�-dire un texte expliquant au programme comment se connecter. Mais, avant tout, nous devons configurer Visual Basic. Dans le menu Outils de Visual Basic nous devons s�lectionner R�f�rences puis cocher :

• Microsoft Ado Ext. 6.0 for DDL and Security
• Microsoft ActiveX Data Objects 6.1 Library

Par exemple, si nous donnons le nom Connect � notre connexion, nous devons d'abord la d�clarer puis la cr�er :

Dim Connect As ADODB.Connection
Set Connect = New ADODB.Connection

On peut alors ouvrir la connexion en fournissant au programme le type de connexion et le chemin d'acc�s � la base de donn�es :

With Connect
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Essais\Commerce.accdb"
     .Open
End With

Nous nous connectons ainsi � la base Access Commerce.accdb situ�e dans le dossier C:\Essais. Cette base contient une table Ventes d�finie par trois champs :

• Produit d�fini comme un texte court de 20 caract�res ;
• Vendeur d�fini comme un texte court de 20 caract�res ;
• Valeur d�fini comme un num�rique r�el double.

Les commandes

Nous nous proposons de charger les donn�es suivantes contenues dans la feuille Donn�es du classeur.

Nous allons commencer par vider la table Ventes pour s'assurer qu'elle ne contient aucun enregistrement. Pour cela nous allons utiliser la commande SQL : DELETE FROM.

Pour lancer une requ�te SQL depuis Visual Basic nous devons d�finir une commande que nous avons appel� ici Commande de la mani�re suivante :

Dim Commande As ADODB.Command
Set Commande = New ADODB.Command
Set Commande.ActiveConnection = Connect

Nous indiquons ici au programme que nous voulons utiliser la connexion Connect que nous avons d�finie pr�c�demment.

Nous pouvons maintenant d�finir la commande et la lancer :

With Commande
     .CommandText = "DELETE FROM Ventes "
     .CommandType = adCmdText
     .Execute
End With

Nous pouvons v�rifier dans Access que les enregistrements pr�sents ont bien �t� effac�s.

Nous pouvons �crire nos donn�es dans la table Ventes par le programme suivant :

Set f = ThisWorkbook.Sheets("Donn�es")
For i = 1 To 8
     produit = f.Cells(i, 1)
     vendeur = f.Cells(i, 2)
     va = f.Cells(i, 3)
     valeur = Replace(va, ",", ".")
     With Commande
         .CommandText = "INSERT INTO Ventes VALUES(" & "'" & produit & "','" & vendeur & "'," & valeur & ")"
         .CommandType = adCmdText
         .Execute
     End With
Next i

Nous pouvons v�rifier que les donn�es ont bien �t� charg�es dans la table Ventes.

L'objet Recordset

Nous pouvons �galement lire dans Excel des donn�es contenues dans la base de donn�es. Par exemple, nous allons afficher dans la feuille de calcul Donn�es les enregistrements de la table Ventes.

Pour cela nous devons cr�er dans notre programme un objet Recordset qui recueillera les donn�es provenant de la table Ventes. Avant tout, nous devons configurer Visual Basic et dans le menu Outils/R�f�rences nous devons cocher Microsoft ActiveX Data Object Recordsets 6.0 Library.

Si nous appelons Record notre Recordset, nous devons le cr�er de la mani�re suivante :

Dim Record As ADODB.Recordset
Set Record = New ADODB.Recordset

Nous allons maintenant charger notre objet Recordset gr�ce � une requ�te SQL :

Record.Open " SELECT * FROM Ventes ", Connect

Pour lire notre Recordset et afficher les donn�es dans la feuille de calcul Donn�es, nous allons proc�der ainsi :

Record.MoveFirst
i = 1
Do While Not Record.EOF
     f.Cells(i + 10, 1) = Record("Produit")
     f.Cells(i + 10, 2) = Record("Vendeur")
     f.Cells(i + 10, 3) = Record("Valeur")
     i = i + 1
     Record.MoveNext
Loop

Nous pouvons constater que les donn�es affich�es dans la feuille de calcul sont bien les m�mes que celles que nous avions saisies.

Le programme complet est le suivant :

Sub Ecrire()
'Suppose avoir s�lectionn� dans Outils/R�f�rences :
'Microsoft ActiveX Data Objects 6.1 Library
'Microsoft ActiveX Data Object Recordsets 6.0 Library
'Microsoft Ado Ext 6.0 for DDL and Security

Dim Connect As ADODB.Connection
Dim Record As ADODB.Recordset
Dim Commande As ADODB.Command
Set Connect = New ADODB.Connection

With Connect
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Essais\Commerce.accdb"
     .Open
End With

Set Commande = New ADODB.Command
Set Commande.ActiveConnection = Connect
With Commande
     .CommandText = "DELETE FROM Ventes "
     .CommandType = adCmdText
     .Execute
End With
Set f = ThisWorkbook.Sheets("Donn�es")
For i = 1 To 8
     produit = f.Cells(i, 1)
     vendeur = f.Cells(i, 2)
     va = f.Cells(i, 3)
     valeur = Replace(va, ",", ".")
     With Commande
         .CommandText = "INSERT INTO Ventes VALUES(" & "'" & produit & "','" & vendeur & "'," & valeur & ")"
         .CommandType = adCmdText
         .Execute
     End With
Next i
Set Record = New ADODB.Recordset
Record.Open " SELECT * FROM Ventes ", Connect
Record.MoveFirst

i = 1
Do While Not Record.EOF
     f.Cells(i + 10, 1) = Record("Produit")
     f.Cells(i + 10, 2) = Record("Vendeur")
     f.Cells(i + 10, 3) = Record("Valeur")
     i = i + 1
     Record.MoveNext
Loop

Connect.Close

End Sub

Base Access prot�g�e par un mot de passe

Pour se connecter � une base Access prot�g�e par un mot de passe, il faut ouvrir la connexion en pr�cisant le mot de passe. Cela se fait de la mani�re suivante :

Dim Connect As ADODB.Connection
Set Connect = New ADODB.Connection
With Connect
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & BaseRSP & "; Jet OLEDB:Database Password=ABCD;"
     .Open
End With

O� ABCD est le mot de passe.

Rappelons que pour d�finir un mot de passe sur une base Access, il faut l'ouvrir en mode exclusif. Pour cela, il faut aller dans le menu Ouvrir, s�lectionner Parcourir, choisir la base que l'on souhaite ouvrir puis, dans la liste d�roulante Ouvrir s�lectionner Ouvrir en exclusif. La base s'ouvre alors en mode exclusif.

Il faut alors aller dans le menu Fichier, s�lectionner Chiffrer avec mot de passe puis entrer le mot de passe choisi.

Connexions ADO avec des fichiers CSV

Il est possible de travailler sur des fichiers CSV en utilisant des connexions ADO, cela permet de pouvoir b�n�ficier, tout au moins partiellement, de la puissance de SQL.

La principale diff�rence avec les connexions � une base de donn�es Access r�side dans la cha�ne de connexion. La source ne fait, en effet, plus r�f�rence � une base mais au dossier dans lequel se trouvent les fichiers CSV auxquels on veut se connecter. Il faut �galement pr�ciser le type des fichiers gr�ce � l'option Extended Properties. Celle-ci se pr�sente, par exemple, ainsi :

Extended Properties="text;HDR=NO;FORMAT=Delimited(;)"

Ce texte doit �tre ins�r� dans la cha�ne de connexion, comme il contient des guillemets ceux-ci doivent �tre doubl�s pour pouvoir �tre reconnus comme tels � l'int�rieur de la cha�ne. On pourra �crire, par exemple :

RepertNom = "C:\Essais\"
Set Connect = New ADODB.Connection
propriete = " ; Extended Properties=""text;HDR=NO;FORMAT=Delimited(;)"""
With Connect
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & RepertNom & propriete
     .Open
End With

HDR indique si le fichier CSV contient ou non les titres des colonnes en premi�re ligne. Dans notre exemple, ce n'est pas le cas, si ce l'�tait, il faudrait �crire HDR=YES. Dans le format, nous avons pr�cis� que les fichiers CSV utilisent le point-virgule comme s�parateur. Le format par d�faut est la virgule.

Le fichier Schema.ini

Lorsqu'on utilise des connexions ADO avec des fichiers CSV, il est tr�s recommand�, voire n�cessaire, d'utiliser un fichier texte Schema.ini qui d�crit la structure des fichiers CSV utilis�s. Il se pr�sente, par exemple, sous la forme suivante :

[EssaiCSV.csv]
Format=Delimited(;)
DecimalSymbol=,
Col1=OP Char Width 25
Col2=VAR Char Width 25
Col3=BR Char Width 25
Col4=VA Float
[Libel.csv]
Format=Delimited(;)

Ici, nous avons d�crit deux fichiers CSV : EssaiCSV.csv et Libel.csv. Toutes les fichiers doivent �tre d�crits dans le m�me fichier Schema.ini et appara�tre les uns en-dessous des autres. Le nom du fichier est indiqu� entre crochets.

Pour le fichier EssaiCSV.csv, nous avons pr�cis� qu'il utilisait le point-virgule comme s�parateur et la virgule comme symbole d�cimal. Nous avons ensuite d�crit ses colonnes en pr�cisant pour chaque colonne son nom et son format. Les formats possibles sont les suivants :

Char (un texte)
Float (un nombre r�el)
Integer (un nombre entier court)
LongChar (un m�mo)
Date (une date)

Pour le fichier Libel.csv, seul son s�parateur est pr�cis�, Excel va alors d�terminer le format de chacune des colonnes en lisant les premi�res lignes. Cela peut s'av�rer dangereux si le format peut �tre interpr�t� diff�remment selon les lignes. Par exemple, si les premi�res lignes contiennent des entiers et les derni�res des nombres d�cimaux, Excel va comprendre que le format est Integer et il va supprimer la partie d�cimale des derniers nombres.

Le fichier Schema.ini est un fichier texte, il peut �tre cr�� avec le bloc-notes de Windows. Il doit imp�rativement se trouver dans le m�me dossier que les fichiers CSV qu'il d�crit.

Travailler avec plusieurs fichiers CSV

L'un des principaux avantages d'utiliser une connexion ADO pour travailler avec des fichiers CSV est de pouvoir travailler avec plusieurs fichiers et de faire des jointure avec SQL. Par exemple, supposons que nous disposions des deux fichiers CSV : EssaiCSV.csv et Libel.csv d�crits dans le fichier Schema.ini et qu'ils se pr�sentent ainsi :

OP;VAR;BR;VA
B.1;CSD;BR1;1034,45
B.2;CSD;BR1;654
P.1;TRE;BR1;754
B.1;CSD;BR2;1034,45
B.2;CSD;BR2;654
P.1;TRE;BR2;7540
B.1;CSD;BR3;1034,45
B.2;CSD;BR3;654
P.1;TRE;BR3;754
B.1;CSD;BR4;1034,45
B.2;CSD;BR4;654
P.1;TRE;BR4;754
B.1;CSD;BR5;1034,45
B.2;CSD;BR5;654
P.1;TRE;BR1;754
B.1;CSD;BR1;1034,45
B.2;CSD;BR1;654
P.1;TRE;BR1;754
B.1;CSD;BR1;1034,45
B.2;CSD;BR1;654
P.1;TRE;BR1;754
B.1;CSD;BR1;1034,45
B.2;CSD;BR1;654
P.1;TRE;BR1;754,6785

Et :

OP;LIBEL
B.1;Valeur ajout�e
B.2;Exc�dent brut d'exploitation
P.1;Production

Le programme suivant r�alise la jointure des deux fichiers et affiche les r�sultats dans la feuille Donn�es :

Sub JointureCSV() 'Suppose avoir s�lectionn� dans Outils/R�f�rences :
'Microsoft ActiveX Data Objects 6.1 Library
'Microsoft ActiveX Data Object Recordsets 6.0 Library
'Microsoft Ado Ext 6.0 for DDL and Security

Dim Connect As ADODB.Connection
Dim Record As ADODB.Recordset

RepertNom = "C:\Essais\"

Set Connect = New ADODB.Connection
propriete = " ; Extended Properties=""text;HDR=YES;FORMAT=Delimited(;)"""
With Connect
     .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & RepertNom & propriete
     .Open
End With

Set Record = New ADODB.Recordset

Record.Open "SELECT e.OP, l.LIBEL, e.VAR, e.BR, e.VA FROM EssaiCSV.csv e, Libel.csv l where e.OP=l.OP", Connect

Set f = ThisWorkbook.Sheets("Donn�es")

Record.MoveFirst
i = 1
Do While Not Record.EOF
     f.Cells(i, 1) = Record("OP")
     f.Cells(i, 2) = Record("LIBEL")
     f.Cells(i, 3) = Record("VAR")
     f.Cells(i, 4) = Record("BR")
     f.Cells(i, 5) = Record("VA")
     i = i + 1
     Record.MoveNext
Loop
Record.Close
Connect.Close
End Sub

On obtient :

La commande COMMAND

On peut �galement utiliser la commande Command pour effectuer des requ�tes SQL. Par exemple, dans le programme pr�c�dent, on peut ins�rer la table Ventes1 dans la table Ventes de la mani�re suivante :

Dim Commande As ADODB.Command
Set Commande = New ADODB.Command
With Commande
     .ActiveConnection = Connect
     .CommandText = " INSERT INTO Ventes.csv SELECT * FROM Ventes1.csv "
     .CommandType = adCmdText
     .Execute
End With

Pour que ce programme fonctionne, il faut au pr�alable avoir d�fini la structure du fichier Ventes1.csv dans le fichier texte Schema.ini.

La principale limitation avec les fichiers CSV est qu'on ne peut pas modifier des enregistrements sp�cifiques avec la commande UPDATE ou les supprimer avec la commande DELETE FROM car les fichiers CSV sont des fichiers s�quentiels et que tout nouvel enregistrement est plac� � la fin du fichier.

Ce n'est pas forc�ment un probl�me pour les comptables nationaux car il est pr�f�rable que, comme les comptables d'entreprise, ils n'effacent jamais de donn�es et que, pour effectuer une correction, ils commencent par introduire un enregistrement avec des valeurs de signe oppos� � l'enregistrement qu'ils veulent modifier.

Cela dit, nous avons vu au chapitre pr�c�dent qu'il est toujours possible de modifier ou de supprimer des enregistrements dans un fichier CSV en utilisant les instructions Line Input # et Print #.

Auteur : Francis Malherbe

• Version PDF