Compiler plusieurs fichiers Excel en un clic !

Compiler plusieurs fichier Excel en un clic avec VBA.

Vous être Chargé de Programme dans un Organisme Humanitaire, Responsable Suivi et Évaluation, Marketeur, Manager d'équipes commerciales, Chargé d'Etudes, Comptable, Responsable des Ressources Humaines, employé dans une banque, une entreprise d'assurance, ...

Il vous arrive de recevoir des données de même nature de vos différents collaborateurs, à partir desquelles vous devrez faire des rapports. Si ce ne sont que 2 rapports vous recevez, il n'y a pas de difficulté majeure. Mais, s'il vous arrive de recevoir 25 fichiers par exemple, ou même 100 fichiers, la tâche devient plus difficile. Allez-vous les ouvrir l'un après l'autre, les copier les uns après les autres, pour en faire un rapport synthèse?
C'est une possibilité. Mais laissez-moi vous dire qu'Excel est trop généreux pour ne pas avoir prévu une solution plus rapide

« Dtrade » est une entreprise commerciale spécialisée dans la vente des produits d’élevage tels que les poulets, les pintades, les dindons, les œufs dans plusieurs villes de la Côte d’Ivoire. Chaque PDV possède un certain nombre de produits qui ne sont pas forcément les mêmes d’un PDV à un autre. Chaque responsable de PDV tient une feuille de calcul Excel à jour, concernant les produits à vendre dans sa localité. A la fin de chaque journée tous les responsables expédient leurs bilans commerciaux par e-mail au Responsable National de vente de l’entreprise basé à Abidjan. Celui-ci est chargé de compiler ces données et d’en faire des analyses statistiques pour extraire des informations géomarketing nécessaires à l’amélioration de la politique de vente.

Pour un souci de faciliter la compréhension, le projet sera présenté avec seulement l’exemple de six villes à savoir Abidjan, San-Pédro, Daloa, Bouaké, Man et Yamoussoukro et de quelques produits. Les tableaux ci-dessous représentent les données de ces six villes pour le 07 mars 2012

a. Classeur d'Abidjan

b. Classeur de Bouaké

Les autres classeurs ont les mêmes colonnes. Il s'agit des classeurs de San-Pédro, Daloa, Man et Yamoussoukro.

Pour la suite, je vous invite à créer ces différents classeurs Excel. Ainsi, vous pourrez pratiquez en même temps que je vous lisez cet article.

Le Responsable des ventes de la structure souhaite dans un premier temps, avant son analyse, compiler les données dans un fichier nommé synthèse comme suit :

Normalement, pour avoir une telle synthèse, il suffit d'ouvrir chacun des fichiers des points de ventes, et de copier-coller les données l'une en dessous de l'autre dans le fichier de synthèse. Difficile, n'est-ce pas?

Donc, une solution optimale serait de recourir à un outil de programmation, comme par exemple VBA. On devrait pouvoir écrire un code qui puisse permettre d’ouvrir automatiquement chaque fichier pour ensuite copier et coller les données.

Il faut commencer par créer un dossier (nommé Projet) sur le disque C. Ensuite, enregistrer les différents fichiers (Abidjan, Bouaké, ...) dans le dossier Projet. Il faut savoir que le dossier pourrait être créé n’importe où. Dans le cas où l’utilisateur souhaite travailler dans un autre répertoire, il devra adapter le reste du projet avec l’adresse de ce répertoire.

1. Création du fichier de synthèse

Créer un fichier Excel dans le dossier qu’il faut enregistrer avec l’extension (.xlm). Ce type de classeur permet de prendre en compte les macros. On obtient donc le nom suivant Synthese.xlm.

2. Environnement VBA.

Il suffit de combiner le raccourci clavier « alt+f11 » ou cliquer sur l’icône Visual Basic. Ceci ouvre l’environnement de programmation VBA.

3. Création de module.

Dans l’espace VBA, cliquer sur « Insertion » puis sur « Module ». Il se crée ainsi un nouveau module, comme ceci. En gros, c’est juste la feuille dans laquelle on écrit les codes (programmation).

