Synthèse de plusieurs classeurs

Introduction

Vous êtes à la tête d'une agence immobilière, qui possède des succursales en Suisse romande. Chaque succursale possède un certain nombre d'immeubles à vendre : des maisons, des appartements, des fermes, ... Chaque responsable de succursale tient une feuille de calcul Excel à jour, concernant les biens à vendre dans sa région, comme ceci :

Tous les fichiers ont la même structure :

• Deux colonnes
• Un titre de colonne "Immeuble" et "Nombre"
• Le total en dessous

Chaque Succursale possède un nombre différent d'objets différents (Par exemple, Fribourg a 4 types d'objets différents, alors que Neuchâtel n'en a que 2). A Genève, Lausanne et Neuchâtel, ils ont des maisons individuelles, mais pas à Fribourg. Par contre, à Fribourg, ils possèdent des bungalows.

Chacun des reponsables des différentes villes vous envoie périodiquement leur classeur Excel par E-Mail. Vous désirez rassembler les données pour établir un tableau récapitulatif, comme ceci :

Une fois ce fichier récapitulatif établi, vous en ferez ce que vous voudrez : des graphiques, des tableaux croisés dynamiques, des tris et des filtres, mais tout ceci n'est pas le sujet de ce didacticiel.

Nul besoin de VBA pour effectuer cette opération ! Il suffit d'ouvrir chacun des fichiers des succursales, et de copier-coller les données l'une au dessous de l'autre dans le fichier de récapitulation.

MAIS...

Si vous aviez 250 succursales au lieu de 4, et s'il s'agit d'un travail de consolidation à fournir chaque de semaine, ça va devenir ingérable.

Donc, nous allons programmer !

Comment, donc, ouvrir automatiquement chaque fichier pour recopier les données dans un nouveau classeur que nous nommerons "Recap".

Commencez par créer les 4 classeurs Excel des 4 succursales, avec les données. Pour vous faciliter la vie, je l'ai fait à votre place (parce que je ne vous veux que du bien) : je vous propose de télécharger ce .ZIP, et de décompresser les fichiers dans un nouveau dossier de votre PC.

Création de Recap.xlsm

Une fois que vous avez décompressé vos 4 fichiers dans un dossier, créez un nouveau classeur Excel, et enregistrez-le sous le nom "Recap" (Dans le même dossier que les autres classeurs des villes) Important : Choisissez "Classeur Excel prenant en charge les macros" dans "Type de fichier", comme sur cette image :

Votre dossier contient ainsi : "Recap.xlsm", qui récapitulera les 4 fichiers régionaux Les 4 fichiers régionaux .xlsx

Ouvrez Recap.xlsm, car c'est dans ce fichier que va se construire la macro. Laissez les autres fichiers fermés.

L'onglet Développeur

Si vous n'avez pas l'onglet "Développeur" :

Faites-le apparaître en allant dans le menu Fichier/Options, cliquez sur Personnaliser le ruban, et cochez la case Développeur :

Un mot sur la sécurité

Des gens mal intentionnés peuvent exploiter VBA pour Excel afin de programmer des virus et autres saletés afin de pourrir votre PC. Microsoft redoute ces actions comme la peste, car s'il s'avérait qu'Excel était un vecteur d'infection des ordinateurs, plus personne ne lui ferait confiance, et les ventes tomberaient en chute libre.

Lorsqu'on installe une porte blindée + un sas de sécurité + un digicode + un appareil de reconnaissance vocale à son appartement, il devient difficile d'y accéder, même pour son propriétaire, et on a tendance à laisser tout ouvert, de peur de rester coincé à l'intérieur.

Avec les produits de la gamme Office (Excel inclus), c'est un peu ce qui se passe ! Microsoft a tellement poussé le bouchon de la sécurité, qu'il devient malaisé de tout comprendre !

Je ne vais pas vous donner un cours sur la sécurité : je vais juste vous indiquer où se trouvent ces fameuses options de sécurité complexes, qu'il faudra trifouiller si vous rencontrez des problèmes à l'exécution de vos macros.

Allez dans le menu Fichier/Options/Centre de gestion de la confidentialité, et cliquez sur "Paramètres du centre de gestion de la confidentialité"

Cliquez sur les différentes options de gauche (Editeurs, approuvés, emplacements approuvés, Documents approuvés, ...), et ajustez les paramètres de sécurité petit à petit, jusqu'à ce que votre souci soit réglé. Oui, je sais, ces options sont majoritairement incompréhensibles pour le commun des mortels ! Par exemple, dans paramètres des macros, vous pouvez cocher "Activer toutes les macros"

Je ne peux malheureusement pas vous en dire plus parce que, d'une part, je ne suis pas un spécialiste de la sécurité, et que, d'autre part, tout va dépendre de la politique de sécurité mise en place par le service informatique de votre entreprise (si vous travaillez en entreprise), concernant Office (et donc Excel). Certaines actions pourront vous être simplement interdites !

Ne confondez pas les erreurs induites par des problèmes de sécurité, et des erreurs de programmation, qui n'ont rien à voir. Ce que je dis parait évident, mais il est très facile de confondre un bug auquel vous ne pouvez rien, avec une erreur de programmation ou de logique dont vous êtes responsable.

Préparation de l'environnement VBA

Dans Excel, appuyez sur le raccourci clavier , ou cliquez sur l'icône de l'onglet Développeur.

Ca va ouvrir l'environnement VBA (Visual Basic pour Application) d'Excel. Fermez toutes les fenêtres que vous y voyez, les petites croix encadrées de rouge :

Vous vous retrouvez dans une fenêtre grise :

