Get Special Folder Path using VBA

.

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 am putting code for only 8 folders. At the end of this article you can download an Excel Workbook with code to play around with the code.

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

There are three ways of finding the above path in Excel VBA.

1. Using WScript.Shell

2. Using Windows shfolder.dll

3. Using Excel VBA Function Environ$

 

1. How to get path of Special folders in Windows using Excel VBA



Sub GetSpecialFolderPath()
Dim objSFolders As Object
Set objSFolders = CreateObject("WScript.Shell").SpecialFolders
Sheets("Sheet1").Activate
With Sheets("Sheet1")
.Range("B2").Value = "My Document Path is:-         " & objSFolders("mydocuments")
.Range("B3").Value = "Desktop Path is:-             " & objSFolders("desktop")
.Range("B4").Value = "All User Desktop Path is:-    " & objSFolders("allusersdesktop")
.Range("B5").Value = "Recent Documents Path is:-    " & objSFolders("recent")
.Range("B6").Value = "Favorites Document Path is:-  " & objSFolders("favorites")
.Range("B7").Value = "Programs Path is:-            " & objSFolders("programs")
.Range("B8").Value = "Start Menu Path is:-          " & objSFolders("StartMenu")
.Range("B9").Value = "Send To Path is:-             " & objSFolders("SendTo")
End With
End Sub

2. Get Special Folder Path using shfolder.dll

About shfolder.dll: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 etc. If this DLL is missing or disabled then accessing these folders is not possible.
Below is the Function which returns the Special Folder Path:


'***********************************
' Function to get the special
' folder path using WScript.Shell
'***********************************

Private Declare Function GetFolderPath Lib "shfolder.dll" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, _
    ByVal nFolder As Long, _
    ByVal hToken As Long, _
    ByVal dwReserved As Long, _
    ByVal lpszPath As String) As Long

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 and nFolder values while calling.


'**********************************
' Function to get the Folder Path
' using shfolder.dll
'**********************************
Function GetSFolderPath()
    Dim sBuffer As String
    sBuffer = Space$(260)
    'To get the My Documents Path
    If GetFolderPath(&H5, &H5, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D2").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Desktop Path
    If GetFolderPath(&H10, &H10, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D3").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the All User Desktop Path
    If GetFolderPath(&H19, &H19, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D4").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If     
    ' To get the Recent Document Path
    If GetFolderPath(&H8, &H8, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D5").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Favorites Path
    If GetFolderPath(&H6, &H6, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D6").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Program Path
    If GetFolderPath(&H2, &H2, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D7").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Start Menu Path
    If GetFolderPath(&HB, &HB, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D8").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
    ' To get the Send To Path
    If GetFolderPath(&H9, &H9, -1, SHGFP_TYPE_default, sBuffer) = 0 Then
        Sheet1.Range("D9").Value = Left$(sBuffer, StrPtr(sBuffer))
    End If
End Function

3. Using Excel VBA Function Environ$()

Note: You can also try getting folder paths using Environ$() VBA function. But this is not reliable. You can try getting the Temp Folder path as below:



Function GetSpecialPath()
    MsgBox Environ$("temp")
    MsgBox Environ$("userprofile")
End Function

I have used Environ$() function to get the Temp Folder Path in Windows in most of the send email articles.

You can download the file to play around !!

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

5 Comments

  1. Rajveer

    Amazing written code Friend..

    I find this very help full to auto save some files using macro to desired folders..

    ThanX a TON.. 🙂

    Reply
    • Vishwamitra Mishra

      Thanks Rajveer !!

      Reply
  2. kirtiraj

    hi… vish sir,

    I m a beginner to excel macros. i think i m good at excel but when it comes to macros(vba) i dont a bit of it… what should i do ? can u help me out with this…..

    ps

    when i wright your vba codes i m able to run it…. but i dont know how it works even i dont the purpose of syntax written over there…. what should i do… plese help me out with this…

    Thank u….

    Reply
  3. rimmy

    Hi Vishwa,
    I have two workbook, data1.xlsx and data2.xlsx and i have one master workbook mdata.xlsm . i want to copy data1.xlsx into sheet1 of mdata.xlsm and data2.xlsx into sheet2 of mdata.xlsm so please give me the vba code for this program.
    and i have one more question:
    I am getting the error-1004 application defined or object defined error
    in this code:Activesheet.Paste Destination:=Worksheets(“Sheet2”).Range(Cells(erow, 1), Cells(erow, 4))
    please tell me how to fix this error.

    Thank you

    Reply

Trackbacks/Pingbacks

  1. Mail Chart as Image from Outlook - VBA Code - Welcome to LearnExcelMacro.com - […] For sending the chart as an Image, it is important that we first save the Excel Chart as an…
  2. Excel VBA Özel Klasör Yollarını ve Ä°simlerini Listele • K40 - […] Yararlanılan Kaynaklar http://learnexcelmacro.com/wp/2012/12/get-special-folder-path-excel-macro/ […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest