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
Derniers articles parBenoît RIVIERE (voir tous)
- Analyse des données comptables (FEC) : analyse de la conformité comptable des transactions et de leur dénouement - dimanche 23 novembre 2025
- L’AMF et l’AFA appellent à la vigilance sur le risque de corruption par des réseaux criminels de personnes ayant accès à des informations privilégiées - lundi 4 août 2025
- Détecter les inversions HT/TVA avec ANA-FEC2 - jeudi 31 juillet 2025
- Analyse de FEC en ligne avec ANA-FEC2 (manuel d’utilisation) - mardi 29 juillet 2025
- Rapport AFA 2024 : les contrôles comptables à ne pas négliger - samedi 5 juillet 2025