Allez dans le menu Affichage/Explorateur de projets. (Attention : PAS explorateur d'objets !)

Création d'un module

Cliquez à gauche, sur VBAProject (Recap.xlsm) Allez dans le menu Insertion/Module

Vous créez ainsi un nouveau module dans le dossier des modules, comme ceci : On peut avoir plusieurs modules dans un fichier Excel .xlsm, et chaque module pourra accueillir plusieurs macros.

VBA et Excel sur un même écran

Fermez la fenêtre de gauche (Projet - VBAProject), et disposez vos fenêtres de manière à avoir la fenêtre VBA à côté de votre fenêtre Excel, afin d'avoir votre écran qui ressemble à peu près au mien, comme ceci : Pour ce faire, voici une astuce bien pratique : en cliquant avec le bouton droit de votre souris sur une partie inoccuppée (une partie gris foncée) de la barre des tâches, vous pourrez choisir Afficher les fenêtres en côte à côte, comme sur l'image ci-contre : Attention : Si vous avez d'autres programmes ouverts en même temps, ils occuperont également une partie de votre écran !

Création de notre macro

Bien. Il est temps de commencer à écrire notre Macro ! Dans le module, écrivez sub CreationSynthese, et appuyez sur la touche ENTER de votre clavier, comme sur l'image :

Vous obtenez ce résultat : . Nous allons entrer, petit à petit, les instructions de notre macro entre "Sub CreationSynthese()" et "End Sub".

Ecriture des titres de colonnes

Nous allons commencer par écrire les en-têtes de colonne (Région - Immeuble - Nombre) :

Très facile ! Voici la syntaxe : Range("A1") = "Région" Range("B1") = "Immeuble" Range("C1") = "Nombre" Allez-y, recopiez ces trois lignes, et placez les comme sur l'îmage : Soit vous recopiez "A la main", soit, vous sélectionnez ci-dessus les lignes de code, et vous faites un copier-coller, si vous avez un peu la flemme ;-) Les informaticiens affectionnent la loi du moindre effort.

Mise en retrait du code : indentation

Constatez que nous avons "indenté" le code. Indenter veut simplement dire que nous n'avons pas collé le code tout à gauche, comme ceci :, mais nous l'avons un peu avancé vers la droite (avec la touche tabulation ou la touche espace), comme ceci :

Ce n'est pas obligatoire, mais c'est juste plus simple pour se relire ensuite.

Exécution d'une macro

Ecrire une macro, c'est bien. L'exécuter, c'est mieux ! Qu'elle fonctionne, c'est lidéal !

Rendez-vous dans la fenêtre Excel (pas VBA), et dans l'onglet développeur, cliquez sur : . La liste de vos macros s'affiche (Il n'y en a qu'une, c'est facile). Cliquez dessus et cliquez sur le bouton exécuter : .

Ca maaaaaaaaaarche !

Les trois titres se sont effectivement inscrits en A1, B1 et C1 : . N'est-ce pas encourageant ?

Si ça ne marche pas, et que vous avez des messages d'erreur, ne vous découragez pas ! La plupart du temps, c'est un guillemet qui manque, une faute d'orthographe, ou une bêtise comme ça.

Il existe un raccourci clavier pour accéder rapidement à la liste des Macros, depuis Excel : . J'insiste : depuis Excel ! Si vous êtes dans l'environnement VBA, ça ne fonctionne pas.

Exécution de macro depuis l'environnement VBA

Raccourci clavier F5

Il y a une autre manière d'exécuter une macro, depuis l'environnement VBA cette fois. Effacez le contenu de votre feuille de calcul (Effacez donc Région, Immeuble et Nombre). C'est très important, parce que si vous n'effacez pas, la macro va se réexécuter au même endroit, et vous ne verrez pas si elle a bien tout réécrit au même endroit ou si elle n'a rien fait, vous comprenez le principe ?

Cliquez quelque part dans votre macro, n'importe où : , et appuyez sur la touche de votre clavier : HOP ! La macro s'exécute tout entière, quel que soit l'endroit où vous avez cliqué !

Bien...

Nous n'avons, actuellement, que trois lignes de programmation toutes simples. Mais... Imaginons une très grosse macro de plusieurs dizaines (centaines) de lignes, il peut être intéressant d'exécuter la macro petit à petit.

C'est à dire de prendre le temps de voir chaque ligne s'exécuter. On verrait Région s'écrire dans A1, puis, dans un deuxième temps, Immeuble dans B1, et enfin, Nombre dans C1. En plus d'être intéressante, cette fonctionnalité est très utile pour comprendre un bout de programme qui ne fonctionne pas correctement. ça va permettre d'analyser... De "débugger" le code. Débugger, c'est simplement, chercher les erreurs, chercher pourquoi le programme ne marche pas comme on veut. Eh oui ! mauvaise nouvelle : quand on programme, on fait des erreurs... Tout programmeur fait des erreurs, dont certaines très difficiles à débusquer !

Mais bon... Un programmeur VBA n'est pas chirurgien cardiaque : les erreurs ne provoqueront pas la mort de quelqu'un !

Exécution pas à pas

Pour tester ce mode pas-à-pas, cliquez n'importe où dans votre macro . Et n'appuyez PAS sur , mais appuyez sur . La première ligne de votre macro se surligne en jaune, comme ceci : . Bien. Appuyez à nouveau sur . La ligne suivante se colore en jaune, mais elle n'est PAS encore exécutée : . Maintenant, appuyez une 3^ème fois sur , tout en observant votre feuille Excel : VBA vient d'écrire Région dans A1, et la ligne suivante devient jaune à son tour: . Cliquez encore une fois sur : , encore une fois

Très Important : appuyez une dernière fois sur une fois que End Sub est en jaune, sinon, la macro n'est pas complètement terminée, et vous aurez des problèmes plus tard.

Mise en forme de la ligne de titre

Maintenant, Mettons en forme la ligne de titre (A1, B1, et C1). C'est à dire :

• écrire en gras
• avec un fond jaune pâle.

Ca s'exprime ainsi en VBA :

Range("A1:C1").Interior.Color = 13434879
Range("A1:C1").Font.Bold = True

Nous n'allons pas forcément détailler chaque détail de programmation, comme par exemple, pourquoi écrire Range("A1:C1").Font.Bold = True plutôt que Range("A1:C1") = Bold. car le but de ce didacticiel est plutôt de vous montrer (en gros) comment jongler avec plusieurs classeurs. mais si la syntaxe des objets, propriétés et méthodes vous intéresse, je vous recommande chaudement de consulter cette page.

Nous allons quand même répondre à une question : Comment puis-je savoir que le code couleur "Jaune pâle" correspond à la valeur 13434879 ?

L'enregistreur de macros

Il existe dans Excel, un "enregistreur de macros". Au lieu d'écrire une macro comme nous sommes en train de le faire, on peut demander à Excel d'observer minutieusement ce que nous sommes en train de faire dans Excel, et de le reproduire en langage VBA.

Marche à suivre :

1. Dans l'onglet Développeur d'Excel, cliquez sur cette icone :
2. Donnez un nom de macro quelconque , et cliquez sur
3. TOUT ce que vous faites est enregistré !
   Par exemple, colorez le fond d'une cellule quelconque en fond jaune pâle, et mettez-là en gras.
4. Lorsque vous avez terminé, arrêtez la macro avec ce bouton (Oui, il se trouve à la même place que "Enregistrer une macro")

Rendez-vous à présent dans l'environnement VBA. Cherchez où il a bien pu stocker cette nouvelle macro Tagada ! Il peut l'avoir placé à la suite de votre macro, ou, à son bon vouloir, dans un nouveau module. Voici où je l'ai trouvée, Dans Module2 (Il suffit de double-cliquer dessus pour l'ouvrir) : Ne cherchez pas de logique, il n'y en a pas ! En tout cas, je ne l'ai jamais trouvée.

Je retrouve ainsi mon fameux code couleur 13434879 qui représente mon jaune pâle, ainsi que la syntaxe pour mettre une cellule en gras.

Vous remarquerez que pour passer de ce code :

Range("B1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434879
.TintAndShade = 0
.PatternTintAndShade = 0 End With
Selection.Font.Bold = True

à celui-ci :

Range("A1:C1").Interior.Color = 13434879
Range("A1:C1").Font.Bold = True

Il y a une sacrée marge ! Comment ai-je fait ? ... Je suis programmeur de formation, ce qui me permet d'optimiser le code avec une certaine facilité. Mais ne vous découragez pas ! Ce didacticiel n'a certes pas vocation à vous transformer en programmeur aguerri, mais il vous fournit beaucoup de ficelles pour mettre au point des macros VBA très, très utiles. Un peu d'intuition, un peu (beaucoup) de transpiration, un soupçon d'opiniâtreté, et il n'y a pas de raison que vous n'y arriviez pas ! Et, encore une fois, cette page d'initiation aux objets peut vous être d'un grand secours.

Voici le code de votre macro modifiée (copiez-collez-là dans votre VBA) :

Sub CreationSynthese() Range("A1") = "Région" Range("B1") = "Immeuble" Range("C1") = "Nombre" Range("A1:C1").Interior.Color = 13434879 Range("A1:C1").Font.Bold = True End Sub

Commentaires

Pour l'instant, notre macro est encore toute petite, mais il est nécessaire d'être bien organisé pour pouvoir s'y retrouver lorsqu'elle va grandir. Je vous recommande chaudement de commenter vos macros, c'est à dire d'insérer des lignes de commentaires qui divisent la macro en différentes sections, et/ou qui expliquent son fonctionnement.

Pour ajouter une ligne de commentaire dans une macro VBA, il suffit de commencer une ligne par une apostrophe : '. Les lignes ainsi préfixées seront ignorées par VBA. Modifier votre code de cette manière :

' Macro permettant de créer une synthèse ' des succursales de Suisse romande Sub CreationSynthese() ' Ecriture de la ligne de titre : Range("A1") = "Région" Range("B1") = "Immeuble" Range("C1") = "Nombre" ' Mise en forme de la ligne de titre : Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle) Range("A1:C1").Font.Bold = True End Sub

Testez cette nouvelle version de la macro. Commencez par effacer le contenu de votre feuille Excel. Astuce : Cliquez avec le bouton droit de la souris dans l'angle supérieur gauche de votre feuille, et choisissez Supprimer, comme ceci : Si vous vous contentez d'appuyer sur la touche Suppr ou Del de votre clavier, vous effacerz les textes, mais pas le fond jaune.

Une fois la feuille entièrement vide, exécutez votre macro avec (Afin de l'exécuter pas à pas, comme nous l'avons vu plus haut), et appréciez son bon fonctionnement : la macro écrit les titres, puis les met en gras avec un fond jaune pâle.

N'oubliez pas d'appuyer encore une fois sur une fois que End Sub est en jaune.

Et pourquoi ne pas programmer l'effacement initial ? Après tout, on pourrait assurer le coup, et commencer par effacer le contenu de notre feuille. Comme ceci :

Ajoutez cette ligne de code à votre macro :

Et re-testez la Macro. Il est très important de tester fréquemment votre macro ! N'imaginez pas pouvoir écrire une grande macro d'un coup, et espérer l'exécuter sans encombre... Seuls quelques programmeurs pointus et aguerris peuvent se targuer d'une telle prouesse !

Ouverture et fermeture du fichier Genève.xlsx

Maintenant que les titres sont en place, nous allons rechercher les données des classeurs régionaux pour les recopier ici. J'habite Genève, je propose donc de commencer par ... Genève ;-)

L'ouverture d'un fichier se code comme ceci en VBA :

La fermeture se dit comme ceci, par contre :

Les deux syntaxes suivantes sont fausses et/ou ne fonctionnent pas :

Workbooks.Close "C:\Mon dossier\Mon sous-dossier\Genève.xlsx"
Workbooks("C:\Mon dossier\Mon sous-dossier\Genève.xlsx").Close

Je n'ai pas dit que c'était logique, mais c'est ainsi.

Afin de tester l'ouverture (et la fermeture) de votre fichier (classeur) : Créez une nouvelle macro sous CreationSynthese, appelée "MonTest" et testez-là avec . Attention : Il vous faudra évidemment remplacer mon propre chemin d'accès (F:\Atelier\VBA Excel\) par le vôtre. Sub Test() Worbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx" Worbooks("F:\Atelier\VBA Excel\Genève.xlsx").Close End Sub Vous verrez alors le fichier s'ouvrir, et, une pression de F8 plus tard, se refermer.

Nous venons de créer une nouvelle petite macro : Test(). Durant tout ce didacticiel, je vous proposerai souvent de créer de petites macros de test, chaque fois appelées Test()... MAIS ... Vous n'avez pas le droit d'avoir plusieurs macros de même nom dans un même module. Donc, je vous conseille de soit :

• Remplacer chaque fois l'ancienne macro Test() par la nouvelle
  OU
• Créer des macros l'unes en dessous de l'autre, que vous nommerez Test1(), Test2(), Test3(), etc.

Bien.

Revenons à notre macro principale : CreationSynthese()

Une fois que vous avez constaté son bon fonctionnement, ajoutez ces deux instructions d'ouverture et de fermeture à votre macro principale CreationSynthese :

Copier-coller

Maintenant, il s'agit de copier les données de Genève.xlsx vers Recap.xlsm. En d'autres mots, copier les cellules A2:B5 de Genève vers B2:C5 de Recap.

Commençons par essayer de copier la cellule A2 de Genève.xlsx vers Recap.xlsm, comme ceci (pour gagner de la place, je ne vous affiche que la partie de la fin de la macro qui nous intéresse) :

Ca fonctionne très bien. Malheureusement, nous ne pouvons pas procéder ainsi pour une plage de cellules, ça ne marche pas :

Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2:C5") = Workbooks("Genève.xlsx").Sheets("Feuil1").Range("A2:B5")

Du coup, on est obligé de se taper la copie, cellule par cellule :

Et encore ! Nous n'avons que quelques données ! Imaginez un tableau avec des milliers de lignes : ingérable !

Heureusement, il existe d'autres techniques, notamment celle du copier-coller. ça se passe comme ceci :

Concernant la copie,  le code parle de lui-même :

Mais pour le collage, on aurait pensé que cette ligne suivante aurait fonctionné :

Or, il n'en est rien. Il est indispensable de :

1. Activer Recap.xlsm (L'équivalent de cliquer dessus avec la souris)

2. Sélectionner ensuite la cellule en haut à gauche de "à partir de où" on veut coller :

3. De (enfin !) coller les cellules, directement dans la feuille ainsi préparée (on ne doit étrangement pas préciser la cellule B2)

Voici la macro actuelle (Que vous pouvez copier-coller directement dans votre macro principale):

Les points d'arrêt

Pour tester cette macro, vous pouvez bien entendu toujours utiliser F8. Ce qui est ennuyeux, c'est qu'avec F8, vous devez recommencer toute la macro depuis le début. En fait, vous aimeriez que la macro s'exécute à toute vitesse jusqu'à l'ouverture de genève.xlsx, et, ensuite, pas à pas.

Marche à suivre :

Maintenant, exécutez la Macro avec . Elle s'exécute à toute vitesse, mais se stoppe net à la ligne en rouge ! Maintenant, appuyez petit à petit sur F8, plusieurs fois. La suite de la macro s'exécute pas à pas. N'oubliez pas d'appuyer encore une fois sur F8 lorsque le End Sub est en jaune (Je me répète, mais on l'oublie tout le temps)

Bien, bien, bien ! Vous suivez toujours ? On dirait, puisque vous êtes toujours là !

Nous sommes donc arrivés à copier les données d'un classeur à l'autre...

MAIS... Nous avons utilisé des plages de référence fixes (A2:B5). Or, comme on l'a vu en haut de ce didacticiel, les différentes villes possèdent un nombre différent de données.

Même pour une seule ville, en fait ! Prenons Genève : quand on va recevoir à nouveau par e-mail la nouvelle version actualisée de ce même fichier Genève.xlsx (dans un mois par exemple), qu'est ce qui prouve qu' il y aura toujours le même nombre de lignes à copier qu'aujourd'hui ? ... Il y aura peut-être d'autres bâtiments ! On construit beaucoup, sur Genève !

Sélection de l'ensemble d'un tableau : UsedRange

Nous allons donc devoir récupérer toutes les données ... dynamiquement ! Et c'est la propriété UsedRange (Tableau réellement utilisé) qui va nous être d'une grande aide.

Créez une nouvelle macro sous votre macro principale, comme ceci :

Testez-là avec F8. L'ensemble de tout le tableau réellement utilisé (A1:B6) au sein de la feuille est sélectionné :

Ce UsedRange va nous permettre de copier les données qui nous intéressent avec cette instruction :

Mais ... ce n'est pas parfait ! En effet, UsedRange inclut la ligne de titre (1), et la ligne de total (6). Or, nous ne voulons PAS copier ces deux lignes ! Et pour modifier ce "point de détail", nous allons devoir transpirer un peu !

Pour ce faire, Je vais vous expliquer plusieurs notions de programmation qui sembleront ne rien à voir avec notre problème, mais nous remettrons tout ensemble pour résoudre ce souci de sélection trop vaste.

UsedRange.Rows.Count permet de connaître le nombre de lignes en usage (en l'occurrence : 6).

Affichage d'une boîte de message : MsgBox

L'instruction MsgBox permet d'afficher un petit message à l'écran. C'est l'abréviation de MeSsaGeBOX - Boîte de message.

Par exemple :

Essayez. Créez une petite macro sous votre macro principale, comme ceci :

Lancez-là avec F5. Cette boîte de message apparaît : . Cliquez simplement sur .

Sympa, non ?

Maintenant que vous comprenez le principe de ce MsgBox, modifiez la macro de la manière suivante :

Testez-là avec F5. Il affiche le nombre de Rows (Lignes) du tableau réellement utilisé, c'est à dire 6 : .

Concaténation avec "&"

Passons à un autre sujet : nous allons voir comment "construire une phrase" en concaténant plusieurs éléments. Concaténer veut simplement dire "Coller l'un à la suite de l'autre".

Modifiez votre macro comme suit :

Sub Test() Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
MsgBox "Il y a " & ActiveSheet.UsedRange.Rows.Count & " lignes dans notre tableau"
Workbooks("Genève.xlsx").Close End Sub

Le signe & fonctionne un peu comme du ciment entre deux briques : "Brique" & "Brique" & "Brique" & "Brique. ça commence toujours par une brique, et ça se termine toujours par une brique. Et il y a du ciment entre chaque brique. Une brique peut-être du texte, comme "il y a", mais aussi une variable dynamique, comme ActiveSheet.UsedRange.Rows.Count par exemple, ou Range("A1").

On peut également faire des calculs sur les variables : ActiveSheet.UsedRange.Rows.Count * 2 va donner : 12.

Le code suivant :

Affiche :

Grâce à &, nous allons pouvoir déterminer la plage de cellules à sélectionner correctement : sans la première, ni la dernière ligne, c'est à dire : A2:B5

En fait, quelle que soit la taille du tableau, ça commencera toujours par A2, et ça se terminera toujours dans la colonne B, mais, et c'est la difficulté, B Combien ? B "Le nombre de lignes dans UsedRange MOINS 1. Comme ceci :

Variables

Comment expliquer une variable simplement ? ... C'est une sorte de post-it sur lequel on écrit une valeur... Un post-it qui aurait un nom... Par exemple, imaginons un post-it nommé "TarteAuxPommes", sur lequel on écrit : 650.

Sub Test() ' On crée un nouveau post-it appelé TarteAuxPommes, et on y écrit dessus : 650
TarteAuxPommes = 650

' L'instruction suivante Affiche 700 (Mais TarteAuxPommes Vaut toujours 650) :
MsgBox TarteAuxPommes + 50
' Maintenant, on affiche 650 :
MsgBox TarteAuxPommes
' Là, on n'affiche rien du tout (Il manque un "s" à la fin... Du coup, c'est comme si c'était un "post-it vide") :
MsgBox TarteAuxPomme
' Et là, on affiche carrément TarteAuxpommes (Il ne faut pas mettre de guillemets, sinon, il affiche textuellement ce qu'il y a à l'intérieur) :
MsgBox "TarteAuxpommes"
' Maintenant, on gomme 650, et on écrit à la place : 1001
TarteAuxPommes = 1001
' Et ici en dessous, devinerez-vous ce qu'il va afficher ?
TarteAuxPommes = "Le conte des " & TarteAuxPommes & " nuits."
MsgBox TarteAuxPommes End Sub

Actuellement, dans notre macro, l'intérêt d'une variable est limité : au mieux, ça permet une meilleure lisibilité du code :

Sub Test()
LignePresqueToutEnBas = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
Range("A2:B" & LignePresqueToutEnBas).Select End Sub

Mais par la suite, nous verrrons que ces variables sont 'achement intéressantes !

Tailles de tableau variable

Bien ! Maintenant, couplons nos connaissances en copie que nous avons vu plus haut avec la concaténation. Le précédent code était :

Sub CreationSynthese() ' Effacement de la feuille
Cells.Delete
' Ecriture de la ligne de titre :
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
' Mise en forme de la ligne de titre :
Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle)
Range("A1:C1").Font.Bold = True
' Ouverture de genève.xlsx :
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
' Copie des données :
Workbooks("Genève.xlsx").Sheets("Feuil1").Range("A2:B5").Copy
Workbooks("Recap.xlsm").Activate
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2").Select
Workbooks("Recap.xlsm").Sheets("Feuil1").Paste
' Fermeture de Genève.xlsx :
Workbooks("Genève.xlsx").Close End Sub

Que nous allons joyeusement remplacer par celui-ci :

Sub CreationSynthese() ' Effacement de la feuille
Cells.Delete
' Ecriture de la ligne de titre :
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
' Mise en forme de la ligne de titre :
Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle)
Range("A1:C1").Font.Bold = True
' Ouverture de genève.xlsx :
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
' Copie des données :
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks("Genève.xlsx").Sheets("Feuil1").Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2").Select
Workbooks("Recap.xlsm").Sheets("Feuil1").Paste
' Fermeture de Genève.xlsx :
Workbooks("Genève.xlsx").Close End Sub

Le résultat est assez probant, n'est-il pas ?

Maintenant, il s'agit de préciser, dans la colonne A, que les données proviennent du classeur Genève.xlsx. Sinon, lorsque les données des autres villes seront empilées en dessous, comment faire pour savoir quelle donnée vient de quelle ville ?

Comme ceci :

Mais évidemment, il faut rendre cette instruction dynamique aussi, selon le nombre de lignes :

Cette instruction trouve sa place dans notre code, ici :

Une deuxième ville

Occupons-nous à présent d'une deuxième ville. Par exemple : Fribourg.xlsx. La manoeuvre d'importation est identique, à un point de détail près : on ne doit pas copier-coller les données de Fribourg à partir de B2, comme avec Genève :

MAIS...

On doit se placer une ligne plus bas que la dernière ligne du tableau (dans la ligne 6):

Nous allons être confronté à deux difficultés : une petite et une grosse (nous parlerons de la grosse un peu plus bas).

La première et petite difficulté consiste à déterminer l'endroit de collage dans Recap.xlsm. En effet, lors du premier fichier genève.xlsx, c'était facile, c'était dans la cellule B2 .

Workbooks("Recap.xlsm").Activate
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2").Select
Workbooks("Recap.xlsm").Sheets("Feuil1").Paste

Mais maintenant, ce n'est donc plus dans la cellule B2, mais dans B6... Plus précisément : pile-poil en dessous du tableau existant.

Maintenant, la 2^ème difficulté consiste à écrire, dans les cellules de A6 à A9 : Fribourg.xlsx.

La fin, c'est facile : c'est toujours ActiveSheet.UsedRange.Rows.Count... mais le début ? Comment lui faire comprendre qu'il faut commencer à A6 ?

Pas si simple !

La solution consiste à créer une variable ! Vous vous souvenez ? Nous allons mémoriser ActiveSheet.UsedRange.Rows.Count AVANT de copier le contenu du classeur Fribourg.xlsx : Nous aurons bien 5 (Il suffira d'ajouter 1 pour avoir 6), puisque APRES, ActiveSheet.UsedRange.Rows.Count contiendra ... 9 !

Ca va ? Vous n'avez pas la migraine ?

Démonstrtation :

Vous suivez ? Bien !

Voici le code complet à ce stade du didacticiel (pour les deux premières villes Genève et Fribourg) :

Sub CreationSynthese() ' Effacement de la feuille
Cells.Delete
' Ecriture de la ligne de titre :
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
' Mise en forme de la ligne de titre :
Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle)
Range("A1:C1").Font.Bold = True

' Traitement de Genève.xlsx :
' --------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks("Genève.xlsx").Sheets("Feuil1").Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2").Select
Workbooks("Recap.xlsm").Sheets("Feuil1").Paste
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count) = "Geneve.xlsx"
Workbooks("Genève.xlsx").Close

' Traitement de Fribourg.xlsx :
' ----------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Fribourg.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks("Fribourg.xlsx").Sheets("Feuil1").Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
Workbooks("Recap.xlsm").Sheets("Feuil1").Paste
ActiveSheet.Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = "Fribourg.xlsx"
Workbooks("Fribourg.xlsx").Close End Sub

Objets et propriétés

Penchons-nous d'un peu plus près sur certaines instructions : Il y a certaines lignes de code qui précisent de quel classeur et feuille il s'agit, comme par exemple :

Et d'autres pas, comme celle-ci :

En réalité, il n'est souvent pas nécessaire de préciser le classeur ni la feuille, car il s'agit, par défaut, du classeur courant, et de la feuille courante... Où on se trouve, quoi !

Mais... dans notre exemple, nous manipulons plusieurs classeurs. Quand on y regarde de plus près, ce n'est pas évident de savoir du premier coup d'oeil où on se trouve (Dans Recap ? Fribourg ? Genève ?). Pour bien faire, on devrait systématiquement indiquer le nom du classeur et de la feuille dans laquelle on désire travailler.

Seulement, l'écriture devient lourde, regardez :

Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B1") = "Région"
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2") = "Immeuble"
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B3") = "Nombre"

Propriété par défaut

Si on voulait être vraiment précis, on devrait même indiquer la propriété .Value, comme ceci :

Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B1").Value = "Immeuble"

La propriété Value est la propriété par défaut. C'est à dire que si on ne l'indique pas, c'est ce que VBA comprend. Comment vous dire ? ... C'est un peu comme si on vous demandait . "Qui êtes-vous ?"... Vous donnez votre nom... pas votre pointure de chaussures ! Et si on vous demande "Quel est votre nom ?", vous donnez aussi votre nom. On dit que "Nom" est votre "propriété par défaut".

Si on veut connaître votre pointure de chaussure, on vous demande explicitement . "Quelle est votre pointure de chaussure" ?

En VBA, c'est pareil, quand on parle d'une autre propriété que Value, on doit l'indiquer explicitement, comme ceci :

Dans ce cas, nous parlons de la propriété Bold (Gras) du sous-objet Font (Police de caractère) de l'objet Range("A1:C1") (La plage de cellule qui va de A1 jusqu'à C1). Si vous voulez en savoir plus sur les objets, propriétés et méthodes, rendez-vous sur ma page "Quel sont les objets".

