vba code save to any desktop on any pc

Copper Contributor

HI I have a code that prints a pdf to my desktop and also creates a copy of the workbook on the desktop. the issue is the code specifically points to my user name and desktop. How do I change the path to go to any users desktop when they run the same code? 

4 Replies

@wishicouldcode 

The path of the user's desktop on Windows is returned by

 

CreateObject("WScript.Shell").SpecialFolders("Desktop")

 

Is that enough or do you need more details?

Yeah if you have the whole code how to do that for pdf and excel that would be great. It doesnt seem as simple as just typing the file path.

Im using this command for exporting the workbook wb.SaveAs
and this one for the pdf
Dim pdfName As String, FullName As String, sht As Worksheet and fullname path

@wishicouldcode 

Try these:

Sub SaveWorkbook2Desktop()
    Dim Wb As Workbook
    Dim sPath As String
    Dim sFile As String
    Set Wb = ActiveWorkbook ' or a specific open workbook
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sFile = Wb.Name ' or specify another name
    Wb.SaveAs Filename:=sPath & "\" & sFile
End Sub

Sub SavePDF2Desktop()
    Dim Ws As Worksheet
    Dim sPath As String
    Dim sFile As String
    Set Ws = ActiveSheet ' or a specific sheet
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sFile = Ws.Name ' or specify another name
    Ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & "\" & sFile
End Sub
thanks for your time here.