I. Mes premiers pas avec Excel▲
Tout d'abord pour pouvoir utiliser Excel dans un projet Visual Basic vous devez rajouter dans le menu Projets - Références, Microsoft Excel x.x.
Ceux qui sont plutôt à l'aise sous VBA (Visual Basic for Application), vous comprendrez très vite que le passage de VBA à VB est extrêmement simple.
Pour commencer nous allons voir quels sont les objets dont nous avons besoin et comment les utiliser. Pour travailler sur une feuille Excel, vous devez tout d'abord ouvrir Excel puis un classeur sur lequel vous allez travailler. Cela se passe en trois étapes :
- ouvrir Excel ;
- sélectionner le classeur par défaut ;
- sélectionner la feuille par défaut.
Voici le code associé à ces trois étapes :
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ajout d'un classeur, car à l'ouverture d'Excel il n'y a aucun classeur d'ouvert
appExcel.Workbooks.Add 'Ceci n'est nécessaire que si vous n'ouvrez pas un fichier existant
'Récupération du classeur par défaut
Set wbExcel = appExcel.ActiveWorkbook
'Récupération de la feuille par défaut
Set wsExcel = wbExcel.ActiveSheetMaintenant que vous savez ouvrir une feuille Excel, voilà comment on referme une application Excel. Cette phase est EXTRÊMEMENT IMPORTANTE. En effet si vous oubliez de fermer Excel à la fin de son utilisation vous pouvez vous avoir des plantages, car vous aurez trop d'Excel d'ouverts et la mémoire de votre ordinateur sera saturée. Par contre à la fin de votre application même si vous oubliez de fermer Excel, il se fermera tout seul sauf si votre application plante. Donc je vous conseille vivement d'effectuer la fermeture d'Excel dès que vous n'en avez plus besoin.
Voici le code associé à la fermeture de l'application Excel :
wbExcel.Close 'Fermeture du classeur Excel
appExcel.Quit'Fermeture de l'application Excel
'Désallocation mémoire
Set wsExcel = Nothing
Set wbExcel = Nothing
Set appExcel = NothingAu cours d'un des programmes que j'ai réalisés, je me suis heurté à un problème qui a déjà été remarqué par plusieurs personnes. Lorsque vous ajoutez un saut de page dans votre feuille, l'application Excel ne se ferme pas même après les lignes écrites ci-dessus. Rassurez-vous tout de même, Excel est automatiquement fermé lorsque votre application se termine. Mais c'est un problème à ne pas négliger lorsque vous avez besoin de travailler avec plusieurs fichiers ouverts cat la mémoire de votre ordinateur va saturer.
Dans le prochain chapitre, nous allons voir comment ouvrir un fichier Excel standard, un fichier CVS, et un fichier texte avec des séparateurs.
II. Ouvrir un fichier spécifique▲
II-A. Ouverture d'un fichier Excel▲
Ouvrir un fichier Excel c'est ce qu'il y a de plus simple. Voici un exemple de code :
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
Set wbExcel = appExcel.Workbooks.Open("C:\MonFichierExcel.xls")
'wsExcel correspond à la première feuille du fichier
Set wsExcel = wbExcel.Worksheets(1)Je ne remarque pas les lignes permettant de fermer le fichier, mais je vous rappelle que ces lignes ne sont pas à négliger, mais si elles ne sont pas obligatoires.
II-B. Ouverture d'un fichier CSV▲
À titre informatif, les fichiers CSV sont des fichiers textes où une ligne du fichier correspond à une ligne de la feuille et les colonnes sont séparées par des ';'.
Voici un petit exemple :
Le fichier :
123,45,,52,65
,10,23,,
,,23,,41Le tableau résultat est le suivant :
|
123 |
45 |
52 |
65 |
|
|
10 |
23 |
|||
|
23 |
41 |
Quand je disais que le fichier Excel était le plus simple, en fait les CSV sont tout aussi simples.
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
Set wbExcel = appExcel.Workbooks.Open("C:\MonFichierCSV.csv")
'wsExcel correspond à la première feuille du fichier
Set wsExcel = wbExcel.Worksheets(1)II-C. Ouverture d'un fichier texte contenant des séparateurs▲
Sous Excel vous pouvez ouvrir des fichiers textes et les afficher sous forme de tableur, car chaque colonne est séparée par un symbole ou alors les colonnes ont une taille fixe. Voici la liste des séparateurs gérés par Excel :
- la tabulation ;
- le point-virgule ;
- la virgule ;
- l'espace.
Mais si cela ne vous convient pas, Excel vous propose de choisir votre propre séparateur. Mais ce ne peut être qu'un seul caractère.
Comme tout bon tableur vous pouvez indiquer à Excel à partir de quelle ligne du fichier vous souhaitez commencer l'import et à quel format (celui par défaut est Windows(ANSI)).
Voici le code pour ouvrir un fichier texte avec comme séparateur le ';' commençant à la deuxième ligne et au format Windows(ANSI) :
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
Workbooks.OpenText Filename:= "C:\MonFichierTexte.txt", Origin:=xlWindows, _
StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, pace:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Set wbExcel=appExcel.ActiveWorkbook
Set wsExcel=wbExcel.ActiveSheetAfin de ne pas être trop perdu, je vous explique à quoi correspond chaque option ci-dessus :
- Filename : nom du fichier (tout le monde avait deviné :oD ) ;
- Origin : indique l'origine du fichier dans notre cas xlWindows signifie Windows(ANSI) ;
- StartRow : indique à partir de quelle ligne le tableau commence ;
- DataType : indique sous quelle forme est stocké le fichier. xlDelimited veut dire que les colonnes sont séparées par un ou plusieurs séparateurs. xlFixedWidth veut dire que les colonnes ont une largeur fixe.
Les options suivantes ne sont valides que si DataType:=xlDelimited
- TextQualifier : permet d'identifier les zones de texte. Par exemple si vous 123;« blabla;blabla »;123 vous pouvez avoir trois ou quatre colonnes suivant ce que vous choisissez comme identifieur de texte. Vous pouvez utiliser xlDoublQuote (c'est notre cas : "), xlSingleQuote (') ou bien xlNone si vous n'avez pas d'identifieur de texte. Donc pour revenir à l'exemple si vous mettez xlDoubleQuote vous obtenez trois champs sinon vous en obtenez quatre ;
- ConsecutiveDelimiter : Si cette propriété est à True alors si vous 1;;2 il ne comptera que deux colonnes, car s'il trouve plusieurs séparateurs à la suite il réagit comme s'il y en avait qu'un ;
- Tab : est à True si la tabulation est un séparateur sinon est à False ;
- Semicolon : est à True si le point-virgule est un séparateur sinon est à False ;
- Comma : est à True si la virgule est un séparateur sinon est à False ;
- Space : est à True si l'espace est un séparateur sinon est à False ;
- Other : si vous ne spécifiez pas de caractère, il est à False. Si vous souhaitez utiliser un caractère que vous définissez vous devez mettre True et rajouter la propriété OtherChar:=« m » : en mettant votre caractère à la place de m ;
- FieldInfo:=Array(1,1) : indique que la largeur des colonnes est aléatoire.
Les options ci-dessous ne sont valides que si DataType:=xlFixedWidth
FieldInfo : c'est une suite de Array(x,y) où x correspond à l'index de la première colonne et y au type de la colonne. y peut prendre les valeurs suivantes :
- xlGeneralFormat Général : 1 ;
- xlTextFormat Texte : 2 ;
- xlMDYFormat Format de date Mois-Jour-Année : 3 ;
- xlDMYFormat Format de date Jour-Mois-Année : 4 ;
- xlYMDFormat Format de date Année-Mois-Jour : 5 ;
- xlMYDFormat Format de date Mois-Année-Jour : 6 ;
- xlDYMFormat Format de date Jour-Année-Mois : 7 ;
- xlYDMFormat Format de date Année-Jour-Mois : 8 ;
- xlEMDFormat Date EMD : 9 ;
- xlSkipColumn Non distribuée : 10.
Voici un exemple où vous souhaitez charger sur 3 colonnes. La première colonne commence à 0 (début de la feuille), la deuxième à la 10e colonne et la troisième à la 15e colonne. Le code obtenu est FieldInfo:=Array(0,1), Array(10,1), Array(15,1)
Le prochain chapitre portera sur la navigation dans une feuille Excel et l'affectation des cellules.
III. Utiliser une feuille Excel▲
Afin d'éviter d'avoir à s'embêter avec les lettres des colonnes, nous allons commencer par déclarer un tableau contenant le nom des colonnes :
Option Base 1
Public colHeader As Variant
colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", _
"AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", _
"BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", _
"CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ")Je me suis arrêté à la colonne CZ, mais vous pouvez en rajouter autant que vous le souhaitez. Je vous conseille de mettre le code ci-dessus dans un module pour que toute votre application puisse utiliser ce tableau.
Pour commencer ce chapitre, nous allons sélectionner une feuille, et calculer la somme des cellules A3 à F3 et mettre le résultat dans la cellule B4.
Dim i As Integer
Dim somme As Integer
Dim appExcel As Excel.Application
Dim sheet As Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un nouveau vierge
appExcel.Workbooks.Add
Set sheet = appExcel.ActiveWorkbook.ActiveSheet
somme = 0
'Parcours des cellules : A correspond à la première case de mon tableau et F à la sixième
For i = 1 To 6
'La propriété Cells(n°ligne,n°colonne) permet de sélectionner une seule cellule
somme = somme + sheet.Cells(3,i).Value
Next i
'La propriété Range(zone) permet de sélectionner une zone. La variable zone est une chaine de caractères de la forme "B5:T20" (cellule du coin haut gauche et cellule bas droite séparée par le caractère ':')
sheet.Range(colHeader(2) & "4:" & colHeader(2) & "4").Select
'La chaine formée par colHeader(2) & "4:" & colHeader(2) & "4" donne "B4:B4" ce qui sélectionne une seule cellule
sheet.Selection.Value = somme
...C'était un petit exemple qui vous montre comment accéder à une cellule directement (grâce à Cells) et comment sélectionner un ensemble de cellules (grâce à Range).
Bien sûr vous pouvez sélectionner aussi bien une ligne qu'une colonne, mais avec la propriété Range c'est assez embêtant, car la première cellule on la connaît, mais pas la dernière, à moins de mettre les valeurs maximales d'Excel. Donc il existe deux propriétés qui sont Rows et Columns.
Pour ce qui font de l'anglais c'est plutôt clair :)
- Columns : permet de sélectionner un ensemble de colonnes consécutives ;
- Rows : permet sélectionner un ensemble de lignes consécutives.
Nous allons commencer par Columns.
III-A. Sélectionner une ou plusieurs colonnes▲
Pour sélectionner un ensemble de colonnes, vous devez faire passer le nom de la première colonne et celui de la dernière. Pour sélectionner les colonnes D à H vous faites :
sheet.Columns("D:H").SelectComme vous n'avez que le numéro des colonnes et non pas les lettres, vous faites :
sheet.Columns(colHeader(4) & ":" & colHeader(8)).SelectDonc pour sélectionner une seule colonne vous devez mettre le même nom de colonne de début et de fin. Mais bien sûr vous êtes un peu faignant comme la plupart des codeurs. Vous préférez passer votre temps à réfléchir à des solutions plutôt qu'à coder. Donc il existe une solution pour gagner un peu. Pour la sélection d'une colonne vous pouvez ne passer que le nom de la colonne à sélectionner ( Coloumns(« D »).Select ) ou bien le numéro de la colonne à sélectionner ( Columns(4).Select ).
III-B. Sélectionner une ou plusieurs lignes▲
Pour sélectionner un ensemble de colonnes, vous devez faire passer le nom de la première colonne et celui de la dernière. Pour sélectionner les lignes 25 à 43 vous faites :
sheet.Rows("25:43").SelectPour sélectionner une seule ligne, vous pouvez faire Rows(« 25 »).Select ou bien Rows(25).Select.
Les techniques de sélection sont valables pour la copie, le coupage, le collage, la suppression et l'insertion. Vous devez juste remplacer Select par Copy, Cut, Paste, Delete, Insert.
III-C. Sélectionner une feuille▲
Vous pouvez soit récupérer une feuille spécifique soit récupérer la feuille active.
'Récupération de la feuille s'appelant maFeuille
Set sheet = appExcel.ActiveWorkbook.Sheets("maFeuille")
'Récupération de la deuxième feuille
Set sheet = appExcel.ActiveWorkbook.Sheets(2)
'Récupération de la feuille active
Set sheet = appExcel.ActiveWorkbook.ActiveSheetIII-D. Changer le nom d'une feuille▲
Rien de plus simple. Vous sélectionnez une feuille avec la méthode ci-dessus, puis vous faites :
sheet.Name = "NouveauNom"IV. Remarque▲
Il est tout à fait possible qu'il y ait des erreurs dans le document. Si vous en trouvez, ou bien souhaitez un peu plus d'explication sur certains points, veuillez m'envoyer un mail afin de mettre à jour l'ensemble de ce document. D'avance merci.