Références de feuilles et de classeurs

Ici, nous nous contentons d'élaguer le code en retirant ce qui n'est pas vraiment nécessaire :

Sub CreationSynthese() ' Effacement de la feuille
Cells.Delete
' Ecriture de la ligne de titre :
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
' Mise en forme de la ligne de titre :
Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle)
Range("A1:C1").Font.Bold = True

' Traitement de genève.xlsx :
' --------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks("Genève.xlsx").Sheets("Feuil1").Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B2").Select
Workbooks("Recap.xlsm").Sheets("Feuil1")ActiveSheet.Paste
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count) = "Geneve.xlsx"
Workbooks("Genève.xlsx").Close

' Traitement de Fribourg.xlsx :
' ----------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Fribourg.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Workbooks("Fribourg.xlsx").Sheets("Feuil1").Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Workbooks("Recap.xlsm").Sheets("Feuil1").Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
Workbooks("Recap.xlsm").Sheets("Feuil1")ActiveSheet.Paste
ActiveSheet.Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = "Fribourg.xlsx"
Workbooks("Fribourg.xlsx").Close End Sub

Qui nous donne donc le code suivant. Plus simple à déchiffrer, non ? :

Sub CreationSynthese() ' Effacement de la feuille
Cells.Delete
' Ecriture de la ligne de titre :
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
' Mise en forme de la ligne de titre :
Range("A1:C1").Interior.Color = 13434879 '(Jaune pâle)
Range("A1:C1").Font.Bold = True

