Comment fusionner plusieurs classeurs sur Excel en 1 clic ?
Aujourd’hui, je vous propose de découvrir comment effectuer l’opération inverse, c’est-à-dire que nous allons souhaiter regrouper toutes les feuilles de calculs contenues dans un dossier au sein d’un seul et même classeur.
Comme pour le tutoriel précédent, nous allons utiliser deux méthodes : la fusion manuelle et la fusion automatisée avec VBA.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Fusion manuelle des classeurs
1.1. Méthode de fusion manuelle
Pour fusionner plusieurs classeurs manuellement, nous devons suivre les étapes suivantes :
- Ouvrez un nouveau classeur Excel dans lequel vous allez fusionner les données.
- Ouvrez les classeurs Excel que vous souhaitez fusionner.
- Ensuite, cliquez sur l'onglet de la feuille que vous souhaitez copier et faites un clic droit sur l'onglet. Cliquez sur "Déplacer ou copier".
- Dans la fenêtre qui s'ouvre, sélectionnez le nouveau classeur et cochez la case "Créer une copie". Cliquez sur "OK" pour copier la feuille dans le nouveau classeur. Répétez ces étapes pour chaque feuille que vous souhaitez fusionner.
- Renommez les feuilles copiées en fonction de vos préférences.
- Enregistrez le nouveau classeur contenant toutes les feuilles fusionnées. Il est important de sauvegarder les modifications régulièrement pour éviter de perdre des données importantes.
1.2. Avantages / Inconvénients de la méthode
La fusion manuelle est une méthode simple et rapide, mais elle peut être fastidieuse si vous devez fusionner un grand nombre de feuilles ou de classeurs.
Elle est également sujette aux erreurs humaines, telles que la sélection de feuilles incorrectes ou la copie de données incorrectes.
Cependant, elle ne nécessite pas de connaissances en programmation et peut être effectuée par n'importe qui ayant une compréhension de base d'Excel.
2. Fusion automatisée des classeurs avec VBA
2.1. Qu'est-ce qu'une macro VBA ?
Comme nous l’avons vu un peu plus tôt, pour faciliter et fiabiliser l’opération de fusion, nous allons souhaiter passer par une macro commande en VBA.
Si vous avez l’habitude de suivre les tutoriels de la chaîne, vous devez savoir ce qu’est une macro VBA.
Sinon, sachez qu’il s’agit de saisir des lignes de commande dans un langage de programmation nommé Visual Basic for Applications (VBA), afin de pouvoir automatiser des tâches répétitives dans Excel. En utilisant une macro VBA pour fusionner des classeurs, nous pouvons éviter la tâche fastidieuse de sélectionner manuellement les feuilles à fusionner. Comme vous allez pouvoir vous en rendre compte dans la suite de ce tutoriel, le langage de programmation VBA permet de démultiplier les possibilités offertes par Excel. Pour que vous puissiez tous suivre ce tutoriel, je vais vous expliquer chacune des lignes que nous allons utiliser dans cette macro, mais si vous souhaitez aller plus loin dans la découverte de ce langage, vous pouvez cliquer ici pour découvrir mon livre dédié au langage VBA.
2.2. Comment créer une macro VBA ?
Pour créer une macro VBA, nous devons d'abord ouvrir l'éditeur de macros en appuyant sur [Alt]+[F11].
Ensuite, nous devons créer un nouveau module dans lequel nous pouvons écrire le code de la macro.
Un module est en effet une feuille dans laquelle nous allons pouvoir saisir les lignes de code de la macro.
2.2. Création de la macro fusionnerClasseurs()
Pour commencer, nous allons créer la macro qui va nous permettre d’automatiser la fusion des feuilles de tous les classeurs contenus dans un dossier.
Pour simplifier celle-ci, nous allons considérer qu’il s’agit de classeurs contenant une feuille de calcul unique reprenant les ventes réalisées par une entreprise dans différents pays.
Pour créer une macro, nous utilisons le mot-clé « Sub », suivi du nom de la macro, ici « fusionClasseurs ».
Sub fusionClasseurs() End Sub
Lorsque nous validons en appuyant sur la touche [Entrée], VBE va créer la macro en ajoutant les parenthèses et la ligne « End Sub ».
Toutes les lignes que nous allons saisir entre ces lignes seront exécutées à chaque fois que nous appellerons la macro « fusionClasseurs ».
Ensuite, nous allons commencer par déclarer une première variable nommée "nomDossier" que nous typons en tant que chaîne de caractères et qui va nous permettre d’enregistrer le nom du dossier dans lequel se trouvent les fichiers à fusionner :
Dim nomDossier As String
Nous allons maintenant pouvoir demander à l’utilisateur de sélectionner le dossier dans lequel se trouvent les fichiers, grâce à la méthode « Application.FileDialog » qui permet d’afficher une boîte de dialogue de sélection de dossier :
Application.FileDialog(msoFileDialogFolderPicker)
Cette ligne crée une instance de la boîte de dialogue « FileDialog » avec le type de dialogue « msoFileDialogFolderPicker » qui permet à l'utilisateur de sélectionner un dossier plutôt qu'un fichier.
Nous ajoutons l’instruction « With » juste avant afin de simplifier l'accès aux propriétés et méthodes de l'objet généré.
En effet, grâce à cette instruction, nous allons générer un bloc de code dans lequel nous pourrons accéder aux propriétés et méthodes de cet objet sans avoir à répéter son nom à chaque fois.
Cela rend le code plus lisible et concis.
With Application.FileDialog(msoFileDialogFolderPicker) End With
À l’intérieur de ce bloc, nous allons pouvoir effectuer un test sur la méthode « Show » de cet objet, en utilisant une instruction « If », laquelle permet en effet d'exécuter du code en fonction de la valeur d'une condition.
L'instruction If exécute un bloc de code si la condition est vraie et peut exécuter un autre bloc de code si la condition est fausse.
Ici, nous allons regarder si l'utilisateur à bien sélectionné un dossier et appuyé sur [OK].
Lorsque ce sera le cas, la méthode « Show » nous renverra alors la valeur « -1 »
If .Show = -1 Then End If
À ce moment-là, nous pourrons stocker le chemin de ce dossier en accédant à la première (et unique) valeur de la collection « SelectedItems ».
Nous ajouterons ensuite un caractère de séparation de dossier (« \ ») à la fin du chemin pour faciliter la concaténation ultérieure avec les noms de fichiers.
nomDossier = .SelectedItems(1) & "\"
En revanche, si l'utilisateur n'a pas sélectionné de dossier (par exemple, en appuyant sur "Annuler" dans la boîte de dialogue), nous pourrons alors nous positionner dans la condition « Else » du test « If », afin d’appeler l'instruction « Exit Sub » pour terminer immédiatement l'exécution de la macro.
Cela signifie que si l'utilisateur n'a pas sélectionné de dossier, la macro se termine sans effectuer d'autres actions.
Else
Exit Sub
Maintenant que nous sommes certains que l’utilisateur a bien sélectionné un dossier pour lequel nous avons enregistré le chemin complet dans la variable « nomDossier », nous allons pouvoir créer un nouveau classeur Excel, que nous allons affecter sur la variable « nouveauClasseur » que nous allons instancier en tant qu’objet « classeur » (« Workbook »)
Dim nouveauClasseur As Workbook Set nouveauClasseur = Workbooks.Add
Puis, nous allons déclarer une nouvelle variable « classeurSource » qui va permettre de récupérer les noms de fichiers Excel (format xlsx) dans le dossier sélectionné, grâce à la fonction Dir().
Celle-ci permet en effet de rechercher un fichier dans un dossier spécifique en prenant un argument qui représente le chemin d'accès au dossier à explorer et un motif de nom de fichier optionnel qui permet de filtrer les fichiers à rechercher.
Ici, nous allons rechercher tous les fichiers ayant l'extension « .xlsx » dans le dossier sélectionné par l'utilisateur à l'aide de la boîte de dialogue « Application.FileDialog ».
Cette fonction retourne le nom du premier fichier correspondant à la recherche, puis à chaque appel suivant, elle renverra le nom du fichier suivant jusqu'à ce qu'il n'y en ait plus :
Dim classeurSource As String classeurSource = Dir(nomDossier & "*.xlsx")
Maintenant, nous allons pouvoir utiliser une boucle « Do While » pour passer en revue chacune des classeurs Excel du dossier.
Une boucle « Do While » permet en effet d'exécuter un bloc de code tant qu'une condition est vraie.
La condition à évaluer dans cette boucle (« classeurSource <> "" ») signifie "tant que le nom du fichier trouvé par la fonction Dir() n'est pas une chaîne vide".
Ainsi, tant qu'un fichier est trouvé dans le dossier, la boucle continue d'exécuter le code à l'intérieur de la boucle,
Do While classeurSource <> "" Loop
Dans cette boucle, nous allons maintenant pouvoir ouvrir le fichier Excel, copier toutes les feuilles de calcul du fichier ouvert dans le nouveau classeur Excel, puis fermer le fichier ouvert.
La boucle répète ensuite le processus en appelant à nouveau la fonction Dir() pour trouver le prochain fichier correspondant au motif, jusqu’à ce qu’il n’y ai plus aucun fichier de trouvé, c'est-à-dire lorsque la fonction Dir() renvoie une chaîne vide.
Nous déclarons donc une nouvelle variable Workbook dans laquelle nous allons souhaiter ouvrir le classeur dont le nom est retouré par « classeurSource » :
Dim classeur As Workbook Set classeur = Workbooks.Open(nomDossier & classeurSource)
Puis, nous allons boucler sur toutes les feuilles de calcul du classeur, en utilisant cette fois-ci une boucle « For Each », que nous avions découverte la semaine dernière et qui permet d'itérer sur les éléments d'une collection, tels que les éléments d'un tableau, les éléments d'une liste ou comme ce qui nous intéresse ici, les feuilles de calcul d'un classeur Excel :
Dim feuille As Worksheet For Each feuille In classeur.Sheets
Ensuite, il ne reste plus qu’à copier chaque feuille de calcul du classeur source vers le nouveau classeur :
feuille.Copy After:=nouveauClasseur.Sheets(nouveauClasseur.Sheets.Count)
Cela étant fait, nous pouvons fermer le classeur source sans enregistrer les modifications, et récupérer le nom du prochain fichier Excel dans le dossier :
classeur.Close False classeurSource = Dir
Maintenant que le classeur contient bien les feuilles fusionnées, nous pouvons l’enregistrer dans le même dossier que les classeurs sources :
nouveauClasseur.SaveAs Filename:=nomDossier & "Classeur Fusionné.xlsx", FileFormat:=xlOpenXMLWorkbook
La macro, est maintenant terminée, pour l’utiliser, il vous suffit de l'exécuter en appuyant sur la touche F5 ou en cliquant sur "Exécuter" dans l'éditeur de macros.
Nous serons alors invités à sélectionner le dossier dans lequel se trouvent les classeurs à fusionner.
Comme vous pouvez le constater, la fusion automatique à l'aide d'une macro VBA est beaucoup plus rapide et efficace que la fusion manuelle.
Elle permet également d'éviter les erreurs humaines telles que la sélection de feuilles incorrectes ou la copie de données incorrectes.