Macros-commandes VBA/Exercices/Consolidation de classeurs

**Consolidation de classeurs**
Leçon : Macros-commandes VBA

Exercices n^o8

Exercices de niveau 14.
Exo préc. :	Remplacement des hyperliens
Exo suiv. :	Contrôle de données

En raison de limitations techniques, la typographie souhaitable du titre, « Exercice : Consolidation de classeurs
Macros-commandes VBA/Exercices/Consolidation de classeurs », n'a pu être restituée correctement ci-dessus.

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.

Solution

Sub Consolidation_magasins()

'Sélectionne la cellule de départ du tableau consolidé:
Range ("A1").Select
        
    Selection.Consolidate Sources:=Array( _
        "'E:\Wiki\magasin\[Paris.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\magasin\[Marseille.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\magasin\[Bordeaux.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\magasin\[Lille.xlsx]Feuil1'!R1C1:R1000C200", _

        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

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. A 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 :

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
Bases des commerciaux
Base commercial n°1				Base commercial n°2				Base commercial n°3
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.

Solution

Sub consolidation_regroupement()

'Stoppe l'actualisation de l'écran. Cela sert à masquer les actions de la macro
Application.ScreenUpdating = False

'Détermine le chemin d'accès aux fichiers
Dim chemin As String
Dim fichier As String
Dim extension As String

chemin = " C:\Wiki\Consolidation de bases de données\"
fichier = "Base commercial n°"
extension = ".xlsx"

'Indique le nombre de fichiers à consolider
nbfichiers = 3

   For i = 1 To nbfichiers
   
   'Ouvre le fichier à consolider
    Workbooks.Open (chemin & fichier & i & extension)
    
    'Sélectionne la feuille où se trouvent les données
    Sheets("Feuil1").Select

    'Compte le nombre de lignes à copier
    n = WorksheetFunction.CountA(Range("A:A"))
    
    'Compte le nombre de colonnes à copier
    m = ActiveSheet.UsedRange.Columns.Count
    
    'Copie les données
    Range(Cells(2, 1), Cells(n, m)).Copy
    
    'Active le classeur de synthèse
    Windows("Synthèse.xlsm").Activate
    
    'Sélectionne la feuille où on va coller les données
    Sheets("Synthèse").Select

    'Compte le nombre de lignes non vides et ajoute 1 pour avoir le numéro de la première ligne vide
    c = WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Sélectionne la première cellule vide
    Range("A" & c).Select
    
    'Colle les données
    ActiveSheet.Paste
    
    'Ferme la base de données qui a été consolidée et passe à la suivante
    Windows(fichier & i & extension).Close
    
    Next i

'Réactive l'actualisation de l'écran
Application.ScreenUpdating = True

End Sub

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? A vous de jouer ;)

1) Consolidation par somme de données

Ce code doit vous permettre de produire le tableau suivant:

Sub Consolidation_somme()

Range ("A1").Select
  
    Selection.Consolidate Sources:=Array( _
        "'E:\Wiki\Consolidation par somme de données\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\Consolidation par somme de données\[Paris.xls]Feuil1'!R1C1:R1000C200"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=False, CreateLinks:=True
        
End Sub

Alors où est(sont) l(es)'erreur(s)?

Solution

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"

Sub consolidation_regroupement()

'Stoppe l'actualisation de l'écran. Cela sert à masquer les actions de la macro
Application.ScreenUpdating = False

'Détermine le chemin d'accès aux fichiers
Dim chemin As String
Dim fichier As String
Dim extension As Integer

chemin = "C:\Users\Wiki\Consolidation par regroupement de bases de données\"
fichier = "Coordonnées clients "
extension = ".xlsx"

'Indique le nombre de fichiers à consolider
nbfichiers = 2

   For i = 1 To nbfichiers
   
   'Ouvre le fichier à consolider
    Workbooks.Open (chemin & fichier & extension)
    
    'Sélectionne la feuille où se trouvent les données
    Sheets("Feuil1").Select

    'Compte le nombre de lignes à copier
    n = WorksheetFunction.CountA(Range("A:A"))
    
    'Compte le nombre de colonnes à copier
    m = ActiveSheet.UsedRange.Columns.Count
    
    'Copie les données
    Range(Cells(2, 1), Cells(n, m)).Copy
    
    'Active le classeur de synthèse
    Windows("Synthèse.xlsm").Activate
    
    'Sélectionne la feuille où on va coller les données
    Sheets("Synthèse").Select

    'Compte le nombre de lignes non vides et ajoute 1 pour avoir le numéro de la première ligne vide
    c = WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Sélectionne la première cellule vide
    Range("A" & c).Select
    
    'Colle les données
    ActiveSheet.Paste
    
    'Ferme la base de données qui a été consolidée et passe à la suivante
    Windows(fichier & i & extension).Close
    
    Next nbfichiers

'Réactive l'actualisation de l'écran
Application.ScreenUpdating = True


End Sub

Alors où est(sont) l(es)'erreur(s)?

Solution

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

Exemple

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)

Solution

Pour ce faire, il faut créer le fichier consoCA.xlsm puis dans l'onglet DÉVELOPPEUR Visual Basic saisir le code VBA suivant :

Option Explicit

Sub ConsoliderFichiers()
    Dim repertoire As String
    Dim classeur As String
    Dim extension As String
    Dim wkSource As Workbook
    Dim wsCible As Worksheet
    Dim ligne As Long
    
    ' initialise la ligne à écrire
    ligne = 2
    ' initialise la feuille cible
    Set wsCible = ActiveWorkbook.Worksheets("conso")
    ' initialise le répertoire de travail
    repertoire = "c:\tests\excel\"
    ' récupère le premier fichier du répertoire
    classeur = Dir(repertoire)
  
    ' entame la boucle principale (tant qu’il y a des fichiers dans le répertoire)
    Do
        ' récupère les extensions des fichiers
        extension = Right(classeur, 4)
        ' sélectionne uniquement les extensions excel
        If extension = "xlsx" Then
            ' initialise le classeur source
            Set wkSource = CreateObject(repertoire & classeur)
            ' récupère la ligne des données sources
            wkSource.Worksheets(1).Range("A2:J2").Copy
            ' recopie les données sources vers la feuille cible sur la bonne ligne
            wsCible.Range("A" & ligne).PasteSpecial
            'prochaine ligne où seront copiées les données
            ligne = ligne + 1
        End If
            ' recherche du fichier suivant
            classeur = Dir
    Loop While classeur <> ""
    
End Sub

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

Remarque

EXCEL fournit en standard un outil de consolidation de différentes feuilles, mais pas d'automatisme sur son exécution

Conseil

Ne pas oublier l'Option Explicit en début de code et de bien nommer vos variables

Macros-commandes VBA

Remplacement des hyperliens

Contrôle de données