' Traitement de genève.xlsx :
' --------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
Range("B2").Select
ActiveSheet.Paste
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count) = "Geneve.xlsx"
Workbooks("Genève.xlsx").Close

' Traitement de Fribourg.xlsx :
' ----------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Fribourg.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = "Fribourg.xlsx"
Workbooks("Fribourg.xlsx").Close End Sub

Rendre le code de Genève et Fribourg identiques

Puisque nous sommes en mode "simplification", ne pourrait-on pas avoir exactement le même code pour Genève et Fribourg ?

On a été obligé de complexifier un peu le code de Fribourg, parce qu'on ne savait pas ou commencer la copie... Mais à la limite, si on reprend le code de Fribourg pour le coller à la place du code de Genève, ça devrait fonctionner, non ?

Quand on y réfléchit, lorsque nous avons juste les lignes de titre, DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1 contient ... 2, puisque la taille totale du tableau mesure... 1 seule ligne.

Testez par vous-même :

' Traitement de genève.xlsx :
' --------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Range("B2").Select
Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count) = "Geneve.xlsx"
Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = "Genève.xlsx"
Workbooks("Genève.xlsx").Close

' Traitement de Fribourg.xlsx :
' ----------------------------
Workbooks.Open "F:\Atelier\VBA Excel\Fribourg.xlsx"
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = "Fribourg.xlsx"
Workbooks("Fribourg.xlsx").Close

