Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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, 2013, and 2016. 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"
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8236) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Finding the Path to the Desktop.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
One of the things you can do with macros is to work with disk files. As you do so, you may have a need to create a new ...
Discover MoreExcel allows you to fill a cell's background with just about any color you want. If you need to determine the RGB value ...
Discover MoreYou can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's ...
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