Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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 September 9, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Finding the Path to the Desktop.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Using a macro to add worksheets to your workbook is easy. This tip provides two different methods you can use.
Discover MoreOn your system you may have workbooks that contain macros you know are safe to use. Microsoft provides two things you can ...
Discover MoreEver want to have Excel run a procedure whenever you open a workbook? It's not as difficult as you might think. Here's how.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-09-09 10:56:20
J. Woolley
My Excel Toolbox's NameOf function can return the value of an environment variable. For example, this cell formula will return the path to your desktop as indicated in the Tip:
=NameOf("UserProfile")&"\Desktop"
For more about the NameOf function, see my earliest comment here:
https://excelribbon.tips.net/T013432_Inserting_the_Workbook_Name.html
For more about environment variables, see my earliest comment here:
https://excelribbon.tips.net/T013227_Adjusting_a_Path_Based_on_System_and_User.html
My Excel Toolbox's ListSpecialFolders dynamic array function will return 7 Excel special folders plus 48 Windows special folders, including the desktop's path:
=ListSpecialFolders([SkipHeader])
Expect 2 columns (Acronym, Folder) and 55 rows plus the optional header row.
In older versions of Excel that do not support dynamic arrays, you can preselect an appropriate 2x55 (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())
If you only want the path to your desktop, use the following cell formula:
=VLOOKUP("Windows MyDesktop",ListSpecialFolders(),2,FALSE)
See https://sites.google.com/view/MyExcelToolbox
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