Et ça marche ! ...

Du coup, le code est un peu plus "complexe", mais c'est exactement le même pour les deux fichiers !

Maintenant, il suffit de copier-coller le code de Genève (ou de Fribourg) l'un en dessous de l'autre pour traiter toutes les autres villes (Lausanne et Neuchâtel), et le tour est joué !

Vous me direz qu'on aurait pu avoir juste la première ville différente des autres, et qu'on avait pas besoin de se casser la tête. Vous avez raison ! Mais, d'une part, les programmeurs aiment rédiger du code "Propre", "Elégant", et, surtout, lorsque nous allons parcourir toute la série de villes avec une boucle, vous constaterez que ça en valait la peine.

Dans notre cas, nous n'avons que 4 villes à traiter... Et si on en avait 250 ? ... Pire encore : si, parfois, il y avait de nouvelles villes qui se greffaient (Tout à coup, une succursale "Nyon" par exemple)... Il faudrait chaque fois modifier, ajuster le code VBA, qui mesurerait 2 kilomètres de haut !

L'idéal serait que VBA parcoure tout le dossier, et, pour chaque ville qu'il rencontre, effectue le même traitement de copier-coller.

Eh bien, c'est possible ! Et c'est maintenant !

Les boucles

Pour ce faire, nous allons faire un détour par les boucles. Une boucle signifie simplement "Refaire la même chose un certain nombre de fois", comme un circuit de Formule 1 en quelque sorte...

