{"id":701,"date":"2011-06-30T14:31:55","date_gmt":"2011-06-30T12:31:55","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=701"},"modified":"2015-02-15T11:27:51","modified_gmt":"2015-02-15T10:27:51","slug":"excel-vba-naviguer-dans-l%e2%80%99arborescence-d%e2%80%99un-disque-dur-pour-acceder-a-un-dossier-situe-en-amont-2","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=701","title":{"rendered":"EXCEL, VBA : naviguer dans l\u2019arborescence d\u2019un disque dur pour acc\u00e9der \u00e0 un dossier situ\u00e9 en amont"},"content":{"rendered":"<p style=\"text-align: justify;\">L\u2019analyse de donn\u00e9es n\u00e9cessite fr\u00e9quemment l\u2019ouverture de fichiers. Lorsque les fichiers ne sont pas stock\u00e9s dans le m\u00eame dossier que l\u2019application, le programme doit pointer sur le dossier sp\u00e9cifique. Quand ces donn\u00e9es sont localis\u00e9es dans un sous-dossier de l\u2019application, il suffit d\u2019ajouter le nom de ce dossier au chemin d\u2019acc\u00e8s \u00e0 l\u2019application (dans le cas d\u2019EXCEL, ce chemin est fourni par \u00ab\u00a0ThisWorkbook.Path\u00a0\u00bb). Par contre, si les donn\u00e9es sont stock\u00e9es dans un dossier situ\u00e9 en amont, il est n\u00e9cessaire de remonter l\u2019arborescence, ce qui est moins \u00e9vident \u00e0 programmer.<\/p>\n<p style=\"text-align: justify;\"><em><span style=\"text-decoration: underline;\">Exemple d\u2019arborescence\u00a0: dossiers de commissariat aux comptes stock\u00e9s sur un serveur<\/span><\/em><\/p>\n<p style=\"text-align: justify;\">Prenons l\u2019exemple d\u2019une arborescence o\u00f9 pour chaque client, nous avons en sous-dossiers\u00a0:<\/p>\n<ul>\n<li style=\"text-align: justify;\">les dossiers exercice (2009, 2010\u2026) avec dans chacun d\u2019eux, l\u2019application d\u2019audit de l\u2019ann\u00e9e (dossier AppliAUDIT),<\/li>\n<li style=\"text-align: justify;\">et le dossier permanent (DP).<\/li>\n<\/ul>\n<p style=\"text-align: justify;\"><em>Exemple d\u2019arborescence\u00a0:<\/em><\/p>\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td valign=\"top\" width=\"140\"><em>NIVEAU 1<\/em><\/td>\n<td valign=\"top\" width=\"123\"><em>NIVEAU 2<\/em><\/td>\n<td valign=\"top\" width=\"94\"><em>NIVEAU 3<\/em><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\">\\Nom du client<\/td>\n<td valign=\"top\" width=\"123\"><\/td>\n<td valign=\"top\" width=\"94\"><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\"><\/td>\n<td valign=\"top\" width=\"123\">\\DE 31122009<\/td>\n<td valign=\"top\" width=\"94\"><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\"><\/td>\n<td valign=\"top\" width=\"123\">\\DE31122010<\/td>\n<td valign=\"top\" width=\"94\"><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\"><\/td>\n<td valign=\"top\" width=\"123\"><\/td>\n<td valign=\"top\" width=\"94\">\\AppliAUDIT<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\"><\/td>\n<td valign=\"top\" width=\"123\"><\/td>\n<td valign=\"top\" width=\"94\">[\u00a0\u2026]<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" width=\"140\"><\/td>\n<td valign=\"top\" width=\"123\">\\DP<\/td>\n<td valign=\"top\" width=\"94\"><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Pour que l\u2019application d\u2019audit (dossier AppliAUDIT) acc\u00e8de au DP (pour collecter les informations permanentes n\u00e9cessaires \u00e0 la constitution du dossier exercice), il est n\u00e9cessaire de remonter de deux niveaux dans l\u2019arborescence.<\/p>\n<p style=\"text-align: justify;\"><em><span style=\"text-decoration: underline;\">Fonction permettant de remonter dans l\u2019arborescence du syst\u00e8me de fichiers<\/span><\/em><\/p>\n<p style=\"text-align: justify;\">La fonction suivante remonte d\u2019un cran dans l\u2019arborescence. Dans le cas qui nous occupe, pour qu\u2019elle remonte de deux crans, il suffit de l\u2019appeler deux fois.<\/p>\n<p style=\"text-align: justify;\"><em>Code-source de la fonction\u00a0:<\/em><\/p>\n<pre class=\"lang:default decode:true\">Function CheminAmont(chemin as String)\r\n\r\n    CheminAmont = Mid(chemin, 1, InStrRev(chemin, \"\\\") - 1)\r\n\r\nEnd Function<\/pre>\n<p style=\"text-align: justify;\"><em>Explication de la fonction\u00a0:<\/em><\/p>\n<p style=\"text-align: justify;\">Cette fonction retourne (\u00ab\u00a0<em>CheminAmont=<\/em>\u00a0\u00bb) la partie (\u00ab\u00a0<em>Mid(\u2026)<\/em>\u00a0\u00bb) du chemin (stock\u00e9 dans la variable \u00ab\u00a0<em>chemin<\/em>\u00a0\u00bb) entre le premier caract\u00e8re (\u00ab\u00a0,1,\u00a0\u00bb) et le premier \u00ab\u00a0\\\u00a0\u00bb (&#8220;<em>InStrRev(&#8230;)<\/em>&#8220;) situ\u00e9 le plus \u00e0 droite.<\/p>\n<p style=\"text-align: justify;\">Dans notre exemple, la variable \u00ab\u00a0chemin\u00a0\u00bb est \u00e9gal \u00e0 \u00ab\u00a0C:\\Nom du client\\DE 31122010\\AppliAUDIT\u00a0\u00bb. L\u2019appel de la fonction g\u00e9n\u00e9rera le r\u00e9sultat suivant\u00a0: \u00ab\u00a0C:\\Nom du client\\DE 31122010\u00a0\u00bb.<\/p>\n<p style=\"text-align: justify;\">Pour acc\u00e9der au DP, il suffit d\u2019ex\u00e9cuter le code suivant\u00a0:<\/p>\n<pre class=\"lang:default decode:true\">CheminDP= CheminAmont(CheminAmont(ThisWorkbook.Path)) &amp; \"\\DP\"<\/pre>\n<p>Approfondir le sujet : <a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">programmer en VBA<\/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%20naviguer%20dans%20l%E2%80%99arborescence%20d%E2%80%99un%20disque%20dur%20pour%20acc%C3%A9der%20%C3%A0%20un%20dossier%20situ%C3%A9%20en%20amont\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>L\u2019analyse de donn\u00e9es n\u00e9cessite fr\u00e9quemment l\u2019ouverture de fichiers. Lorsque les fichiers ne sont pas stock\u00e9s dans le m\u00eame dossier que l\u2019application, le programme doit pointer sur le dossier sp\u00e9cifique. Quand ces donn\u00e9es sont localis\u00e9es dans un sous-dossier de l\u2019application, il suffit d\u2019ajouter le nom de ce dossier au chemin d\u2019acc\u00e8s \u00e0 l\u2019application (dans le cas &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=701\" class=\"more-link\">Continue reading &lsquo;EXCEL, VBA : naviguer dans l\u2019arborescence d\u2019un disque dur pour acc\u00e9der \u00e0 un dossier situ\u00e9 en amont&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%20naviguer%20dans%20l%E2%80%99arborescence%20d%E2%80%99un%20disque%20dur%20pour%20acc%C3%A9der%20%C3%A0%20un%20dossier%20situ%C3%A9%20en%20amont\";<\/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":[39,1569,215],"tags":[429,431,430,98,1633,228,436,435,434,432,433,1641],"class_list":["post-701","post","type-post","status-publish","format-standard","hentry","category-cas-pratiques","category-programmer-en-vba","category-vba","tag-appliaudit","tag-arborescence","tag-chemin-dacces","tag-code-source","tag-excel","tag-function-end-function","tag-instrrev","tag-mid","tag-recursivite","tag-sous-dossier","tag-thisworkbook-path","tag-vba"],"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\/701","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=701"}],"version-history":[{"count":3,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/701\/revisions"}],"predecessor-version":[{"id":5892,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/701\/revisions\/5892"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}