Tableaux de bord REFECO (PEUGEOT) : Exploiter le fichier REODBL

Les concessionnaires automobiles remontent tous les mois une synthèse de leur activité comptable et commerciale au constructeur qu’ils représentent. A ce titre, les concessionnaires de la marque PEUGEOT établissent un tableau de bord “REFECO” (pour référentiel économique).

Ce tableau de bord est construit à partir d’une balance REODBL (fichier au format texte) extraite du système de gestion informatisé, le DMS. Cette balance reprend le solde de l’ensemble des comptes généraux, auxiliaires, analytiques et statistiques d’un mois donné. A l’échelle d’un groupe, l’agrégation de ces balances constitue une riche base de données dont l’analyse peut s’avérer fort utile (comparaisons entre concessions, N/N-1, revue des réalisations budgétaires…).

Pour constituer cette base de données, un programme VBA peut se charger utilement d’automatiser l’importation dans Excel de l’ensemble des balances.

Le code source de la macro VBA reproduit ci-après réalise cette tâche.

Pour lancer les traitements, il faut appeler la procédure (Sub) nommée Exploitation_REODBL. Pour des raisons pratiques, cette procédure peut être rattachée à un bouton qui lancera les traitements sur un simple clic de l’utilisateur.

En synthèse, cette macro suit les étapes suivantes :

Une boucle compteur (For Each ObjFichier In ObjDossier.Files… Next) scrute le dossier qui contient l’ensemble des balances REODBL à traiter et lance l’importation (appel de la procédure TraitementREODBLEnCours).

For Each ObjFichier In ObjDossier.Files
    NomClasseurREODBLEnCours = ObjFichier.Name
    NumEntité = NumEntité + 1
    TraitementREODBLEnCours
Next

La procédure TraitementREODBLEnCours effectue les tâches suivantes :

Ouverture successive des balances REODBL

Le fichier REODBL est un simple fichier texte. La démarche d’importation du fichier REODBL est exposée dans l’article Importer un fichier texte dans Excel. C’est la commande OpenText qui réalise cette ouverture à l’aide du code source suivant (paramétré selon les spécificités du fichier REODBL) :

'Ouverture du fichier REODBL
Workbooks.OpenText Filename:=chemin & NomClasseurREODBLEnCours, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(16, 1), Array(22, 1), Array(28, 1), Array(34, 1), Array(42, 1), Array(142, 1), Array(163, 1), Array(184, 1), Array(205, 1), Array(226, 1), Array(247, 1), Array(268, 1)), TrailingMinusNumbers:=True

Un fichier REODBL est constitué d’une première ligne comprenant les champs : REODBL (champ contenant ce libellé), le chiffre 1, le code concessionnaire, la date d’arrêté du REFECO (au format AAAAMM), la date de début d’exercice (au format AAAAMM), la date de fin d’exercice (au format AAAAMM), la date de la balance REFECO (au format AAAAMMJJ). Les autres lignes sont toutes constituées ainsi : REODBL, le chiffre 2, le code concessionnaire, la date d’arrêté du REFECO (au format AAAAMM), le numéro de compte (général, analytique…), deux champs vides, le libellé de compte puis six champs correspondant au solde des comptes : cumul solde débiteur, créditeur à fin mois précédent (m-1) (2 champs), idem à fin m (2 champs) et mouvements m (2 champs).

Formatage des montants

La décimale des montants n’est pas retranscrite dans le fichier REODBL. Ainsi le montant 101.10 va être représenté sous la forme 10110.

Les quelques lignes qui suivent se chargent de retranscrire les décimales des champs n° 9 à 14 correspondant aux colonnes J à O dans la feuille de calcul finale obtenue dans Excel (pour ce faire chaque montant est simplement divisé par cent, ainsi 10110 devient 101,10) :

For k = 1 To UBound(TableREODBLEncours, 1)
    For j = 9 To 14
        TableREODBLEncours(k, j) = TableREODBLEncours(k, j) / 100
    Next j
Next k

Ajout d’un champ nommant la concession

Le fichier REODBL identifie les concessions par leur code concession ce qui n’est pas très parlant. Aussi, une table (TableEntités) affectant les codes concession (colonne A) à leur nom (colonne B) est créée dans l’onglet CODES AP.

TableEntités = ClasseurAgrégat.Sheets("CODES AP").Range("A1:B30").Value

Lors du traitement des balances REODBL, le programme recherche le code entité dans la table TableEntités puis…

For i = 1 To UBound(TableEntités, 1)
    If CStr(TableEntités(i, 1)) = Left(CStr(TableREODBLEncours(2, 3)), 6) Then
        j = i
        Exit For
    End If
Next i

… stocke le nom de la concession associé au code concession dans la table TableREODBLEncours :

.Range("A" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), 1).Value = TableEntités(j, 2)

Le contenu de la balance REODBL (table de données TableREODBLEncours) remanié est collé dans le classeur Excel ClasseurAgrégat :

.Range("B" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), UBound(TableREODBLEncours, 2)).Value = TableREODBLEncours

Enfin, fermeture (.Close) de la balance REODBL (sans la sauvegarder SaveChanges:=False)

ClasseurREODBL.Close SaveChanges:=False

Ceci fait, le programme repart sur la balance suivante.

