# Macros-commandes VBA/Exercices/Consolidation de classeurs
## Consolidation de classeurs Excel

Nous nous proposons ici d'effectuer un exercice complexe pour mettre en pratique vos connaissances en termes de macros et ainsi automatiser certaines tâches. Dans le cas présent, il s'agit de la consolidation de classeurs EXCEL.

## Exercice 1: Consolidation par somme de données

Enoncé:
Vous êtes une entreprise française de vente de vêtements dans le prêt-à-porter et vous possédez quatre magasins en France. Chaque mois, vous recevez de la part de vos magasins leurs résultats de ventes sous forme d’un classeur Excel (vous utilisez Excel 2010) et vous voulez consolider ces résultats dans un seul classeur afin de connaître les résultats de votre entreprise et de préparer votre compte de résultat et votre bilan. Vos magasins vous ont envoyé les résultats suivants :
Par soucis d’organisation et de praticité vous enregistrerez tous les classeurs dans le même dossier.
Consolidez ces classeurs.
Vous devez obtenir le tableau suivant :

## Exercice 2: Consolidation par regroupement de bases de données

Enoncé:
Vous êtes une entreprise de vente de fenêtres et vous employez trois commerciaux chargées de récolter les coordonnées de potentiels clients. À la fin de chacune de leur journée, vos commerciaux vous envoient les coordonnées qu’ils ont récoltées dans la journée sous la forme d’un classeur Excel. Vous voulez alors ajouter ces coordonnées à votre base de données consolidée. Aujourd’hui votre base de données contient les coordonnées suivantes :
| Nom | Prénom | Numéro de téléphone | Ville |
| Darcy | Fitzwilliam | 01 56 24 58 91 | Pemberley |
| Bennet | Elizabeth | 06 32 14 78 34 | Longbourn |
| Bingley | Caroline | 06 89 51 22 77 | Netherfield |
| Wickham | Georges | 06 47 65 85 45 | Meryton |
Vos commerciaux vous communiquent leurs bases du jour qui sont les suivantes :
Bases des commerciaux :
| Base commercial n°1 | Base commercial n°1 | Base commercial n°1 | Base commercial n°1 | Base commercial n°2 | Base commercial n°2 | Base commercial n°2 | Base commercial n°2 | Base commercial n°3 | Base commercial n°3 | Base commercial n°3 | Base commercial n°3 |
| Nom | Prénom | Numéro de téléphone | Ville | Nom | Prénom | Numéro de téléphone | Ville | Nom | Prénom | Numéro de téléphone | Ville |
| Burry | Michael | 01 45 85 23 74 | Los Angeles | Priestly | Miranda | 01 45 87 65 22 | Jersey City | McGrégor | Callum | 06 45 03 75 89 | Londres |
| Vennett | Jared | 01 89 56 43 22 | New York | Sachs | Andrea | 01 45 62 33 44 | Hempstead | Hadley | Perséphone | 06 45 47 02 01 | Londres |
| Baum | Mark | 06 11 94 56 34 | Linden | Charlton | Emily | 01 54 78 99 15 | Hoboken | | | | |
| | | | | Thompson | Christian | 01 56 09 45 41 | Paris | | | | |
Par soucis d’organisation et de praticité vous enregistrez tous les classeurs dans le même dossier et vous les nommez de la même façon avec pour seule distinction le numéro du commercial. Exemple : « Base commercial n°1 ».
Consolidez ces classeurs.
* Vous devez obtenir la base suivante:

## Exercice 3: Cherchez l'erreur

De petites erreurs se sont glissées dans les codes suivants. Serez-vous capable de les retrouver? À vous de jouer ;)

### 1) Consolidation par somme de données

Ce code doit vous permettre de produire le tableau suivant:
Alors où est(sont) l(es)'erreur(s)?
Ici le code présente deux erreurs toutes deux présentes dans la dernière ligne du code.
Nous voulons obtenir la liste des produits sur la colonne de gauche ainsi que les titres de la première ligne. Nous voulons également que notre tableau soit sous forme de synthèse c’est-à-dire que nous ne voulons pas le détail des données consolidées, nous ne voulons que la somme des données.
Or, le présent code produit un tableau qui ne rapatrie pas la colonne de gauche (LeftColumn:=False) et qui rapatrie le détail des données en créant des liens vers les fichiers que l’on a consolidés (CreateLinks:=True).
Il aurait fallu modifier ces deux conditions en:
LeftColumn:=True, CreateLinks:=True

### 2) Consolidation par regroupement de bases de données

