{"id":6512,"date":"2016-02-24T00:58:40","date_gmt":"2016-02-23T23:58:40","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=6512"},"modified":"2016-02-26T13:33:00","modified_gmt":"2016-02-26T12:33:00","slug":"vba-excel-analyser-le-fichier-des-ecritures-comptables-fec","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=6512","title":{"rendered":"VBA, EXCEL : analyser le Fichier des Ecritures Comptables (FEC)"},"content":{"rendered":"

La communication du\u00a0Fichier des Ecritures Comptables (FEC)<\/a> \u00e0 l’administration fiscale lors d’une v\u00e9rification de comptabilit\u00e9 (contr\u00f4le fiscal) est une obligation introduite pour les exercices ouverts \u00e0 compter du 1er janvier 2014. Le FEC est une extraction normalis\u00e9e qui reprend l’ensemble des \u00e9critures comptables d’un contribuable. Cette obligation est une opportunit\u00e9 pour l’entreprise. En effet, la normalisation de cette extraction de donn\u00e9es favorise l’usage de l’analyse des donn\u00e9es<\/a> comptables.<\/p>\n

L’article A 47 A-1 du Livre des proc\u00e9dures fiscales<\/a> modifi\u00e9 par l\u2019arr\u00eat\u00e9 du 29 juillet 2013 d\u00e9finit le contenu du Fichier des Ecritures Comptables (FEC).\u00a0Les champs de donn\u00e9es sont les suivants\u00a0:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
INFORMATION<\/th>\nNOM DU CHAMP<\/th>\nTYPE DE CHAMP<\/th>\n<\/tr>\n
1. Le code journal de l’\u00e9criture comptable<\/td>\nJournalCode<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
2. Le libell\u00e9 journal de l’\u00e9criture comptable<\/td>\nJournalLib<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
3. Le num\u00e9ro sur une s\u00e9quence continue de l’\u00e9criture comptable<\/td>\nEcritureNum<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
4. La date de comptabilisation de l’\u00e9criture comptable<\/td>\nEcritureDate<\/td>\nDate<\/td>\n<\/tr>\n
5. Le num\u00e9ro de compte, dont les trois premiers caract\u00e8res doivent correspondre \u00e0 des chiffres respectant les normes du plan comptable fran\u00e7ais<\/td>\nCompteNum<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
6. Le libell\u00e9 de compte, conform\u00e9ment \u00e0 la nomenclature du plan comptable fran\u00e7ais<\/td>\nCompteLib<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
7. Le num\u00e9ro de compte auxiliaire (\u00e0 blanc si non utilis\u00e9)<\/td>\nCompAuxNum<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
8. Le libell\u00e9 de compte auxiliaire (\u00e0 blanc si non utilis\u00e9)<\/td>\nCompAuxLib<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
9. La r\u00e9f\u00e9rence de la pi\u00e8ce justificative<\/td>\nPieceRef<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
10. La date de la pi\u00e8ce justificative<\/td>\nPieceDate<\/td>\nDate<\/td>\n<\/tr>\n
11. Le libell\u00e9 de l’\u00e9criture comptable<\/td>\nEcritureLib<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
12. Le montant au d\u00e9bit<\/td>\nDebit<\/td>\nNum\u00e9rique<\/td>\n<\/tr>\n
13. Le montant au cr\u00e9dit<\/td>\nCredit<\/td>\nNum\u00e9rique<\/td>\n<\/tr>\n
14. Le lettrage de l’\u00e9criture comptable (\u00e0 blanc si non utilis\u00e9)<\/td>\nEcritureLet<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n
15. La date de lettrage (\u00e0 blanc si non utilis\u00e9)<\/td>\nDateLet<\/td>\nDate<\/td>\n<\/tr>\n
16. La date de validation de l’\u00e9criture comptable<\/td>\nValidDate<\/td>\nDate<\/td>\n<\/tr>\n
17. Le montant en devise (\u00e0 blanc si non utilis\u00e9)<\/td>\nMontantdevise<\/td>\nNum\u00e9rique<\/td>\n<\/tr>\n
18. L’identifiant de la devise (\u00e0 blanc si non utilis\u00e9)<\/td>\nIdevise<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Si les informations ” d\u00e9bit ” et ” cr\u00e9dit ” ne sont pas pr\u00e9sentes dans le syst\u00e8me informatis\u00e9 comptable de l’entreprise, les informations 12 et 13 peuvent \u00eatre respectivement remplac\u00e9es par ” montant ” et ” sens “, sur le mod\u00e8le suivant :<\/em><\/p>\n\n\n\n\n\n
INFORMATION<\/th>\nNOM DU CHAMP<\/th>\nTYPE DE CHAMP<\/th>\n<\/tr>\n
12. Le montant<\/td>\nMontant<\/td>\nNum\u00e9rique<\/td>\n<\/tr>\n
13. Le sens<\/td>\nSens<\/td>\nAlphanum\u00e9rique<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

L’application VBA qui suit propose d’exploiter le FEC \u00e0 des fin d’analyse sous Excel. A cette fin, cette application convertit le FEC du format texte vers un classeur Excel cr\u00e9\u00e9 \u00e0 cet effet et effectue quelques ajouts facilitant les travaux d’analyse de donn\u00e9es.<\/p>\n

'ANA_FEC v1.0, 02\/2016\r\n'Programm\u00e9 par Beno\u00eet RIVIERE\r\n\r\nSub ExploitationFEC()\r\n    Dim NomFichier As Variant 'compos\u00e9 du lecteur+chemin+fichier+ext\r\n    Dim oFSO As Scripting.FileSystemObject 'permet de lire le d\u00e9tail d'un nom de fichier\r\n    Dim NbLignes As Long\r\n    \r\n    'S\u00e9lection du fichier \u00e0 importer\r\n    NomFichier = Application.GetOpenFilename(\"Fichiers Texte (*.txt),*.txt,Tous les fichiers (*.*),*.* \", 1, \"S\u00e9lectionnez le Fichier des Ecritures Comptables (FEC) \u00e0 importer\", , False)\r\n    '---si l'utilisateur annule l'importation => sortie de la proc\u00e9dure\r\n    If NomFichier = False Then Exit Sub\r\n    \r\n    'Ouverture du fichiers des \u00e9critures (format texte) et conversion des colonnes\r\n    Workbooks.OpenText Filename:=NomFichier, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=\"|\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True\r\n    \r\n    'Nombre de lignes d'\u00e9critures (y compris la ligne d'ent\u00eate)\r\n    NbLignes = Range(\"A1\", Selection.End(xlDown)).Cells.Count\r\n    \r\n    'Remplacement des espaces dans les champs D\u00e9bit\/Cr\u00e9dit (FEC TIGRE)\r\n    Columns(\"L:M\").Select\r\n    Selection.Replace What:=\"               \", Replacement:=\"\", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False\r\n    \r\n    'Ajout de champs additionnels\r\n    '---Solde de la ligne d'\u00e9criture\r\n    Range(\"S1\").Formula = \"Solde\"\r\n    Range(\"S2\").FormulaR1C1 = \"=RC[-7]-RC[-6]\"\r\n    '------Duplication de la formule jusqu'\u00e0 la derni\u00e8re ligne\r\n    Range(\"S2\").AutoFill Destination:=Range(\"S2:S\" & NbLignes)\r\n    '---aaaa\/mm\r\n    Range(\"T1\").Formula = \"aaaamm\"\r\n    Range(\"T2\").FormulaR1C1 = \"=LEFT(RC[-16],4)&\"\"\/\"\"&LEFT(RIGHT(RC[-16],4),2)\"\r\n    Range(\"T2\").AutoFill Destination:=Range(\"T2:T\" & NbLignes)\r\n    '---Cpte1, Cpte2 et Cpte3\r\n    Range(\"U1\").Formula = \"Cpte1\"\r\n    Range(\"V1\").Formula = \"Cpte2\"\r\n    Range(\"W1\").Formula = \"Cpte3\"\r\n    Range(\"U2\").FormulaR1C1 = \"=LEFT(RC[-16],1)\"\r\n    Range(\"V2\").FormulaR1C1 = \"=LEFT(RC[-17],2)\"\r\n    Range(\"W2\").FormulaR1C1 = \"=LEFT(RC[-18],3)\"\r\n    Range(\"U2:W2\").AutoFill Destination:=Range(\"U2:W\" & NbLignes)\r\n\r\n    'Mise en place des filtres de donn\u00e9es automatiques\r\n    Range(\"A1\").AutoFilter\r\n    \r\n    'Insertion des sous.totaux de filtres sur champs D\/C\/solde\r\n    '---Insertion d'une ligne vierge\r\n    Range(\"A1\").EntireRow.Insert\r\n    '---Formule de calcul sur le champ D\u00e9bit...\r\n\u00a0\u00a0\u00a0 Range(\"L1\").FormulaR1C1 = \"=SUBTOTAL(9,R[2]C:R[\" & NbLignes + 1 & \"]C)\"\r\n    '---...Puis duplication de la formule sur les champs Cr\u00e9dit et Solde\r\n    Range(\"L1\").Copy\r\n    Range(\"M1\").Select\r\n    ActiveSheet.Paste\r\n    Range(\"S1\").Select\r\n    ActiveSheet.Paste\r\n    \r\n    'Formatage\r\n    '---Formatage des champs D\/C et solde\r\n    Range(\"L:M,S:S\").NumberFormat = \"#,##0.00\"\r\n    '---Ajustement des colonnes \u00e0 leur contenu\r\n    Cells.Columns.AutoFit\r\n\r\n    'Sauvegarde du classeur au format Excel\r\n    Set oFSO = New Scripting.FileSystemObject\r\n    ActiveWorkbook.SaveAs Filename:=oFSO.GetBaseName(NomFichier), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False\r\nEnd Sub<\/pre>\n
Quelques explications du code-source de l’application :<\/p>\n
'S\u00e9lection du fichier \u00e0 importer\r\nNomFichier = Application.GetOpenFilename(\"Fichiers Texte (*.txt),*.txt,Tous les fichiers (*.*),*.* \", 1, \"S\u00e9lectionnez le Fichier des Ecritures Comptables (FEC) \u00e0 importer\", , False)\r\n'---si l'utilisateur annule l'importation => sortie de la proc\u00e9dure\r\nIf NomFichier = False Then Exit Sub<\/pre>\n
Cette ligne permet \u00e0 l’utilisateur du programme de s\u00e9lectionner un fichier FEC (format texte) et de r\u00e9cup\u00e9rer le nom du fichier (arborescence comprise) dans la variable NomFichier<\/em>. La commande .GetOpenFilename<\/a> r\u00e9alise cette op\u00e9ration. Si l’utilisateur ne s\u00e9lectionne pas de fichier (annulation en appuyant sur la touche Echap<\/em>), la variable NomFichier<\/em> prendra la valeur bool\u00e9enne<\/a> False. L’expression If NomFichier = False Then Exit Sub<\/em> permet de sortir de la proc\u00e9dure en cours sans r\u00e9aliser les instructions qui suivent (en l’occurrence l’importation du FEC).<\/p>\n
'Ouverture du fichiers des \u00e9critures (format texte) et conversion des colonnes\r\nWorkbooks.OpenText Filename:=NomFichier, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=\"|\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True\r\n<\/pre>\n
La macro VBA ouvre le FEC (commande .OpenText<\/a>), l’importe dans Excel d\u00e8s la premi\u00e8re ligne (StartRow:=1<\/em>). Les dix-huit champs de donn\u00e9es d\u00e9finis par la Loi sont retranscrits dans les colonnes A \u00e0 R. Les deux s\u00e9parateurs pr\u00e9vus par la r\u00e9glementation sont la tabulation (Tab:=True<\/em>)\u00a0et le signe | (OtherChar:=”|”<\/em>).<\/p>\n
Range(\"U2\").FormulaR1C1 = \"=LEFT(RC[-16],1)\"<\/pre>\n
Ajout de champs additionnels : par exemple, ressortir le chiffre le plus \u00e0 gauche (LEFT<\/em>) du num\u00e9ro de compte (classe)…<\/p>\n
'Mise en place des filtres de donn\u00e9es automatiques\r\nRange(\"A1\").AutoFilter<\/pre>\n
Met en place des filtres automatiques (.AutoFilter<\/em>, \u00e9quivalent au menu Donn\u00e9es \/ Filtrer).<\/p>\n
Range(\"A1\").EntireRow.Insert<\/pre>\n
Ins\u00e8re (.Insert<\/em>)\u00a0une ligne avant la ligne une (A1).<\/p>\n
Range(\"L1\").FormulaR1C1 = \"=SUBTOTAL(9,R[2]C:R[\" & NbLignes + 1 & \"]C)\"<\/pre>\n
La commande SUBTOTAL(fonction,plage de cellules)<\/a><\/em>\u00a0calcule la somme des montants non filtr\u00e9s (fonction n\u00b0 9). Cette commande correspond \u00e0 SOUS.TOTAL(fonction; plage de cellules)<\/em> d’Excel.<\/p>\n
Range(\"L1\").Copy\r\nRange(\"M1\").Select\r\nActiveSheet.Paste<\/pre>\n
Cette partie du code copie (.Copy<\/em>) le contenu de la cellule L1 puis le colle en M1 (.Paste), automatisant ainsi le copier\/coller<\/a>.<\/p>\n
Range(\"L:M,S:S\").NumberFormat = \"#,##0.00\"<\/pre>\n
Formate les colonnes L, M et S au format num\u00e9rique (.NumberFormat<\/em><\/a>) avec s\u00e9parateur de milliers et deux d\u00e9cimales.<\/p>\n
Cells.Columns.AutoFit<\/pre>\n
Ajuste automatiquement\u00a0la largeur des colonnes \u00e0 leur contenu (.AutoFit<\/em>).<\/p>\n
Dim oFSO As Scripting.FileSystemObject 'permet de lire le d\u00e9tail d'un nom de fichier\r\n\r\n[...]\r\n\r\n'Sauvegarde du classeur au format Excel\r\nSet oFSO = New Scripting.FileSystemObject\r\nActiveWorkbook.SaveAs Filename:=oFSO.GetBaseName(NomFichier), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False\r\n<\/pre>\n
La\u00a0fonction oFSO.GetBaseName(NomFichier)<\/em> renvoie le nom du fichier compris dans la cha\u00eene NomFichier<\/em>. La commande .SaveAs<\/em> \u00e9quivaut au menu Fichier \/ Enregistrer sous… d’Excel ; le param\u00e8tre FileFormat:=xlOpenXMLWorkbook<\/em> permet d’enregistrer le classeur au format *.xlsx.<\/p>\n
La fonction .GetBaseName<\/em> fait partie de la biblioth\u00e8que Microsoft Scripting Runtime. Cette biblioth\u00e8que doit \u00eatre activ\u00e9e avant de lancer le programme (Menu Outils \/ R\u00e9f\u00e9rences<\/a>… de l’EDI VBA).<\/p>\n
Points d’am\u00e9lioration :<\/p>\n