Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding the Path to the Desktop.
Donald is writing a macro in which he needs to reference a user's desktop. However, the path to the desktop necessarily varies from system to system and user to user. He wonders what coding he can use to determine the path to the desktop regardless of system.
There are several ways to find the path to the desktop in VBA. One way is to call the Windows scripting host, in this manner:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Function GetDesktop() As String
Dim oWSHShell As Object
Set oWSHShell = CreateObject("WScript.Shell")
GetDesktop = oWSHShell.SpecialFolders("Desktop")
Set oWSHShell = Nothing
End Function
Note that this is a user-defined macro that you can use either from the worksheet or from another macro. The use from the worksheet would be as follows:
=GetDesktop()
Another way to determine the path to the desktop is to use the following line in your code:
sPath = Environ("USERPROFILE") & "\Desktop"
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8236) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding the Path to the Desktop.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Want a quick way to convert positive values to negative and vice versa? You can create your own plus/minus button by ...
Discover MoreWhen you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on ...
Discover MoreWrite out a check and you need to include the digits for the amount of the check and the value of the check written out ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-03-17 08:32:03
debriggs
I use the Environ method but test to make sure the path exists
Public Function PathExists(Pathname As String) As Boolean
If Len(Dir(Pathname, vbDirectory)) = 0 Then
PathExists = False
Else
PathExists = True
End If
End Function
2013-08-26 10:38:33
Jonathan Murray
Using the user profile directory and appending desktop will only work as long as Microsoft continues to use that structure, and only when your users haven't moved their 'desktop' folder (i.e. to a network asset to facilitate backups or the use of multiple workstations. The VBA solution seems more reliable.
2013-08-26 08:35:39
Bryan
What's the advantage/disadvantage of each method? Environ is easier to understand since it's a native VBA function, and it seems like it would be quicker since you don't have to dimension, create, and destroy an object (although I do it all on one line so I save a little time there), but I've seen the shell method so much on the internet that it really makes me wonder if it has some hidden advantage. Also, manually adding the "Desktop" makes me worry a bit, as I'm always paranoid that hard-coding a variable will fail in some unknown future.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Comments