Macros-commandes VBA/Exercices/Consolidation de classeurs

Leçons de niveau 14
Une page de Wikiversité, la communauté pédagogique libre.
Consolidation de classeurs
Image logo représentative de la faculté
Exercices no8
Leçon : Macros-commandes VBA

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[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

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 :

Exercice 2.png

Par soucis d’organisation et de praticité vous enregistrerez tous les classeurs dans le même dossier.

Consolidez ces classeurs.

Exercice 2: Consolidation par regroupement de bases de données[modifier | modifier le wikicode]

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 :

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


Exercice 3: Cherchez l'erreur[modifier | modifier le wikicode]

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[modifier | modifier le wikicode]

Ce code doit vous permettre de produire le tableau suivant: Consolidation - Résultats consolidés magasin.png

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)?

2) Consolidation par regroupement de bases de données[modifier | modifier le wikicode]

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)?

Exercice 4: Consolidation de données[modifier | modifier le wikicode]

Début de l'exemple
Fin de l'exemple