Une fois toutes les balances lues, l’utilisateur obtient une base de données regroupant l’ensemble des données comptables d’un mois donné. Agrégée avec les balances des mois voire des années antérieures, l’utilisateur dispose d’une riche base de données dont l’analyse lui permettra de tirer des enseignements et de prendre des décisions.

Code source complet de la macro VBA :

'Extraction REODBL
'=> Extraction des données des fichiers REODBL
'
'Programmé par Benoît RIVIERE, www.auditsi.eu, benoit@auditsi.eu (08/2016)

Option Explicit

'Déclaration des constantes & variables
Const lignedébut = 10
'---Fichiers
Const ExtXLS = "xls"
Const NomDossierREODBL = "REODBL"

'Déclaration des variables
'---Chemin
Dim chemin As String
'---Objets dossier et fichier
Dim ObjFSO, ObjDossier, ObjFichier
'---Classeurs & onglets
Dim NomClasseurREODBLEnCours As String
Dim ClasseurREODBL As Workbook
Dim ClasseurAgrégat As Workbook
Dim CodeEntité As String
Dim NomEntité As String
Dim DateFDM As Variant
Dim NumEntité As Integer
Dim NbFichiers As Integer

Dim TableEntités As Variant

Dim TableREODBLEncours As Variant
Dim NbLignesREODBLcumulé As Long

Sub TraitementREODBLEnCours()
    Dim i, j As Integer
    Dim k As Long
    
    'Ouverture du fichier REODBL
    Workbooks.OpenText Filename:=chemin & NomClasseurREODBLEnCours, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(7, 1), Array(16, 1), Array(22, 1), Array(28, 1), Array(34, 1), Array(42, 1), Array(142, 1), Array(163, 1), Array(184, 1), Array(205, 1), Array(226, 1), Array(247, 1), Array(268, 1)), TrailingMinusNumbers:=True
    Set ClasseurREODBL = ActiveWorkbook
    TableREODBLEncours = ClasseurREODBL.Sheets(1).Range(Cells(1, 1), Cells(ClasseurREODBL.ActiveSheet.UsedRange.Rows.Count, ClasseurREODBL.ActiveSheet.UsedRange.Columns.Count)).Value
    
    For k = 1 To UBound(TableREODBLEncours, 1)
        For j = 9 To 14
            TableREODBLEncours(k, j) = TableREODBLEncours(k, j) / 100
        Next j
    Next k
    
    j = 0
    For i = 1 To UBound(TableEntités, 1)
        If CStr(TableEntités(i, 1)) = Left(CStr(TableREODBLEncours(2, 3)), 6) Then
            j = i
            Exit For
        End If
    Next i
    
    With ClasseurAgrégat.Sheets("REODBL")
        If NbLignesREODBLcumulé = 0 Then .UsedRange.Rows("5:" & ActiveSheet.UsedRange.Rows.Count).Value = ""
        .Range("B" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), UBound(TableREODBLEncours, 2)).Value = TableREODBLEncours
        If j <> 0 Then
            .Range("A" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), 1).Value = TableEntités(j, 2)
            .Range("P" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), 1).Value = "=J" & NbLignesREODBLcumulé + 4 + 1 & "-K" & NbLignesREODBLcumulé + 4 + 1
            .Range("Q" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), 1).Value = "=L" & NbLignesREODBLcumulé + 4 + 1 & "-M" & NbLignesREODBLcumulé + 4 + 1
            .Range("R" & NbLignesREODBLcumulé + 4 + 1).Resize(UBound(TableREODBLEncours, 1), 1).Value = "=N" & NbLignesREODBLcumulé + 4 + 1 & "-O" & NbLignesREODBLcumulé + 4 + 1
        End If
    End With
    NbLignesREODBLcumulé = NbLignesREODBLcumulé + UBound(TableREODBLEncours, 1)
    'Fermeture du fichier REODBL
    ClasseurREODBL.Close SaveChanges:=False
End Sub

Sub Exploitation_REODBL()
    NumEntité = 0
    NbLignesREODBLcumulé = 0
    Set ClasseurAgrégat = ActiveWorkbook
    
    TableEntités = ClasseurAgrégat.Sheets("CODES AP").Range("A1:B30").Value
    
    chemin = ThisWorkbook.Path & "\" & NomDossierREODBL & "\"
    Set ObjFSO = CreateObject("Scripting.FileSystemObject")
    Set ObjDossier = ObjFSO.GetFolder(chemin)
    NbFichiers = ObjDossier.Files.Count
    If NbFichiers > 0 Then
        For Each ObjFichier In ObjDossier.Files
            NomClasseurREODBLEnCours = ObjFichier.Name
            NumEntité = NumEntité + 1
            TraitementREODBLEnCours
        Next
    End If
End Sub

Tous savoir sur la conversion de fichier (Texte <-> Excel) / l’analyse de données

Approfondir le sujet : Programmer en VBA / Découvrir la série d’articles Maîtriser Excel

Bio
Derniers articles

Benoît RIVIERE

Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Derniers articles parBenoît RIVIERE (voir tous)

Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
Les données de la facturation électronique - mercredi 9 octobre 2024
VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024

Audit & Systèmes d'Information

Pistes de réflexion sur l’audit et les systèmes d’information

Tableaux de bord REFECO (PEUGEOT) : Exploiter le fichier REODBL

Benoît RIVIERE

Derniers articles parBenoît RIVIERE (voir tous)

Laisser un commentaire