{"id":12171,"date":"2012-12-22T14:48:25","date_gmt":"2012-12-22T14:48:25","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=2806"},"modified":"2017-07-26T18:47:37","modified_gmt":"2017-07-26T18:47:37","slug":"get-special-folder-path-excel-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/","title":{"rendered":"Get Special Folder Path using VBA"},"content":{"rendered":"

Hello Friends,<\/p>\n

In this article I am going to explain you how to get the path of Special Folders in Windows Operating System. Using VBA we can get path of Special folders path like, Desktop, My Documents, Library etc. Few important special folders are listed below. There are many more special folders available but I am putting code for only 8 folders. At the end of this article you can download<\/a> an Excel Workbook with code to play around with the code.<\/p>\n

[checklist icon=”” iconcolor=”” circle=”” circlecolor=”” size=”18px” class=”” id=””][li_item icon=”fa-book”]How to get path of My Documents<\/strong> in Excel VBA[\/li_item][li_item icon=”fa-desktop”]How to get path of Desktop<\/strong> in Excel VBA[\/li_item][li_item icon=”fa-laptop”]3. How to get path of All User Desktop<\/strong> in Excel VBA[\/li_item][li_item icon=”fa-file-text-o”]How to get path of Recent Documents<\/strong> in Excel VBA[\/li_item][li_item icon=”fa-folder-open-o”]How to get the path of Favorites<\/strong> folder in Excel VBA[\/li_item][li_item icon=”fa-folder-open”]How to get the path of Programs Folder<\/strong> in Excel VBA.[\/li_item][li_item icon=”fa-windows”]How to get the path of Start Menu Folder<\/strong> in Excel VBA
\n[\/li_item][li_item icon=”fa-share-square-o”]How to get the path of Send To Folder<\/strong> in Excel VBA[\/li_item][\/checklist]<\/p>\n

There are three ways of finding the above path in Excel VBA.<\/p>\n

1. Using WScript.Shell<\/strong><\/h2>\n

2. Using Windows shfolder.dll<\/strong><\/h2>\n

3. Using Excel VBA Function Environ$<\/strong><\/h2>\n

 <\/p>\n

1. How to get path of Special folders in Windows using Excel VBA<\/h1>\n
\r\n\r\nSub GetSpecialFolderPath()\r\nDim objSFolders As Object\r\nSet objSFolders = CreateObject("WScript.Shell").SpecialFolders\r\nSheets("Sheet1").Activate\r\nWith Sheets("Sheet1")\r\n.Range("B2").Value = "My Document Path is:-         " & objSFolders("mydocuments")\r\n.Range("B3").Value = "Desktop Path is:-             " & objSFolders("desktop")\r\n.Range("B4").Value = "All User Desktop Path is:-    " & objSFolders("allusersdesktop")\r\n.Range("B5").Value = "Recent Documents Path is:-    " & objSFolders("recent")\r\n.Range("B6").Value = "Favorites Document Path is:-  " & objSFolders("favorites")\r\n.Range("B7").Value = "Programs Path is:-            " & objSFolders("programs")\r\n.Range("B8").Value = "Start Menu Path is:-          " & objSFolders("StartMenu")\r\n.Range("B9").Value = "Send To Path is:-             " & objSFolders("SendTo")\r\nEnd With\r\nEnd Sub\r\n\r\n<\/code><\/pre>\n
2. Get Special Folder Path using shfolder.dll<\/h1>\n
About shfolder.dll:<\/strong><\/span>The shfolder.dll module is the DLL for shell folder services. The functions of the shfolder.dll include displaying of Windows special folders such as Desktop, My Documents, Programs<\/strong> etc. If this DLL is missing or disabled then accessing these folders is not possible.
\nBelow is the Function which returns the Special Folder Path:<\/p>\n
\r\n'***********************************\r\n' Function to get the special\r\n' folder path using WScript.Shell\r\n'***********************************\r\n\r\nPrivate Declare Function GetFolderPath Lib "shfolder.dll" _\r\n    Alias "SHGetFolderPathA" _\r\n    (ByVal hwndOwner As Long, _\r\n    ByVal nFolder As Long, _\r\n    ByVal hToken As Long, _\r\n    ByVal dwReserved As Long, _\r\n    ByVal lpszPath As String) As Long\r\n<\/code><\/pre>\n
Now we can call the above function to get the special folder path by passing the above mentioned parameters in the function. For Different folders all you need to change is hwndOwner<\/strong> and nFolder<\/strong> values while calling.<\/p>\n
\r\n'**********************************\r\n' Function to get the Folder Path\r\n' using shfolder.dll\r\n'**********************************\r\nFunction GetSFolderPath()\r\n    Dim sBuffer As String\r\n    sBuffer = Space$(260)\r\n    'To get the My Documents Path\r\n    If GetFolderPath(&H5, &H5, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D2").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the Desktop Path\r\n    If GetFolderPath(&H10, &H10, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D3").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the All User Desktop Path\r\n    If GetFolderPath(&H19, &H19, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D4").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If     \r\n    ' To get the Recent Document Path\r\n    If GetFolderPath(&H8, &H8, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D5").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the Favorites Path\r\n    If GetFolderPath(&H6, &H6, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D6").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the Program Path\r\n    If GetFolderPath(&H2, &H2, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D7").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the Start Menu Path\r\n    If GetFolderPath(&HB, &HB, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D8").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\n    ' To get the Send To Path\r\n    If GetFolderPath(&H9, &H9, -1, SHGFP_TYPE_default, sBuffer) = 0 Then\r\n        Sheet1.Range("D9").Value = Left$(sBuffer, StrPtr(sBuffer))\r\n    End If\r\nEnd Function\r\n<\/code><\/pre>\n
3. Using Excel VBA Function Environ$()<\/strong><\/h1>\n
 Note: <\/strong>You can also try getting folder paths using Environ$()<\/strong> VBA function. But this is not reliable. You can try getting the Temp Folder<\/strong> path as below: <\/span><\/p>\n
\r\n\r\nFunction GetSpecialPath()\r\n    MsgBox Environ$("temp")\r\n    MsgBox Environ$("userprofile")\r\nEnd Function\r\n<\/code><\/pre>\n
I have used Environ$() function to get the Temp Folder Path in Windows in most of the send email articles.<\/a><\/p>\n
You can download the file to play around !!<\/p>\n
<\/div>\n<\/span>","protected":false},"excerpt":{"rendered":"
Hello Friends, In this article I am going to explain you how to get the path of Special Folders in Windows Operating System. Using VBA we can get path of Special folders path like, Desktop, My Documents, Library etc. Few important special folders are listed below. There are many more special folders available but I […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1673,1246,1678],"tags":[],"class_list":["post-12171","post","type-post","status-publish","format-standard","hentry","category-excel-functions","category-macro","category-interesting-vba-functions"],"yoast_head":"\nGet Special Folder Path using VBA - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get Special Folder Path using VBA\" \/>\n<meta property=\"og:description\" content=\"Hello Friends, In this article I am going to explain you how to get the path of Special Folders in Windows Operating System. Using VBA we can get path of Special folders path like, Desktop, My Documents, Library etc. Few important special folders are listed below. There are many more special folders available but I […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-22T14:48:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-26T18:47:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Get Special Folder Path using VBA\",\"datePublished\":\"2012-12-22T14:48:25+00:00\",\"dateModified\":\"2017-07-26T18:47:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\"},\"wordCount\":489,\"commentCount\":7,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Functions\",\"Excel Macro\",\"Interesting VBA Functions\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\",\"name\":\"Get Special Folder Path using VBA - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-12-22T14:48:25+00:00\",\"dateModified\":\"2017-07-26T18:47:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Functions\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-functions\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Get Special Folder Path using VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Get Special Folder Path using VBA - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/","og_locale":"en_US","og_type":"article","og_title":"Get Special Folder Path using VBA","og_description":"Hello Friends, In this article I am going to explain you how to get the path of Special Folders in Windows Operating System. Using VBA we can get path of Special folders path like, Desktop, My Documents, Library etc. Few important special folders are listed below. There are many more special folders available but I […]","og_url":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-12-22T14:48:25+00:00","article_modified_time":"2017-07-26T18:47:37+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Get Special Folder Path using VBA","datePublished":"2012-12-22T14:48:25+00:00","dateModified":"2017-07-26T18:47:37+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/"},"wordCount":489,"commentCount":7,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Functions","Excel Macro","Interesting VBA Functions"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/","url":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/","name":"Get Special Folder Path using VBA - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-12-22T14:48:25+00:00","dateModified":"2017-07-26T18:47:37+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/12\/get-special-folder-path-excel-macro\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Functions","item":"https:\/\/vmlogger.com\/excel\/excel-functions\/"},{"@type":"ListItem","position":3,"name":"Get Special Folder Path using VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12171"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12171"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12171\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}