Une petite leçon d'anglais s'impose avant de commencer :

• "While" veut dire : "Tant que"
• "Wend" veut dire : "Fin Tant que"

ATTENTION : Avant d'essayer le code ici plus bas, sachez que pour quitter un programme qui tourne trop longtemps, ou indéfiniment, il faut appuyer sur la combinaison de touches de votre clavier. (La touche pause break se trouve en haut à droite de votre clavier).

Le code suivant va afficher "Je suis content" grâce à MsgBox, tant que la variable Tagada est égale à 36 :

Que se passe-t-il ? Il affiche "je suis content" indéfiniment... C'est donc une boucle... infinie !

Constatez que j'ai indenté le texte à l'intérieur du While et du Wend pour faciliter la relecture du code.

Comment faire pour que cette boucle s'exécute un certain nombre de fois, mais pas indéfiniment ?

Comme ceci :

Sub test()
Tagada = 1 ' Tagada vaut donc 1 à la base
While Tagada < 5 ' Tant que Tagada est plus petit que 5 ... ce qui est le cas puisque tagada vaut 1 MsgBox "Je suis content" ' On affiche qu'on est content, ok
Tagada = Tagada + 1 ' Tagada est égal à lui-même + 1, donc 2 Wend ' Comme Tagada vaut 2, et est donc toujours plus petit que 5, on revient au MsgBox, et ainsi de suite... End Sub

Au 2^ème tour, Tagada vaut 2
Au 3^ème tour, Tagada vaut 3
Au 4^ème Tour, Tagada vaut 4
Et au 5^ème tour, ben y'a pas de 5^ème tour, puisque Tagada vaut alors 5, et il faut qu'il soit plus petit que 5 pour continuer à dire "Je suis content". Il va donc dire "Je suis content" 4 fois, et s'arrêter.

Liste des fichiers avec Dir

Quittons les boucles temporairement pour s'occuper du listing d'un dossier.

Dans mon cas personnel, j'ai placé mes fichiers d'exercice dans le dossier F:\Atelier\VBA Excel\. Dans votre cas, il faudra évidemment adapter ce chemin d'accès...

L'instruction Dir permet de récupérer un fichier d'un dossier particulier. Le code suivant va afficher le premier fichier qu'il trouve... Pas forcément Genève.xlsx, mais n'importe lequel (peut-être le premier par ordre alphabétique ?) - Peu importe, en fait.

Va afficher :

L'instruction Dir possède une caractéristique très intéressante : pour lister les fichiers un par un, Il suffit d'initier le premier Dir avec le chemin d'accès, et, ensuite, simplement répéter Dir, comme ceci :

Sub test() MsgBox Dir("F:\Atelier\VBA Excel\") ' Fribourg.xlsx
MsgBox Dir ' Genève.xlsx
MsgBox Dir ' Lausanne.xlsx
MsgBox Dir ' Neuchâtel.xlsx
MsgBox Dir ' Recap.xlsm
MsgBox Dir ' Rien... (ben non, y'a plus de fichier...)
MsgBox Dir ' Et là, on a carrément une erreur ! End Sub

Affinons le listing : n'affichons que les fichiers dont l'extension est xlsx, et PAS xlsm :

Sub test() MsgBox Dir("F:\Atelier\VBA Excel\*.xlsx") ' Fribourg.xlsx
MsgBox Dir ' Genève.xlsx
MsgBox Dir ' Lausanne.xlsx
MsgBox Dir ' Neuchâtel.xlsx
MsgBox Dir ' Rien... (ben non, y'a plus de fichier...)
MsgBox Dir ' Et là, on a carrément une erreur ! End Sub

Le seul "petit" souci est ... qu'on ne doit pas se tromper quant au nombre de "Dir" qu'on doit empiler ! Si on dépasse le nombre de fichiers : Hop ! Erreur ! Nous allons gérer ça dans peu de temps !

La fonction de taille de texte : Len

Len (raccourcissement de Length - Longueur), permet de connaître le nombre de caractères d'une variable ou d'un texte, comme ceci :

Bien. Assemblons la fonction Dir, &, Len et une variable NomFichier. Nous allons afficher le nom de chaque classeur, avec le nombre de caractères qu'il contient.

Sub Test() NomFichier = Dir("F:\Atelier\VBA Excel\*.xlsx")
   MsgBox NomFichier  & " contient " & Len(NomFichier) & " caractères."
