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 it to the desktop. The procedure will then save the file and close it leaving the user on the original sheet. The following needs to be placed in a regular VBA module.
ActiveWorkbook.SaveAs "C:\Users\HYMC\Desktop\XLName.xls" 'ChngeExcel name to variable
ActiveWorkbook.Close False 'don't save see line above.
The above is the address of the desktop on my computer;
So will need to be altered to work on your computer.
Dynamic Desktop Capture
To make the above code more dynamic we can trap the desktop path. The following will save the active workbook to the Desktop.
Dim Path As String
Path=Createobject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & "XL File.xls"
It is an improvement as all you really need to worry about now is the XL file name.