4. Préparation du fichier synthèse

D’abord, il faut écrire les titres du tableau. Cela devrait donner la figure ci-dessous.

Mais attention ! Si on procède ainsi, on ne pourra pas à la fin de la synthèse faire la distinction des données des différentes villes. Il faudra donc préparer le tableau comme suit :

Pour faire cela avec un programme VBA, plus précisément une macro, il faut se positionner dans le module inséré précédemment. A noter qu’une macro commence par « Sub » et se termine par « End Sub ».

En réalité, il suffit de taper Sub « Nom_de_la_macro » puis taper la Touche « Entrer ». Notre cas, on appellera la macro « Titre ». On obtient donc

Sub Titre()

End Sub

Comme sur ci-dessous :

On tape la syntaxe comme ci-dessous :

Sub Titre ()

Range("A1") = "Villes"

Range("B1") = "Produits"

Range("C1") = "Quantités vendues"

Range("D1") = "Prix unitaire"

Range("E1") = "Ventes"

End Sub

Cette syntaxe donne l’instruction à Excel d’affecter les différentes valeurs aux cellules A1, B1, C1, … Range("A1") désignant la cellule A1. On pourrait également utiliser la syntaxe cells(1,1) au lieu de Range("A1"), cells(1,2) au lieu de Range("B1"), cells(1,3) au lieu de Range("C1"), …

Une fois la macro écrite, on passe à l’exécution. Pour cela, se placer dans le corps de la macro et appuyer sur « F5 » ou cliquer sur le symbole « Play »

Dans un souci d’efficacité, il est plus rapide de taper directement les titres dans le classeur et faire les mises en forme nécessaires. L’utilisation des programmes VBA serait réservé aux aspects plus complexes. Pour cela, il faut donc effacer le code précédemment saisi.

5. Agréger les données

A partir de maintenant tout le travail sera programmé. Le code VBA devra permettre de réaliser les étapes résumées ci-dessous :

Etape 1 : Se positionner dans notre dossier de travail            Etape 2 : Ouvrier le 1er classeur            Etape 3 : Copier les données du 1er classeur sans les titres (donc à partir de la ligne 2)            Etape 4 : Coller ces données dans le classeur « Synthese »            Etape 5 : Fermer le 1er classeur            Etape 6 : Ouvrir le 2e classeur            Etape 7 : Copier les données du 2e classeur sans les titres (donc à partir de la ligne 2)            Etape 8 : Coller ces données à la fin des données précédemment collées, pour ne pas les écraser            … Ainsi de suite, jusqu’au dernier classeur

a)      Comment ouvrir/fermer un classeur ?

·         Le code d'ouverture d'un fichier est le suivant : Workbooks.Open "C:\Projet\Abidjan.xlsx"

·         Le code de fermeture est le suivant : Workbooks("Abidjan.xlsx").Close

Il faut donc aller dans le Module précédemment créé et créer une macro nommée « Synthese » et écrit le code comme suit :

Notre Code

Sub Synthese() Workbooks.Open "C:\Projet\Abidjan.xlsx" Workbooks("Abidjan.xlsx").Close End sub

Il ne faut pas oublier de tester le code en l’exécutant.

Une notion à savoir : les commentaires. Ils permettent de pouvoir se retrouver plus tard lorsqu’il sera question d’améliorer le programme. Pour ajouter les commentaires, il suffit de taper le symbole (‘) obtenu à partir de la touche 4 du clavier. Avec les commentaires, le code devient :

Notre Code

Sub Synthese() ‘Ouvrir le classeur Abidjan Workbooks.Open "C:\Projet\Abidjan.xlsx" ‘Fermer le classeur Abidjan Workbooks("Abidjan.xlsx").Close End sub

b)      Copier-coller les données

L’objectif est de recopier les données du classeur « Abidjan » vers le classeur « synthese ». Le classeur « Abidjan » se présente comme suit :