'  MsgBox Fribourg.xlsx & contient &      13           &   caractères.
NomFichier = Dir
MsgBox NomFichier & " contient " & Len(NomFichier) & " caractères."
NomFichier = Dir
MsgBox NomFichier & " contient " & Len(NomFichier) & " caractères."
NomFichier = Dir
MsgBox NomFichier & " contient " & Len(NomFichier) & " caractères."
NomFichier = Dir ' C'est cette ligne qui nous intéresse !
  MsgBox NomFichier & " contient " & Len(NomFichier) & " caractères."
' MsgBox            &   contient &      0            &   caractères. End Sub

Va donner le résultat suivant :

Fribourg.xlsx contient 13 caractères.
Genève.xlsx contient 11 caractères.
Lausanne.xlsx contient 13 caractères.
Neuchâtel.xlsx contient 14 caractères.
 contient 0 caractères

Et c'est la dernière ligne qui nous intéresse ! Après qu'il ait lu le dernier fichier, il lit une sorte de "fichier vide" de 0 caractères !

Boucler sur tous les fichiers jusqu'au fichier vide

L'idée est de lire les fichiers tant que la longueur du fichier lu soit plus grande que ... 0 caractères. Comme ceci :
The idea is to read files        While        Len           (File)                   >                     0 characters. Like this :

Sub Test() NomFichier = Dir("F:\Atelier\VBA Excel\*.xlsx") ' NomFichier = le premier fichier trouvé : disons Fribourg.xlsx
While Len(NomFichier) > 0 ' La longueur de Fribourg.xlsx est de 13 caractères, donc 13 > 0... MsgBox NomFichier ' On affiche : Fribourg.xlsx
NomFichier = Dir ' Hop ! On  lit le fichier suivant ! La variable NomFichier contient maintenant, disons, Genève.xlsx Wend ' Comme la longueur de Genève.xlsx est > 0, on recommence, et ainsi de suite avec lausanne.xlsx et Neuchâtel.xlsx End Sub
On amorce la pompe avec NomFichier = Dir("F:\Atelier\VBA Excel\*.xlsx"), et, ensuite, on passe de fichier en fichier grâce à NomFichier = Dir qui lit chaque fois le fichier suivant.

Et quand le Dir contient le fameux fichier de 0 caractère de long, Hop ! il sort de la boucle et continue son petit bonhomme de chemin après le Wend. Bon, ici, après le Wend, c'est End Sub... mais plus tard, il y aura d'autres instructions entre Wend et End Sub !

Ces boucles While Wend ne sont pas forcément faciles à comprendre du premier coup, mais on les utilise énormément en programmation ! Ici, elles nous servent à parcourir tout un dossier, quel que soit le nombre de fichiers qui s'y trouve, mais elles peuvent avoir un nombre incalculable d'applications dans tous les domaines.

Se placer dans un dossier : ChDir

Une petite dernière chose avant de terminer : comme nous allons utiliser une variable qui va parcourir le contenu du dossier NomFichier = Dir("F:\Atelier\VBA Excel\*.xlsx"), cette variable ne va pas contenir le chemin d'accès complet au fichier (F:\Atelier\VBA Excel), mais juste le nom du fichier (Genève.xlsx par exemple). Jusque là, nous n'avions pas eu de problème, car nous écrivions "en dur" : Workbooks.Open "F:\Atelier\VBA Excel\Genève.xlsx. Mais ici, nous allons devoir "nous placer" dans le bon dossier avec une instruction ChDir (Change Directory - Change Dossier) car nous allons utiliser une variable ClasseurRegional, qu'il faudra ouvrir.

ChDir "F:\Atelier\VBA Excel"

Voici le code intégral de notre macro de base :

Le résultat n'est-il pas sympathique ?:

Dans un souci de perfection, peaufinons encore quelques petites choses :

• Gommer tous les ".xlsx" de la colonne A, qui sont disgracieux
• Ajuster la taille des colonnes
• Ajouter une ligne de total général, pour connaître le nombre de constructions total

Rechercher-remplacer

Commençons par nous débarraser des ".xlsx" vilains pas beaux.

Nous allons utiliser la bonne vieille instruction "Remplacer", afin de rechercher toutes les occurrences de "xlsx" dans la colonne A, et les remplacer par ... rien (en fait : "" - deux guillemets l'un contre l'autre).

La fonction Remplacer se trouve dans le menu "Accueil", tout à droite : . Il s'agit donc de sélectionner la colonne A, et de remplacer comme ceci : , mais par programmation, bien entendu ! Rien ne vous empêche d'enregistrer une macro qui fait ça, pour voir comment VBA la construit.

Si vous enregistrez une macro, vous constaterez qu'il nous a pondu une instruction bien compliquée !

Si vous désirez mieux comprendre cette instruction, je vous renvoie à nouveau à cette page.

Ici, je vais me contenter de vous dire que ces quatre lignes sont "résumables" à une seule ligne, toute simple :

Il vous suffira d'ajouter cette ligne à la fin de votre macro de base, comme ceci :

Sub CreationSynthese() ... ...
Workbooks(ClasseurRegional).Close
ClasseurRegional = Dir Wend
Columns("A:A").Replace ".xlsx", "" End Sub

Optimisation du code

Après le Wend, pas avant... Si vous placez cette ligne ici :

Sub CreationSynthese() ... ...
Workbooks(ClasseurRegional).Close
ClasseurRegional = Dir
Columns("A:A").Replace ".xlsx", "" Wend End Sub

Ca marchera tout aussi bien, mais il va effectuer le remplacement à chaque fichier... C'est complètement inutile ! Autant qu'il ne le fasse qu'une seule fois, à la fin ! Vous ne vous rendez pas compte, on gagne ainsi au moins un centième de seconde, wouaw !

Bon, je rigole avec mon centième de seconde... ! Mais, sans plaisanter, il existe des macros qui traitent des milliers de fichiers, des dizaines de milliers de lignes Excel, et qui mettent des minutes entières, voire des heures à s'exécuter, tant elles ont d'opérations à effectuer! Dans ce cas là, on a tout intérêt à optimiser le code, et disposer les instructions au bon endroit ! Une banale optimisation peut réduire le temps d'exécution d'une macro d'une heure à 10 minutes par exemple..

Les programmeurs aiment ce principe, et l'appliquent même pour de petites macros, comme celle-ci, même si le gain de temps d'un centième de seconde arrache un sourire... C'est juste une habitude de travail.

Lignes des totaux

Occupons-nous à présent de la ligne du total. On pourrait évidemment le faire à la main, puisque c'est une opération à faire une seule fois, mais tant qu'à programmer, autant le faire jusqu'au bout.

Commençons par écrire "TOTAL :" dans la cellule B15. Ou plus exactement dans B... le nombre de lignes du tableau + 1 :

Appliquons un arrière-plan jaune pâle (comme la ligne de titre) dans les cellules A15..C15 : (pas 15 non plus, donc, mais la fin du tableau + 1)

Ouh là là ! Que c'est compliqué tous ces ActiveSheet.UsedRange.Rows.Count ! Utilisons plutôt une variable :