Nous voulons consolider trois classeurs en une seule base de données. Nous voulons pour cela utiliser une boucle afin que notre code soit le plus court possible par conséquent nous avons nommé tous nos fichiers avec la même racine et les avons différenciés par des numéros allant de 1 à 3. Exemple de nom de fichier: "Coordonnées clients 1"
Alors où est(sont) l(es)'erreur(s)?
Ici le code présente quatre erreurs.
La première se trouve à la sixième ligne. On déclare la variable "extension" comme Integer c’est-à-dire un nombre alors que l’on voit par la suite qu'il s'agit d'une chaîne de caractères. Il aurait donc fallu écrire: Dim extension As String
La seconde erreur se trouve à la onzième ligne. On affecte à la variable "nbfichiers" qui sert à indiquer le nombre de classeurs à consolider le nombre de deux. cela signifie donc que l’on veut consolider deux classeurs. Or il est écrit dans l'énoncé que nous voulons consolider trois classeurs. Il aurait donc fallut écrire: nbfichiers = 3
La troisième erreur se trouve à la quatorzième ligne. Sur cette ligne on désigne le classeur à ouvrir pour le consolider en assemblant les variables "chemin", "fichier" et "extension". Cela a pour but d'indiquer à la macro quel classeur elle doit ouvrir et consolider et à quel endroit il se trouve dans notre ordinateur. Notre code nous indique donc d'ouvrir le fichier "Coordonnées clients " portant une extension "xlsx" et se trouvant sous le chemin suivant "C:\Users\Wiki\Consolidation par regroupement de bases de données\".
On remarque donc qu'il nous manque une information très importante qui est le numéro du fichier (1, 2 ou 3) sans laquelle notre macro ne peut pas trouver le classeur adéquat puisque le nom du classeur n’est pas complet. Il aurait fallut écrire: Workbooks.Open (chemin & fichier & i & extension)
Le "i" manquant permet d'autant plus de rendre cohérente l’utilisation de la boucle For. On retrouve d'ailleurs le i à la trente-quatrième ligne "Windows(fichier & i & extension).Close" lorsque l'on indique à la macro de refermer le classeur après l'avoir consolidé. Cette ligne nous donne donc un indice sur cette erreur.
La quatrième et dernière erreur se trouve la trente-cinquième ligne. Sur cette ligne on indique à la macro que nos instructions sont terminées et que l’on veut passer au tour de boucle suivant. Or on déclare ici que le prochain tour de boucle est la variable "nbfichiers" qui compte le nombre de classeurs à consolider.
C'est une erreur car l'incrémenteur de boucle n’est pas la variable "nbfichiers" mais la variable "i" que l’on incrémente de 1 à chaque tour de boucle jusqu'à atteindre la valeur de la fameuse variable "nbfichiers" après laquelle la boucle va s'arrêter. Il aurait donc fallu écrire: Next i

## Exercice 4: Consolidation de données

Voici son énoncé :
* Créer plusieurs classeurs de chiffres d'affaires (nommés par des noms de magasins différents) comprenant chacun 10 colonnes et 2 lignes
* Remplir manuellement chaque classeur magasin avec en ligne 1 une liste de 10 produits de votre choix (la même pour chaque classeur)
* Remplir manuellement chaque classeur magasin avec en ligne 2 des lignes une liste de 10 valeurs de CA de votre choix (différente pour chaque classeur)
* Créer le classeur EXCEL "consoCA.xlsm" avec en ligne 1 la même liste de produits
* Ranger les 3 classeurs EXCEL dans les dossier "c:\tests\excel" de votre ordinateur
* Remplir automatiquement via une macro le classeur "consoCA.xlsm" par les données de chacun des magasins (1 ligne par magasin)
* Pour ce faire, il faut créer le fichier consoCA.xlsm puis dans l'onglet DÉVELOPPEUR Visual Basic saisir le code VBA suivant :
* Ce code VBA
** parcourt dans un répertoire de test les fichiers un par un et exclut tous les fichiers non excel de la consolidation
** copie chaque deuxième ligne du classeur lu
** l'insère sur une ligne variable du classeur "conso.xlsm"
* Pour exécuter la macro, il vous suffit de
** vider le classeur "conso.xlsm" de ses lignes (sauf la ligne 1)
** retourner dans l'onglet DÉVELOPPEUR Macros
** de sélectionner la macro "ConsoliderFichiers"
** de l'exécuter
EXCEL fournit en standard un outil de consolidation de différentes feuilles, mais pas d'automatisme sur son exécution
Ne pas oublier l'Option Explicit en début de code et de bien nommer vos variables
| Remplacement des hyperliens | Contrôle de données |