EXCEL, VBA : importer un fichier texte dans Excel (cas pratique)

Le présent article met en pratique l’importation de fichiers texte dans Excel. Les exemples développés ci-après sont des applications courantes dans le monde de la comptabilité (expertise-comptable et audit compris) : importation dans Excel de balances générales, de journaux d’écritures comptables ou encore du FEC à des fins d’analyse et de révision.

Les étapes à suivre sont les suivantes :

Lancer l’enregistreur de macro (cf à ce sujet la série d’articles ad hoc) afin de réaliser le paramétrage de l’importation du fichier texte à partir des deux actions suivantes à réaliser manuellement :

Ouvrir le fichier (Fichier / Ouvrir / Tous les fichiers *.*),
Paramétrer la conversion du fichier : format de fichier texte (fixe ou délimité), décimales, séparateurs de milliers…

Assistant de conversion d’Excel : choix du type de données d’origine

OpenText est la commande clef du programme puisque c’est elle qui ouvre et convertit le fichier texte en champs de données.

Ajouter éventuellement des champs calculés ; par exemple, la racine des numéros de comptes généraux obtenue à l’aide de la fonction Gauche(adresse cellule;nbcar) (correspondant à la fonction VBA Left)…
Supprimer les colonnes inutiles aux analyses (commande .Delete).
Mettre en place les filtres de données (.Autofilter) et les tris (.Sort).
Mettre en forme les données : largeur de colonnes (ajustement automatique avec la commande .AutoFit), montants (.NumberFormat)…
Une fois l’ensemble des traitements d’importation et de mise en forme terminé, arrêter l’enregistreur de macro.

Pour tout connaître du détail des étapes liées au lancement et à l’arrêt de l’enregistreur de macro ainsi que l’accès à l’éditeur du code source de la macro, consulter la série d’articles sur l’enregistreur de macro.

Exemple de macro obtenue à partir de l’importation d’une balance TIGRE :

Sub Macro1()
'
' Macro1 Macro
'

'
    Workbooks.OpenText Filename:="C:\CIEL11M0", Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(8, 1), Array(51, 1), Array(64, 1), Array(80, 1)), DecimalSeparator:=".", TrailingMinusNumbers:=True
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:D").Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("A1").Select
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "NuméroCompte"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Libellé"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Débit"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Crédit"
    Range("C1").Select
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "Solde"
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.AutoFilter
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("D1:D971"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]-RC[2]"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C2000")
    Range("C2:C2000").Select
    Columns("C:E").Select
    Selection.NumberFormat = "#,##0.00"
    Columns("C:E").EntireColumn.AutoFit
    Range("F1").Select
    Selection.NumberFormat = "#,##0.00"
    ActiveCell.FormulaR1C1 = "Cpte1"
    Range("G1").Select
    Selection.NumberFormat = "#,##0.00"
    ActiveCell.FormulaR1C1 = "Cpte2"
    Range("H1").Select
    Selection.NumberFormat = "#,##0.00"
    ActiveCell.FormulaR1C1 = "Cpte3"
    Range("I1").Select
    Selection.NumberFormat = "#,##0.00"
    ActiveCell.FormulaR1C1 = "Cpte4"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-5],1)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-6],2)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=guache(RC[-7],3)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-7],3)"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-8],4)"
    Range("F2:I2").Select
    Range("I2").Activate
    Selection.AutoFill Destination:=Range("F2:I2000")
    Range("A1").Select
End Sub

Exemples de paramètres de champs de données de la fonction OpenText :

Journaux d’écritures SAGE 100 :

Workbooks.OpenText Filename:= "C:\Ecritures au 31 12 15.txt", Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, 1), Array(5, 2), Array(7, 5), Array(15, 5), Array(23, 1), Array(35, 2), Array(43, 1), Array(46, 2), Array(73, 1), Array(84, 1), Array(85, 1), Array(103, 2)), DecimalSeparator:=".", TrailingMinusNumbers:=True

Balance générale TIGRE (DMS PEUGEOT) :

Workbooks.OpenText Filename:="C:\CIEL11M0", Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(8, 1), Array(51, 1), Array(64, 1), Array(80, 1)), DecimalSeparator:=".", TrailingMinusNumbers:=True

Balance générale CIEL :

Workbooks.OpenText Filename:="C:\Balance comparative N à N-10.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True

Journaux d’écritures CIEL :

Workbooks.OpenText Filename:="C:\liste des écritures.Txt", Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 2), Array(7, 5), Array(15, 2), Array(35, 2), Array(46, 2), Array(71, 1), Array(84, 2), Array(85, 2), Array(103, 2)), DecimalSeparator:=".", TrailingMinusNumbers:=True

Le code généré par l’enregistreur de macro n’est pas optimisé mais est parfaitement opérationnel et permet de répondre à la plupart des besoins courants.

Plusieurs améliorations peuvent être apportées au programme. Par exemple, l’enregistreur de macro a intégré le nom du fichier dans la fonction OpenText, ce qui a pour effet que seul un fichier portant ce nom pourra être ultérieurement traité par la macro. Or il est parfaitement de possible de demander à l’utilisateur de sélectionner le fichier de son choix à l’aide de la commande GetOpenFilename.

Dernière étape : enregistrer le classeur. L’enregistrement d’un classeur contenant une macro doit être réalisé au format *.xlsm.

Tous les articles en rapport avec la conversion de fichier.

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)

VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
Lancer l’exécution d’un script Python à partir d’une macro VBA - lundi 9 septembre 2024
Open Data : quoi de neuf ? - lundi 2 septembre 2024

Audit & Systèmes d'Information

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

EXCEL, VBA : importer un fichier texte dans Excel (cas pratique)

Benoît RIVIERE

Derniers articles parBenoît RIVIERE (voir tous)

Laisser un commentaire