...
Wend
Columns("A:A").Replace ".xlsx", ""
LigneTotal = ActiveSheet.UsedRange.Rows.Count + 1
Range("B" & LigneTotal) = "TOTAL :"
Range("A" & LigneTotal & ":C" & LigneTotal).Interior.Color = 13434879 '(Jaune pâle) End Sub

C'est plus simple à lire.

A propos de variable, nous utilisons le code-couleur jaune-pâle 13434879 ici, et, également au début du code pour la ligne de titre... mais nous pourrions élégamment remplacer ces deux chiffres par une variable, comme ceci :

N'est-ce pas élégant ?

Mettons encore la ligne de total en Gras :

Range("A" & LigneTotal & ":C" & LigneTotal).Font.Bold = True

La ligne de total est presque terminée. Il ne reste plus qu'à y insérer la somme.

FormulaLocal

Nous allons insérer la formule qui va contenir la somme de tous les objets de la colonne C  (Somme(C2: C Le nombre de lignes du tableau).

Nous allons utiliser la propriété FormulaLocal. Pourquoi pas simplement "Formula" ? Qu'est-ce que ce "Local" ?

Excel est traduit en une multitudes de langues. "Somme" se traduit différemment en allemand, en italien, en espagnol... A la base, c'est "Sum" (en anglais, donc), mais comme nous allons écrire la formule en français, nous utilisons FormulaLocal.

Il faut écrire =Somme(C2:C14) dans la cellule C15. Si nous connaissions le nombre de lignes, on écrirait :

Mais comme on ne la connait, pas, nous allons donc aussi rendre cette ligne dynamique :

Ajustement des colonnes

Nous touchons au but ! Il ne reste plus qu'à adapter le contenu des colonnes... C'est à dire, par programmation, sélectionner toutes les colonnes, et double-cliquer entre deux colonnes quelconques. Comment ? Vous ne connaissiez pas cette astuce ? ... Essayez : Sélectionnez tout le tableau Excel en cliquant sur le carré avec le petit triangle, a gauche de la colonne A, et au dessus du chiffre 1, et double-cliquez entre deux colonnes, n'importe lesquelles.

De cette apparence : , passons à celle-ci :

Comme ceci :

Cells.EntireColumn.AutoFit

Et, tout à la fin, sélectionnons la cellule A1, histoire d'éviter d'avoir ces 4 cellules sélectionnées à la fin :

Voilà ! Ca y est ! C'est fait ! C'est terminé ! C'est fini ! Ca marche ! Ca maaaaaaaaaaaaaaaaaaaaaaaaaaaaaaarche !

Madame, Mademoiselle, Monsieur, votre serviteur a le plaisir, la fierté et l'avantage de vous présenter le code complet et fonctionnel de ce didacticiel :

Sub CreationSynthese() ' Initialisation
' --------------
Cells.Delete
Range("A1") = "Région"
Range("B1") = "Immeuble"
Range("C1") = "Nombre"
JaunePale = 13434879
Range("A1:C1").Interior.Color = JaunePale
Range("A1:C1").Font.Bold = True

' Parcours de tous les fichiers
' -----------------------------
ChDir "F:\Atelier\VBA Excel"
ClasseurRegional = Dir("F:\Atelier\VBA Excel\*.xlsx")
While Len(ClasseurRegional) > 0 Workbooks.Open ClasseurRegional
AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1
Range("A2:B" & AvantDerniereLigne).Copy
Workbooks("Recap.xlsm").Activate
DebutNomFichier = ActiveSheet.UsedRange.Rows.Count + 1
Range("B" & ActiveSheet.UsedRange.Rows.Count + 1).Select
ActiveSheet.Paste
Range("A" & DebutNomFichier & ":A" & ActiveSheet.UsedRange.Rows.Count) = ClasseurRegional
Workbooks(ClasseurRegional).Close
ClasseurRegional = Dir Wend

' Fin des travaux
' ---------------
Columns("A:A").Replace ".xlsx", ""
LigneTotal = ActiveSheet.UsedRange.Rows.Count + 1
Range("B" & LigneTotal) = "TOTAL :"
Range("A" & LigneTotal & ":C" & LigneTotal).Interior.Color = JaunePale
Range("A" & LigneTotal & ":C" & LigneTotal).Font.Bold = True
Range("C" & LigneTotal).FormulaLocal = "=Somme(C2:C" & LigneTotal - 1 & ")"
Cells.EntireColumn.AutoFit
Range("A1").Select End Sub

Ah, vous en avez vu, des choses, dans ce didacticiel ! Toutes ces notions sont souvent utilisées en VBA, mais elles échappent parfois aux non-programmeurs de formation. Ces histoires de boucles, de variables, de concaténation, de taille dynamique des tableaux sont des notions dont on entend parler sans arrêt, mais qu'il faut comprendre une fois pour toutes, afin de pouvoir aller plus loin dans les méandres de VBA, et avoir le plaisir d'engendrer des macros complexes et efficaces.

Bon, d'accord, on pourrait ajouter tout plein de fonctionnalités à cette macro :

• Mieux commenter chaque ligne de code : ça, vous pouvez le faire vous-même... Afin que dans 6 mois, lorsque vous revisitez votre macro, vous la compreniez aisément, ligne par ligne, sans avoir besoin  de vous casser la tête à vous rappeler pourquoi vous avez fait telle chose comme ci plutôt que comme ça... Eh oui ! Sinon, le simple fait de changer la couleur de fond de la ligne de titre risque d'être un calvaire...
• Lui assigner un raccourci-clavier pour l'exécuter aisément. Actuellement, vous devez utiliser le raccourci clavier Alt-F8 dans Excel (pas dans VBA), et choisir "CreationSynthese", puis "exécuter"
• ... Et/ou : créer un bouton sur la feuille de calcul, voire dans le ruban pour l'appeler facilement
• ... Ou même : faire en sorte de démarrer cette macro à chaque ouverture du classeur Recap.xlsm
• Ajouter des instructions pour ne pas voir la feuille "clignoter" quand on fait les opérations copier-coller en chaîne
• Installer des sous-totaux à chaque changement de ville
• Effectuer automatiquement une sauvegarde du classeur de récapitulation lorsqu'on relance la macro une nouvelle fois, plutôt qu'écraser chaque fois les données
• Créer automatiquement un tableau croisé dynamique, ou un graphique, à partir des données recueillies
• Envoyer le classeur Recap.xlsm à toutes les filiales par e-mail
• Demander dynamiquement à l'utilisateur ou se trouvent les fichiers plutôt que de le coder "en dur" (F:\Atelier\VBA Excel)
• Colorer une ligne sur deux en teinte différente pour mieux lire le fichier Recap.xlsm
• Installer une mise en forme conditionnelle, pour mettre en exergue les chiffres les plus hauts, ou plus bas (Comment ? Vous ne connaissez pas la mise en forme conditionnelle ? Tss tss !)
• Etc.

Mais tout ceci déborde laaaaargement ce didacticiel, qui n'avait d'autre ambition que de vous rendre familières les instructions VBA de synthèse de plusieurs classeurs.

Allez, vous avez bien mérité un Nespresso. What else ?