Les titres ayant déjà été ajoutés dans le classeur « Synthese », il suffit de copier les données du classeur « Abidjan » à partir de la cellule « A2 » jusqu’à la cellule « D8 ». En d’autres termes, la plage de cellule « A2:D8 ». Pour rappel, toutes les données des différents classeurs se trouvent sur la feuille « Feuil1 ».

4 lignes de codes permettent de réaliser cette action. Il est recommandé de tester d’abord le code dans une macro distincte avant de l’ajouter au code « Synthese ».

Sub Copier_Coller

Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2:D8").Copy

Workbooks("Synthese.xlsm").Activate

Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B2").Select

Workbooks("Synthese.xlsm").Sheets("Feuil1").Paste

End Sub

Une lecture attentive du code permet de le comprendre aisément. En effet, tout comme cela se fait graphiquement, après avoir copier les données, on active le classeur « Synthese », on va sur la feuille « Feuil1 », ensuite on se positionne dans la cellule « B2 » à partir de laquelle on veut coller les données.

Voici la macro actuelle :

Notre Code

Sub Synthese() ‘Ouvrir le classeur Abidjan Workbooks.Open "C:\Projet\Abidjan.xlsx" ‘Copier coller les données Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2:D8").Copy Workbooks("Synthese.xlsm").Activate Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B2").Select Workbooks("Synthese.xlsm").Sheets("Feuil1").Paste ‘Fermer le classeur Abidjan Workbooks("Abidjan.xlsx").Close End sub

Il a été facile de copier les données du classeur « Abidjan », mais la plage copiée est bien une référence fixe (A2:E8). Or, comme il a été dit plus haut que les différents PDV n’ont pas forcément les mêmes nombres de lignes. Il faut donc pouvoir dire à Excel de copier, pour chaque classeur, la plage de cellule qui contint des données. Ceci est possible avec l’outil « UsedRange ».

a)      Copier les données à l’aide de l’outil UsedRange

La propriété UsedRange permettra de cibler la plage de cellule réellement utilisée :

·         UsedRange.select : Sélectionner la plage de cellule réellement utilisée

·         UsedRange.copy : Copier la plage de cellule réellement utilisée

·         UsedRange.Rows.Count : Sélectionner la plage de cellule réellement utilisée

·         UsedRange.Column.Count : Sélectionner la plage de cellule réellement utilisée

Un test avec l’une des propriétés de UsedRange peut être fait avec une novelle macro qu’on nommera « Plage_Reellement_Utilisee »

Sub Plage_Reellement_Utilisee()

Workbooks.Open "C:\Projet\Abidjan.xlsx"

ActiveSheet.UsedRange.Select

End Sub

On obtient le résultat ci-après :

Il faudra apporter quelques corrections, car la ligne des titres et celle du total ne devraient pas être sélectionnées.

Ici il faudra sélectionner la plage allant de la cellule « A2 » à l’avant dernière cellule du tableau de la colonne D’où l’introduction de la propriété « UsedRange.Rows.Count » :

Sub Test3()

Workbooks.Open "C:\Projet\Abidjan.xlsx"

Range("A2:D"&ActiveSheet.UsedRange.Rows.Count - 1).Select

End Sub

Le symbole « & » permet de concaténer la lettre « D » et le nombre renvoyé par la propriété « ActiveSheet.UsedRange.Rows.Count ».

Pour améliorer le programme, on va désigner une variable nommée AvantDerniereLigne qui va récupérer le nombre renvoyé par ActiveSheet.UsedRange.Rows.Count – 1. Cette variable sera réutilisée dans le reste du programme.

Il faudra maintenant renseigner la colonne A. ceci est possible avec le code ci-après

Range("A2:A"&AvantDerniereLigne)="Abidjan"

Le code s’allonge progressivement ; il devient :

Notre Code

