{"id":49271,"date":"2023-02-26T18:31:38","date_gmt":"2023-02-26T17:31:38","guid":{"rendered":"https:\/\/ekiwi-blog.de\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/"},"modified":"2023-02-26T20:23:49","modified_gmt":"2023-02-26T19:23:49","slug":"vba-read-out-folder-and-directory-names-and-list-in-excel","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/","title":{"rendered":"VBA: Read out folder and directory names and list in Excel"},"content":{"rendered":"<p>Let&#8217;s say you don&#8217;t have many folders to create with VBA and Excel, but the opposite case. That is, there is a directory with quite <strong>many subfolders<\/strong> on a drive and for some reason you need these subdirectories <strong>in the form of a list<\/strong> to process them further.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of content<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ekiwi-blog.de\/en\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/#Initial_situation_many_subdirectories_need_to_be_transferred_to_excel_sheet\" >Initial situation: many subdirectories need to be transferred to excel sheet<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ekiwi-blog.de\/en\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/#Macro_for_reading_out_the_subfolders\" >Macro for reading out the subfolders<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ekiwi-blog.de\/en\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/#Result\" >Result<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Initial_situation_many_subdirectories_need_to_be_transferred_to_excel_sheet\"><\/span>Initial situation: many subdirectories need to be transferred to excel sheet<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><u>A fictitious use case<\/u>: You have many documents that you need to hand over to someone or another company. These documents are structured in many individual folders. Now you want to document the progress and status of the document transfer in the form of an Excel list. It would be tedious and inefficient to type each folder name individually into the <a href=\"https:\/\/ekiwi-blog.de\/en\/50316\/vba-excel-macro-save-spreadsheet-as-csv-file\/\" title=\"VBA Excel Macro: Save spreadsheet as CSV file\">Excel spreadsheet<\/a>. Such a process must be automated. This is best done using <abbr title=\"Visual Basic for Application\">VBA<\/abbr>. So that is the initial situation.<\/p>\n<figure id=\"attachment_8378\" aria-describedby=\"caption-attachment-8378\" style=\"width: 998px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-8378\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten.png\" alt=\"Screenshot with subfolders and Excel list\" width=\"998\" height=\"542\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten.png 998w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten-300x163.png 300w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten-768x417.png 768w\" sizes=\"auto, (max-width: 998px) 100vw, 998px\" \/><figcaption id=\"caption-attachment-8378\" class=\"wp-caption-text\">these folders are to be listed in the Excel table<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Macro_for_reading_out_the_subfolders\"><\/span>Macro for reading out the subfolders<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To read out all the directory names, insert the following macro in the Visual Basic editor.<\/p>\n<pre><code><span style=\"color: #3366ff;\">Option Explicit<\/span>\r\n<span style=\"color: #3366ff;\">Public Sub<\/span> ListFolder()\r\n<span style=\"color: #3366ff;\">Dim<\/span> objFSO <span style=\"color: #3366ff;\">As Object<\/span>\r\n<span style=\"color: #3366ff;\">Dim<\/span> folder <span style=\"color: #3366ff;\">As Object<\/span>\r\n<span style=\"color: #3366ff;\">Dim<\/span> strPfad <span style=\"color: #3366ff;\">As String<\/span>\r\n<span style=\"color: #3366ff;\">Dim<\/span> subFolder <span style=\"color: #3366ff;\">As Object<\/span>, colSubfolders <span style=\"color: #3366ff;\">As Object<\/span>\r\n<span style=\"color: #3366ff;\">Dim<\/span> i <span style=\"color: #3366ff;\">As Integer\r\n<\/span>    <span style=\"color: #339966;\">'Replace path here to the directory to be read out<\/span>\r\n    strPfad = \"C:\\Users\\DeinUserName\\Desktop\\temp\\ImageFolder\"\r\n    <span style=\"color: #3366ff;\">Set<\/span> objFSO = CreateObject(\"Scripting.FileSystemObject\")\r\n    <span style=\"color: #3366ff;\">Set<\/span> folder = objFSO.GetFolder(strPfad)\r\n    <span style=\"color: #3366ff;\">Set<\/span> colSubfolders = folder.Subfolders\r\n    <span style=\"color: #339966;\">'Set start line \"1\" for Excel here<\/span>\r\n    i = 1\r\n    <span style=\"color: #3366ff;\">For Each<\/span> subFolder <span style=\"color: #3366ff;\">In<\/span> colSubfolders\r\n        i = i + 1\r\n        Range(\"A\" &amp; i).Value = subFolder.Name\r\n    <span style=\"color: #3366ff;\">Next<\/span> subFolder\r\n    <span style=\"color: #3366ff;\">Set<\/span> folder = <span style=\"color: #3366ff;\">Nothing<\/span>\r\n    <span style=\"color: #3366ff;\">Set<\/span> colSubfolders = <span style=\"color: #3366ff;\">Nothing<\/span>\r\n    <span style=\"color: #3366ff;\">Set<\/span> objFSO = <span style=\"color: #3366ff;\">Nothing<\/span>\r\n<span style=\"color: #3366ff;\">End Sub<\/span><\/code><\/pre>\n<p>The macro should preferably be inserted in the corresponding table in which the subfolders are to be listed. If you add the macro to the workbook, you still have to specify which table it refers to.<\/p>\n<figure id=\"attachment_8380\" aria-describedby=\"caption-attachment-8380\" style=\"width: 539px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-8380 size-full\" title=\"Assign macro to table\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-makro-in-tabelle-einfuegen.png\" alt=\"Screenshot VBA-Editor Excel Macro\" width=\"539\" height=\"266\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-makro-in-tabelle-einfuegen.png 539w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-makro-in-tabelle-einfuegen-300x148.png 300w\" sizes=\"auto, (max-width: 539px) 100vw, 539px\" \/><figcaption id=\"caption-attachment-8380\" class=\"wp-caption-text\">Insert macro in relevant table<\/figcaption><\/figure>\n<p>Now adjust the <a href=\"https:\/\/ekiwi-blog.de\/en\/26678\/where-is-itunes-exe-found-location-directory-path\/\">directory path<\/a> to be read out and define the row (variable i) and column (letter at Range [A]) at which cell the entries of the folder names should start.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Result\"><\/span>Result<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once all this is done, you can run the macro via the green <em>play<\/em> symbol in the VBA editor or via the &#8220;<em>Developer Tools<\/em>&#8221; (<em>Alt+F8<\/em>) in the Excel spreadsheet.<\/p>\n<p>For the example shown here, the result then looks like the following image.<\/p>\n<figure id=\"attachment_8379\" aria-describedby=\"caption-attachment-8379\" style=\"width: 526px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-8379 size-full\" title=\"Result after running the macro to read a directory\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten-ergebnis.png\" alt=\"Screenshot Windows Explorer and Excel with listed directory names\" width=\"526\" height=\"500\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten-ergebnis.png 526w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2021\/01\/vba-ordnernamen-in-excel-tabelle-auflisten-ergebnis-300x285.png 300w\" sizes=\"auto, (max-width: 526px) 100vw, 526px\" \/><figcaption id=\"caption-attachment-8379\" class=\"wp-caption-text\">listed folder names in the excel sheet<\/figcaption><\/figure>\n<p>In addition to reading out folders, with <a href=\"https:\/\/ekiwi-blog.de\/en\/24960\/vba-read-pdf\/\">VBA you can also read<\/a> out entire web pages.<\/p>","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s say you don&#8217;t have many folders to create with VBA and Excel, but the opposite case. That is, there<\/p>\n","protected":false},"author":2,"featured_media":6532,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[2252,2738,1702,1707,2737,1704,1700,1705],"class_list":["post-49271","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-code-en","tag-directories","tag-excel-en","tag-macro","tag-macros","tag-microsoft-office-en","tag-programming","tag-vba-en"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49271","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=49271"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/49271\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/6532"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=49271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=49271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=49271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}