Save a File to the Desktop with VBA

The following is a simple Excel VBA example of copying data from the current workbook and saving the file to the desktop.  The procedure will then save the file and close it leaving the user on the original sheet.

The following YouTube video shows you how to save a file to the desktop with VBA. You don’t really need an Excel file for this video.

 
 

The Save File Process

The following VBA code needs to be placed in a regular VBA module. It will copy the contents of the current region on the active sheet provided it starts in A1 and paste it onto a newly created worksheet, then save that worksheet to the desktop. The more dynamic procedure is in the video and below this VBA process.

Sub SavetoDesktop() 'Save an Excel file to the desktop with the path hard coded.
[A1].CurrentRegion.Copy
Workbooks.Add
[a1].PasteSpecial xlValues
ActiveWorkbook.SaveAs "C:\Users\HYMC\Desktop\XLName.xls" 'Change Excel name to a variable if it suits
ActiveWorkbook.Close False 'don't save see line above.
End Sub


The above is the address of the desktop on my computer;

"C:\Users\HYMC\Desktop\XLName.xls"

So will need to be altered to work on your computer.


Dynamic Desktop Capture


To make the above VBA code more dynamic (no path to the desktop required) we can trap the desktop path using the WScript.Shell command. The following will save the active workbook to the desktop no matter the path.


Sub SavetoDesktop2() 'Excel VBA to save a file to the desktop with a dynamic Path.
Dim Path As String
Range("A1", Range("H" & Rows.Count).End(xlUp)).Copy
Workbooks.Add
[a1].PasteSpecial xlValues
Path = Createobject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & "XL File.xls"
End Sub


It is an improvement as all you really need to worry about now is the Excel file name, the save to desktop procedure is all done for you.