Sub Synthese() ‘Ouvrir le classeur Abidjan Workbooks.Open "C:\Projet\Abidjan.xlsx" ‘Copier-coller les données AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count-1 Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2: B"&AvantDerniereLigne).Copy Workbooks("Synthese.xlsm").Activate Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B2").Select Workbooks("Synthese.xlsm").Sheets("Feuil1").Paste Range("A2:A"&AvantDerniereLigne)="Abidjan" ‘Fermer le classeur Abidjan Workbooks("Abidjan.xlsx").Close End sub

a)      Données du PDV suivant

Supposons que le PDV suivant est « Bouaké ». Il faudra repérer la ligne du classeur « Synthese » à partir de laquelle, les données de la ville « Bouaké » devront être collées. Encore, il faudra utiliser la propriété « UsedRange ». Plus précisément à partir de la cellule "B"&ActiveSheet.UsedRange.Rows.Count + 1, c'est-à-dire la ligne juste après la dernière ligne utilisée.

Ensuite, il faudra écrire « Bouaké » dans la colonne A. mais il faut retenir qu’une fois les données de Bouaké collées, ActiveSheet.UsedRange.Rows.Count ne renvoie plus la ligne à partir de laquelle les de Bouaké ont été collées. Mais plutôt la « nouvelle dernière ligne ».

On peut contourner cette difficulté en créant une variable qui va mémoriser la valeur contenue dans ActiveSheet.UsedRange.Rows.Count avant de copier le contenu du classeur « Bouaké ».

Le code continue de s’allonger. Après ajout de quelques commentaires, voici à quoi il ressemble maintenant :

Notre Code

Sub Synthese() ‘I/ OUVRIR LE CLASSEUR ABIDJAN Workbooks.Open "C:\Projet\Abidjan.xlsx" ‘II/ COPIER-COLLER LES DONNÉES D’ABIDJAN AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count-1 Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2: B"&AvantDerniereLigne).Copy Workbooks("Synthese.xlsm").Activate Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B2").Select Workbooks("Synthese.xlsm").Sheets("Feuil1").Paste Range("A2:A"&AvantDerniereLigne)="Abidjan" ‘Fermer le classeur Abidjan Workbooks("Abidjan.xlsx").Close ‘III/ COPIER-COLLER LES DONNEES DE BOUAKE Workbooks.Open "C:\Projet\Bouaké.xlsx" AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count– 1 Workbooks("Bouaké.xlsx").Sheets("Feuil1").Range("A2:B" &AvantDerniereLigne).Copy Workbooks("Synthese.xlsm").Activate ‘variable qui va mémoriser la valeur contenue dans …. DebutPlage = ActiveSheet.UsedRange.Rows.Count + 1 Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B"&ActiveSheet.UsedRange.Rows.Count + 1).Select Workbooks("Synthese.xlsm").Sheets("Feuil1").Paste ActiveSheet.Range("A" &DebutPlage& ":A" &ActiveSheet.UsedRange.Rows.Count) = "Bouaké" Workbooks("Bouaké.xlsx").Close End sub

b)      Simplifier le code

Sub Simplifier_Code()

Workbooks.Open "C:\Projet\Abidjan.xlsx"

AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count-1

Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2: B"&AvantDerniereLigne).Copy

End Sub

Ici l’instruction

Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2: B"&AvantDerniereLigne).Copy (1)

le code précédant dit de copier une plage de cellule de la feuille 1 du classeur « Abidjan » qui en réalité est le classeur courant et la feuille courante. C’est-à-dire le classeur actif, puisque l’une des instructions précédentes est Workbooks.Open "C:\Projet\Abidjan.xlsx"" On vient d’ouvrir le classeur « Abidjan », on est donc dans le classeur « Abidjan » et sur la feuille par défaut. Du coup, c’est redondant de le préciser à Excel. On peut donc simplifier le code (1) et obtenir : Range("A2: B"&AvantDerniereLigne).Copy

Les éléments surlignés en jaune peuvent être retirés du code :

Notre Code

