VBA Express : Excel - Get Desktop Address

Excel

Get Desktop Address

Ease of Use

Easy

Version tested with

2000

Submitted by:

mvidas

Description:

Will return the desktop address of the computer running the macro

Discussion:

Helpful if you want to have a macro automatically save a file directly to the desktop, this shows you the code to return the desktop address. Three examples of use are given, one to messagebox the desktop address, one to save a file to the desktop, and one to return the address in a function (which can be helpful for more than one program to utilize it). The attached sample file shows you an example of each of the uses.

Code:

instructions for use

			 'Will create a messagebox with your Desktop Address
Sub MsgboxDesktopAddress() 
    MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop") 
End Sub 
 
 'Use this to save to the desktop. Set to a string variable and add the path separator
Sub SaveToDesktop() 
    Dim DTAddress As String 
    DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator 
    ActiveWorkbook.SaveAs DTAddress & "Your File Name.xls" 
End Sub 
 
 'To be used as a function
Public Function DesktopAddress() As String 
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator 
End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

Go to Tools -> Macros -> Macros...
Select "MsgboxDesktopAddress" and click Run

Sample File:

Get Desktop Address.zip 9.23KB

Approved by mdmackillop

This entry has been viewed 278 times.