{"id":6443,"date":"2016-02-15T01:59:45","date_gmt":"2016-02-15T00:59:45","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=6443"},"modified":"2016-02-15T02:07:30","modified_gmt":"2016-02-15T01:07:30","slug":"excel-vba-importer-un-fichier-texte-dans-excel-cas-pratique","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=6443","title":{"rendered":"EXCEL, VBA : importer un fichier texte dans Excel (cas pratique)"},"content":{"rendered":"<p style=\"text-align: justify;\">Le pr\u00e9sent article met en pratique\u00a0l&#8217;<a href=\"http:\/\/www.auditsi.eu\/?p=6374\">importation de fichiers texte dans Excel<\/a>. Les exemples d\u00e9velopp\u00e9s ci-apr\u00e8s\u00a0sont des applications\u00a0courantes dans le monde de la comptabilit\u00e9 (expertise-comptable et audit compris) : importation dans Excel de balances g\u00e9n\u00e9rales, de journaux d&#8217;\u00e9critures comptables ou encore du <a href=\"http:\/\/www.auditsi.eu\/?tag=fec\">FEC<\/a> \u00e0 des fins d&#8217;analyse et de r\u00e9vision.<\/p>\n<p style=\"text-align: justify;\">Les \u00e9tapes \u00e0 suivre sont les suivantes :<\/p>\n<ul>\n<li style=\"text-align: justify;\">Lancer l&#8217;<a href=\"http:\/\/www.auditsi.eu\/?p=3995\">enregistreur de macro<\/a> (cf \u00e0 ce sujet la <a href=\"http:\/\/www.auditsi.eu\/?cat=1206\">s\u00e9rie d&#8217;articles ad hoc<\/a>)\u00a0afin de\u00a0r\u00e9aliser le param\u00e9trage de l&#8217;importation du fichier texte \u00e0 partir des deux actions suivantes \u00e0 r\u00e9aliser manuellement :<\/li>\n<\/ul>\n<ol>\n<li style=\"text-align: justify;\">Ouvrir le fichier (Fichier \/ Ouvrir \/ Tous les fichiers *.*),<\/li>\n<li style=\"text-align: justify;\">Param\u00e9trer la conversion du fichier : format de fichier texte (fixe ou d\u00e9limit\u00e9), d\u00e9cimales, s\u00e9parateurs de milliers&#8230;<\/li>\n<\/ol>\n<div id=\"attachment_6379\" style=\"width: 613px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/www.auditsi.eu\/?attachment_id=6379\" rel=\"attachment wp-att-6379\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6379\" class=\"size-full wp-image-6379\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2016\/01\/EXCEL-Assistant-de-conversion-Type-de-donn\u00e9es-dorigine.png\" alt=\"Assistant de conversion d'Excel : choix du type de donn\u00e9es d'origine\" width=\"603\" height=\"508\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2016\/01\/EXCEL-Assistant-de-conversion-Type-de-donn\u00e9es-dorigine.png 603w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2016\/01\/EXCEL-Assistant-de-conversion-Type-de-donn\u00e9es-dorigine-300x253.png 300w\" sizes=\"auto, (max-width: 603px) 100vw, 603px\" \/><\/a><p id=\"caption-attachment-6379\" class=\"wp-caption-text\"><em>Assistant de conversion d&#8217;Excel : choix du type de donn\u00e9es d&#8217;origine<\/em><\/p><\/div>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/www.auditsi.eu\/?p=6374\">OpenText<\/a> est la commande clef du programme puisque c&#8217;est elle qui ouvre et convertit le fichier texte en champs de donn\u00e9es.<\/p>\n<ul>\n<li style=\"text-align: justify;\">Ajouter \u00e9ventuellement des champs calcul\u00e9s ; par exemple, la racine des num\u00e9ros de comptes g\u00e9n\u00e9raux obtenue \u00e0 l&#8217;aide de la fonction <a href=\"http:\/\/www.auditsi.eu\/?p=5227\">Gauche<\/a>(adresse cellule;nbcar) (correspondant \u00e0 la fonction VBA <em>Left<\/em>)&#8230;<\/li>\n<li style=\"text-align: justify;\">Supprimer les colonnes inutiles aux analyses (commande <em>.Delete<\/em>).<\/li>\n<li style=\"text-align: justify;\">Mettre en place les <a href=\"http:\/\/www.auditsi.eu\/?p=2793\">filtres<\/a> de donn\u00e9es (<em>.Autofilter<\/em>) et les tris (<em>.Sort<\/em>).<\/li>\n<li style=\"text-align: justify;\">Mettre en forme les donn\u00e9es : largeur de colonnes (ajustement automatique avec la commande .<em>AutoFit<\/em>), montants (.<em>NumberFormat<\/em>)&#8230;<\/li>\n<li style=\"text-align: justify;\">Une fois l&#8217;ensemble des traitements d&#8217;importation et de mise en forme termin\u00e9, arr\u00eater l&#8217;enregistreur de macro.<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Pour tout conna\u00eetre du d\u00e9tail des \u00e9tapes li\u00e9es au lancement et \u00e0 l&#8217;arr\u00eat de l&#8217;enregistreur de macro ainsi que l&#8217;acc\u00e8s \u00e0 l&#8217;\u00e9diteur du code source de la macro, consulter la s\u00e9rie d&#8217;articles sur l&#8217;<a href=\"http:\/\/www.auditsi.eu\/?p=3995\">enregistreur de macro<\/a>.<\/p>\n<p style=\"text-align: justify;\">Exemple de macro obtenue \u00e0 partir de l&#8217;importation d&#8217;une balance TIGRE :<\/p>\n<pre class=\"lang:default decode:true \">Sub Macro1()\r\n'\r\n' Macro1 Macro\r\n'\r\n\r\n'\r\n    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\r\n    Columns(\"B:B\").Select\r\n    Selection.Delete Shift:=xlToLeft\r\n    Columns(\"B:B\").EntireColumn.AutoFit\r\n    Columns(\"C:D\").Select\r\n    Selection.Replace What:=\".\", Replacement:=\",\", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False\r\n    Range(\"A1\").Select\r\n    Selection.EntireRow.Insert\r\n    ActiveCell.FormulaR1C1 = \"Num\u00e9roCompte\"\r\n    Range(\"B1\").Select\r\n    ActiveCell.FormulaR1C1 = \"Libell\u00e9\"\r\n    Range(\"C1\").Select\r\n    ActiveCell.FormulaR1C1 = \"D\u00e9bit\"\r\n    Range(\"D1\").Select\r\n    ActiveCell.FormulaR1C1 = \"Cr\u00e9dit\"\r\n    Range(\"C1\").Select\r\n    Selection.EntireColumn.Insert\r\n    ActiveCell.FormulaR1C1 = \"Solde\"\r\n    Range(\"A1\").Select\r\n    Selection.CurrentRegion.Select\r\n    Selection.AutoFilter\r\n    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Clear\r\n    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range(\"D1:D971\"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers\r\n    Range(\"C2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=RC[1]-RC[2]\"\r\n    Range(\"C2\").Select\r\n    Selection.AutoFill Destination:=Range(\"C2:C2000\")\r\n    Range(\"C2:C2000\").Select\r\n    Columns(\"C:E\").Select\r\n    Selection.NumberFormat = \"#,##0.00\"\r\n    Columns(\"C:E\").EntireColumn.AutoFit\r\n    Range(\"F1\").Select\r\n    Selection.NumberFormat = \"#,##0.00\"\r\n    ActiveCell.FormulaR1C1 = \"Cpte1\"\r\n    Range(\"G1\").Select\r\n    Selection.NumberFormat = \"#,##0.00\"\r\n    ActiveCell.FormulaR1C1 = \"Cpte2\"\r\n    Range(\"H1\").Select\r\n    Selection.NumberFormat = \"#,##0.00\"\r\n    ActiveCell.FormulaR1C1 = \"Cpte3\"\r\n    Range(\"I1\").Select\r\n    Selection.NumberFormat = \"#,##0.00\"\r\n    ActiveCell.FormulaR1C1 = \"Cpte4\"\r\n    Range(\"F2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=LEFT(RC[-5],1)\"\r\n    Range(\"G2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=LEFT(RC[-6],2)\"\r\n    Range(\"H2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=guache(RC[-7],3)\"\r\n    Range(\"H2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=LEFT(RC[-7],3)\"\r\n    Range(\"I2\").Select\r\n    ActiveCell.FormulaR1C1 = \"=LEFT(RC[-8],4)\"\r\n    Range(\"F2:I2\").Select\r\n    Range(\"I2\").Activate\r\n    Selection.AutoFill Destination:=Range(\"F2:I2000\")\r\n    Range(\"A1\").Select\r\nEnd Sub<\/pre>\n<p style=\"text-align: justify;\">Exemples de param\u00e8tres de champs de donn\u00e9es de la fonction <em>OpenText<\/em> :<\/p>\n<ul>\n<li style=\"text-align: justify;\">Journaux d&#8217;\u00e9critures SAGE 100 :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">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\r\n<\/pre>\n<ul>\n<li>Balance g\u00e9n\u00e9rale TIGRE (<a href=\"http:\/\/www.auditsi.eu\/?p=4477\">DMS<\/a> PEUGEOT)\u00a0:<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">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<\/pre>\n<ul>\n<li>Balance g\u00e9n\u00e9rale CIEL :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">Workbooks.OpenText Filename:=\"C:\\Balance comparative N \u00e0 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<\/pre>\n<ul>\n<li>Journaux d&#8217;\u00e9critures CIEL :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">Workbooks.OpenText Filename:=\"C:\\liste des \u00e9critures.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<\/pre>\n<p style=\"text-align: justify;\">Le code g\u00e9n\u00e9r\u00e9 par l&#8217;enregistreur de macro n&#8217;est pas optimis\u00e9 mais est parfaitement op\u00e9rationnel et permet de r\u00e9pondre \u00e0 la plupart des besoins courants.<\/p>\n<p style=\"text-align: justify;\">Plusieurs am\u00e9liorations peuvent \u00eatre apport\u00e9es au programme. Par exemple, l&#8217;enregistreur de macro a int\u00e9gr\u00e9 le nom du fichier dans la fonction <em>OpenText<\/em>, ce qui a pour effet que seul un fichier portant ce nom pourra \u00eatre ult\u00e9rieurement trait\u00e9 par la macro. Or il est parfaitement de possible de demander \u00e0 l&#8217;utilisateur de s\u00e9lectionner le fichier de son choix \u00e0 l&#8217;aide de la commande <a href=\"http:\/\/www.auditsi.eu\/?p=6456\">GetOpenFilename<\/a>.<\/p>\n<p>Derni\u00e8re \u00e9tape : enregistrer le classeur. L&#8217;enregistrement d&#8217;un classeur contenant une macro doit \u00eatre r\u00e9alis\u00e9 au <a href=\"http:\/\/www.auditsi.eu\/?p=5875\">format *.xlsm<\/a>.<\/p>\n<p>Tous les articles en rapport avec la <a href=\"http:\/\/www.auditsi.eu\/?tag=conversion-de-fichier\">conversion de fichier<\/a>.<\/p>\n<p>Approfondir le sujet : <a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">Programmer en VBA<\/a> \/ <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"EXCEL%2C%20VBA%20%3A%20importer%20un%20fichier%20texte%20dans%20Excel%20%28cas%20pratique%29\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Le pr\u00e9sent article met en pratique\u00a0l&#8217;importation de fichiers texte dans Excel. Les exemples d\u00e9velopp\u00e9s ci-apr\u00e8s\u00a0sont des applications\u00a0courantes dans le monde de la comptabilit\u00e9 (expertise-comptable et audit compris) : importation dans Excel de balances g\u00e9n\u00e9rales, de journaux d&#8217;\u00e9critures comptables ou encore du FEC \u00e0 des fins d&#8217;analyse et de r\u00e9vision. Les \u00e9tapes \u00e0 suivre sont les &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=6443\" class=\"more-link\">Continue reading &lsquo;EXCEL, VBA : importer un fichier texte dans Excel (cas pratique)&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"EXCEL%2C%20VBA%20%3A%20importer%20un%20fichier%20texte%20dans%20Excel%20%28cas%20pratique%29\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[50,39,40,48,1569,215],"tags":[1714,1660,1692,166,940,450,173,1690,1018,308,1666,939,1687,267,1691,1633,52,22,986,294,1387,985,803,251,1154,217,1665,1689,1671,1688,1675,1137,1641,1676,1672],"class_list":["post-6443","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-cas-pratiques","category-controle-des-comptes","category-excel","category-programmer-en-vba","category-vba","tag-autofit","tag-numberformat","tag-sort","tag-analyse-de-donnees","tag-ascii","tag-autofilter","tag-balance-generale","tag-ciel","tag-comptabilite","tag-conversion-de-fichier","tag-convertisseur","tag-csv","tag-decimalseparator","tag-delete","tag-ecritures-comptables","tag-excel","tag-exploitation-des-donnees","tag-extraction-de-donnees","tag-fichier-texte","tag-filtres","tag-gauche","tag-importation-de-donnees","tag-journaux-comptables","tag-left","tag-logiciel-de-comptabilite","tag-macro-commande","tag-opentext","tag-sage-100","tag-startrow","tag-tigre","tag-trailingminusnumbers","tag-tri","tag-vba","tag-workbooks","tag-xlmsdos"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6443"}],"version-history":[{"count":15,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6443\/revisions"}],"predecessor-version":[{"id":6507,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6443\/revisions\/6507"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}