Sub Synthese() ‘I/ OUVRIR LE CLASSEUR ABIDJAN Workbooks.Open "C:\Projet\Abidjan.xlsx" ‘II/ COPIER-COLLER LES DONNÉES D’ABIDJAN AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count-1 Workbooks("Abidjan.xlsx").Sheets("Feuil1").Range("A2: B"&AvantDerniereLigne).Copy Workbooks("Synthese.xlsm").Activate Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B2").Select Workbooks("Synthese.xlsm").ActiveSheets("Feuil1").Paste Range("A2:A"&AvantDerniereLigne)="Abidjan" ‘Fermer le classeur Abidjan Workbooks("Abidjan.xlsx").Close ‘III/ COPIER-COLLER LES DONNEES DE BOUAKE Workbooks.Open "C:\Projet\Bouaké.xlsx" AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count– 1 Workbooks("Bouaké.xlsx").Sheets("Feuil1").Range("A2:B" &AvantDerniereLigne).Copy Workbooks("Synthese.xlsm").Activate ‘variable qui va mémoriser la valeur contenue dans …. DebutPlage = ActiveSheet.UsedRange.Rows.Count + 1 Workbooks("Synthese.xlsm").Sheets("Feuil1").Range("B"&ActiveSheet.UsedRange.Rows.Count + 1).Select Workbooks("Synthese.xlsm").ActiveSheets("Feuil1").Paste ActiveSheet.Range("A" &DebutPlage& ":A" &ActiveSheet.UsedRange.Rows.Count) = "Bouaké" Workbooks("Bouaké.xlsx").Close End sub

A ce niveau, le code pour Abidjan et pour Bouaké n’est pas tout à fait le même. Ne pourrait-on pas remédier à cela ?

c)       Uniformiser le code

En remplaçant le code de Bouaké par celui d’Abidjan, on obtient le code suivant :

Workbooks.open "c:\Projet\abidjan.xlsx"

AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count - 1

Range("A2:B"&AvantDerniereLigne ).copy

Workbooks("Synthese.xlsm").Activate

DebutPlage = ActiveSheet.UsedRange.Rows.Count + 1

Range("B"&DebutPlage).Select

Activesheet.Paste

Range("A"&DebutPlage& ":A" &ActiveSheet.UsedRange.Rows.Count) = "Abidjan"

Workbooks("abidjan.xlsx").Close

Lorsqu’on observe de près les valeurs que prennent les variables « DebutPlage » et « AvantDerniereLigne » dans ce code, on comprend aisément qu’il est identique au précédent.

Ce code pourra donc permettre de copier-coller les données de toutes les autres villes du dossier (Daloa, Man, San-Pedro, Yamoussoukro). Surtout qu’en réalité, il y a 150 PDV. L’idéal serait que VBA parcourt tout le dossier et qu’il fasse le même traitement pour toutes les villes. On va donc utiliser une syntaxe : la boucle.

Cette notion de boucle a été abordée dans la partie théorique de ce document. Ici il s’agira de l’appliquer. Mais avant, il va falloir aborder certaines notions : « dir » et « len »

d)      Instruction dir

« dir » permet de récupérer le nom d’un fichier dans un dossier spécifié. Par exemple le code dir("C:\Projet\") permet de récupérer le nom du premier fichier du dossier « Projet » qui se trouve sur le Disque C: En fait le dossier dans lequel on travail. Ainsi, le code ci-dessous permet d’afficher à l’aide d’une boîte de message le nom récupéré :

Sub Premier_Fichier()

Msgbox dir("c:\Projet\")

End sub

Il va afficher :

Pour lister le premier fichier d’extension «xlsx », il suffit écrire msgbox dir("c:\Projet\*.xlsx")

a)      Fonction de taille de texte : len

Len (de l’Anglais length qui veut dire longueur), permet de connaitre le nombre de caractères d'un texte, comme ceci :

Sub Longueur()

Msgbox len("diarrahervé") 'affiche : 12

End sub

Il s’agit maintenant de combiner les fonctions dir, & et len. L’exemple qui suit permet d’afficher le nom du premier classeur du dossier, avec le nombre de caractères qu'il contient.

Sub Nom_et_Longueur()

NomDuFichier = dir("c:\Projet\*.xlsx")

Msgbox NomDuFichier&" contient " &len(NomDuFichier) &" caractères"

End Sub

Si le dossier ne contient pas de fichier Excel, Msgbox affiche Contient 0 caractère.

b)      Boucler sur tous les fichiers jusqu'au fichier vide

