Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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.
Written by Allen Wyatt (last updated November 28, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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, 2016, 2019, and Excel in Microsoft 365. 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, 2016, 2019, and Excel in Microsoft 365. 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!
Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros ...
Discover MoreIf you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in ...
Discover MoreThe macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-02-17 06:56:21
Tom
i used Environ("USERPROFILE") & "\Desktop" all the time, but with the introduction of onedrive and the Desktop moved there that doesn't work anymore.
so your 'GetDesktop' is a nice function that always works!
2022-12-07 11:26:42
J. Woolley
The ListSpecialFolders function described in my previous comment below has been updated with 3 additional Windows special folders. It now returns 55 rows plus the optional header row.
2022-12-06 14:24:39
J. Woolley
My Excel Toolbox's VBAResult function will return the result of a VBA expression. For example, this cell formula will return the path to the desktop as indicated in the Tip:
=VBAResult("Environ(""USERPROFILE"") & ""\Desktop""")
My Excel Toolbox's ListSpecialFolders dynamic array function will return 7 Excel special folders plus 45 Windows special folders, including the desktop's path (and others listed by Rick Rothstein in his comment below):
=ListSpecialFolders([SkipHeader])
Expect 2 columns (Acronym, Folder) and 52 rows plus the optional header row.
In older versions of Excel that do not support dynamic arrays, you can preselect an appropriate 2x52 (plus header) range and enter the formula as a CSE (Ctrl+Shift+Enter) array. Or you can use My Excel Toolbox's SpillArray function to simulate a dynamic array:
=SpillArray(ListSpecialFolders())
Finally, the following cell formula will return the path to the desktop:
=VLOOKUP("Windows MyDesktop",ListSpecialFolders(),2,FALSE)
See https://sites.google.com/view/MyExcelToolbox
2020-07-11 10:47:21
Rick Rothstein
You have given the way to use the Windows Scripting Host to find the path to the Desktop, but you can find the path to other special folders using it as well. Here is a list of special folder names whose paths you can find using Windows Scripting Host...
AllUsersDesktop
AllUsersStartMenu
AllUsersPrograms
AllUsersStartup
Desktop
Favorites
Fonts
MyDocuments
NetHood
PrintHood
Programs
Recent
SendTo
StartMenu
Startup
Templates
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 © 2023 Sharon Parq Associates, Inc.
Comments