Générer automatiquement des rapports personnalisés, Excel VBA

Générer automatiquement des rapports personnalisés, Excel VBA

Voici une explication détaillée et un exemple de code VBA pour générer automatiquement des rapports personnalisés dans Excel. L’objectif est d’automatiser la création de rapports en utilisant VBA dans Excel. Nous allons créer un rapport personnalisé qui récupère des données d’une feuille source, les traite, puis les formate dans une nouvelle feuille de rapport.
Objectif
Nous allons :

• Extraire les données d’une feuille existante (par exemple, une feuille de données brutes).
• Appliquer des filtres spécifiques pour sélectionner les informations pertinentes.
• Créer un rapport personnalisé avec des en-têtes, des valeurs calculées et une mise en forme.
• Exporter le rapport dans une nouvelle feuille ou un nouveau fichier.

Explication détaillée
1. Structure des données :

• Supposons que nous avons une feuille de travail nommée « DataSource » avec des colonnes telles que « Date », « Produit », « Ventes », « Région », etc.
• Nous allons générer un rapport qui regroupe les ventes par « Région » et « Produit » et calcule les ventes totales pour chaque combinaison région-produit.

2. Configuration de VBA :

• Ouvrez le classeur Excel.
• Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
• Insérez un nouveau module en cliquant sur Insertion > Module.
• Écrivez le code VBA dans ce module pour automatiser la génération du rapport.

3. Flux du processus :

• Étape 1 : Collecter les données de la feuille « DataSource« .
• Étape 2 : Filtrer et regrouper les données.
• Étape 3 : Créer une nouvelle feuille pour le rapport.
• Étape 4 : Écrire les en-têtes et formater le rapport.
• Étape 5 : Calculer les statistiques récapitulatives.
• Étape 6 : Appliquer la mise en forme du rapport final.

Code VBA pour générer des rapports personnalisés automatiquement

Sub GenerateCustomizedReport()
    ' Définir les variables
    Dim wsSource As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim reportRow As Long
    Dim region As String
    Dim product As String
    Dim totalSales As Double
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    ' Référence à la feuille de données source
    Set wsSource = ThisWorkbook.Sheets("DataSource")
    ' Obtenir la dernière ligne avec des données dans la feuille "DataSource"
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Créer une nouvelle feuille pour le rapport
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "CustomizedReport"
    ' Écrire les en-têtes dans la feuille de rapport
    wsReport.Cells(1, 1).Value = "Région"
    wsReport.Cells(1, 2).Value = "Produit"
    wsReport.Cells(1, 3).Value = "Ventes Totales"
    ' Commencer à écrire les données à partir de la ligne 2
    reportRow = 2
    ' Boucle à travers les données de la source pour traiter les ventes par région et produit
    For i = 2 To lastRow ' Supposons que la ligne 1 contient les en-têtes
        region = wsSource.Cells(i, 4).Value ' Colonne 4 = "Région"
        product = wsSource.Cells(i, 2).Value ' Colonne 2 = "Produit"
        totalSales = wsSource.Cells(i, 3).Value ' Colonne 3 = "Ventes"
        ' Créer une clé unique en combinant région et produit
        Dim key As String
        key = region & "-" & product
        ' Si la clé n'existe pas dans le dictionnaire, l'ajouter avec la valeur initiale
        If Not dict.Exists(key) Then
            dict.Add key, totalSales
        Else
            ' Si la clé existe, ajouter les ventes à la valeur existante
            dict(key) = dict(key) + totalSales
        End If
    Next i
    ' Boucle à travers le dictionnaire et écriture des données dans la feuille de rapport
    For Each key In dict.Keys
        ' Diviser la clé en région et produit
        Dim keyParts() As String
        keyParts = Split(key, "-")
        ' Écrire la région, le produit et les ventes totales dans la feuille de rapport
        wsReport.Cells(reportRow, 1).Value = keyParts(0)
        wsReport.Cells(reportRow, 2).Value = keyParts(1)
        wsReport.Cells(reportRow, 3).Value = dict(key)
        reportRow = reportRow + 1
    Next key
    ' Appliquer un formatage au rapport
    With wsReport
        ' Formatage de la ligne d'en-têtes
        .Rows(1).Font.Bold = True
        .Rows(1).HorizontalAlignment = xlCenter
        .Rows(1).Interior.Color = RGB(200, 200, 255)
        ' Ajuster la largeur des colonnes
        .Columns("A:C").AutoFit
        ' Ajouter une bordure autour de la plage de données
        With .Range("A1:C" & reportRow - 1).Borders
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        ' Ajouter une somme des ventes totales en bas
        .Cells(reportRow, 1).Value = "Total"
        .Cells(reportRow, 2).Value = "Ventes"
        .Cells(reportRow, 3).Formula = "=SUM(C2:C" & reportRow - 1 & ")"
        .Cells(reportRow, 3).Font.Bold = True
    End With
    ' Notification à l'utilisateur que le rapport est généré
    MsgBox "Le rapport personnalisé a été généré avec succès !", vbInformation
End Sub

Explication du Code
1. Variables :

• wsSource : Fait référence à la feuille source (où les données brutes sont stockées).
• wsReport : La nouvelle feuille où le rapport sera créé.
• lastRow : La dernière ligne de données dans la feuille source.
• dict : Un objet dictionnaire pour stocker et sommer les ventes par « Région » et « Produit ».
• key : Une clé unique générée en combinant la région et le produit afin de regrouper les données correctement.

2. Création d’une Nouvelle Feuille :

• Une nouvelle feuille est créée avec le nom « CustomizedReport« .

3. Boucle à Travers les Données :

• Le code boucle à travers chaque ligne de la feuille « DataSource » et crée une clé unique en combinant la région et le produit.
• La valeur des ventes pour chaque combinaison région-produit est stockée et additionnée dans le dictionnaire.

4. Écriture des Données dans le Rapport :

• Après avoir traité toutes les données, le rapport est généré en itérant sur le dictionnaire et en écrivant la région, le produit, et les ventes totales pour chaque combinaison dans la nouvelle feuille.

5. Mise en Forme :

• La ligne d’en-têtes est formatée en gras, les textes sont centrés et la couleur de fond est ajustée.
• La largeur des colonnes est automatiquement ajustée pour un meilleur rendu.
• Des bordures sont ajoutées autour des données pour améliorer la lisibilité.
• Une somme des ventes totales est calculée en bas du rapport.

6. Notification Utilisateur :

• Après la génération du rapport, une boîte de dialogue est affichée pour informer l’utilisateur que le rapport a été généré avec succès.

Exécution du Code VBA
1. Ouvrez Excel et appuyez sur Alt + F11 pour ouvrir l’éditeur VBA.
2. Insérez un nouveau module via Insertion > Module.
3. Copiez et collez le code ci-dessus dans le module.
4. Appuyez sur F5 pour exécuter le code, et cela générera le rapport personnalisé.
Personnalisation
Vous pouvez ajuster ce code selon vos besoins spécifiques :

• Modifiez les colonnes et les références de données en fonction de la structure de vos données source.
• Appliquez des formats supplémentaires tels que la mise en forme conditionnelle, des polices différentes, ou des schémas de couleurs.
• Utilisez des calculs plus complexes (par exemple, des moyennes, des croissances en pourcentage) en fonction de vos exigences de reporting.

Il s’agit d’un modèle de base qui peut être étendu pour des scénarios plus complexes, comme l’ajout de graphiques ou l’exportation du rapport au format PDF.