L’idée est de lire les fichiers d’extension .xlsx tant que la longueur de leur caractère est plus grand que 0, en d’autres termes tous les fichiers d’extension.

Sub Nom_Fichier_Boucle()

NomDuFichier=dir("c:\Projet\*.xlsx") ' C’est-à-dire Abidjan.xlsx

While len(NomDuFichier) > 0 'la longueur de abidjan.xlsx est de 13 caractères, donc 13 > 0...

Msgbox NomDuFichier 'on affiche abidjan.xlsx

NomDuFichier = dir 'On lit le fichier suivant, c’est-à-dire Bouake.xlsx

Wend 'comme la longueur de Bouake.xlsx est > 0, on recommence, et ainsi de suite

End Sub

Msgbox va afficher successivement les noms des fichiers Excel contenus dans le dossier jusqu’à la fin.

Le code peut maintenant s’écrire comme suit :

Notre Code

Sub Synthese() Nom_Classeur = dir("c:\Projet\*.xlsx") ‘On crée une variable Nom_Classeur While len(Nom_Classeur) > 0 Workbooks.open Nom_Classeur AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count – 1 Range("A2: E"&AvantDerniereLigne ).copy Workbooks("Synthese.xlsm").Activate DebutPlage = ActiveSheet.UsedRange.Rows.Count + 1 Range("B"&DebutPlage).Select ActiveSheet.Paste Range("A"&DebutPlage&":A"& ActiveSheet.UsedRange.Rows.Count) = Nom_Classeur ‘Ici les noms des villes seront suivis de .xlsx. Ceci sera corrigé plus tard Workbooks(Nom_Classeur).Close Nom_Classeur = dir Wend End sub

Il va falloir maintenant retirer l’extension « .xlsx » sur les noms des villes. Car dans l’instruction Range("A"&DebutPlage&":A"&ActiveSheet.UsedRange.Rows.Count)=Nom_Classeur, « Nom_Classeur » prend successivement les valeurs Abidjan.xlsx, Bouaké.xlsx, …

Il faut donc rechercher ".xlsx" dans la colonne A et les remplacer par ... rien (en fait : ""). La syntaxe est la suivante :

Columns("a:a").replace ".xlsx", ""

Cette syntaxe devra être placée hors de la boucle. Le code final est donc le suivant :

Code final

Sub Synthese() Nom_Classeur = dir("c:\Projet\*.xlsx") ‘On crée une variable Nom_Classeur While len(Nom_Classeur) > 0 Workbooks.open Nom_Classeur AvantDerniereLigne = ActiveSheet.UsedRange.Rows.Count – 1 Range("A2: E"&AvantDerniereLigne ).copy Workbooks("Synthese.xlsm").Activate DebutPlage = ActiveSheet.UsedRange.Rows.Count + 1 Range("B"&DebutPlage).Select ActiveSheet.Paste Range("A"&DebutPlage&":A"& ActiveSheet.UsedRange.Rows.Count) = Nom_Classeur Workbooks(Nom_Classeur).Close Nom_Classeur = dir Wend Columns("a:a").replace ".xlsx", "" End sub

En dernier point, il reste à mettre un bouton pour permettre d’exécuter le code VBA, comme présenté ci-dessous :

Il s’agit juste d’insérer une forme, ajouter le libellé (Compiler les données). Faire un clic droit sur la forme, puis cliquer « Affecter une macro… » et sur « Nouvelle ». Ceci permet d’ouvrir le module. Juste écrire le nom du code c’est-à-dire « Synthese ». Enregistrer et fermer.

Pour synthétiser automatiquement les données, il faudra donc cliquer sur le bouton qui va permettre d’exécuter la macro (Synthese). Intéressant non ?