Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later 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 (8233) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Finding the Path to the Desktop.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to specify which directory on your hard drive should be used by a macro? It's easy to do using the ChDir command.
Discover MoreWorkbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might want ...
Discover MoreIf you have a macro that takes a long time to process a workbook, you might want to continue working in Excel while the macro ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-08-20 05:10:32
Mehdi
Thanks
2015-10-14 12:59:46
Good very usefull thnks guys
2015-10-13 09:45:14
Keith
Awesome Tip!
Small issue. I needed to add an ending slash before adding the file name. But it was wonderful.
sPath = Environ("USERPROFILE") & "Desktop"
2015-10-09 07:26:55
Calvin
Thanks Allen! Helped a lot
2015-10-06 15:41:26
Carlos
Thanks from Brazil!
2015-07-21 15:01:40
Daniel
I used this part:
sPath = Environ("USERPROFILE") & "Desktop"
and it worked great for my devices! Thank you so much!
2014-11-12 14:11:01
Diana
Just what I need :)
thanks
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments