Forum Discussion

Copper Contributor

Jul 12, 2022

vba code save to any desktop on any pc

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 pat...

Macros and VBA

HansVogelaar

MVP

Jul 12, 2022

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?

wishicouldcode

Copper Contributor

Jul 12, 2022

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

HansVogelaar

MVP

Jul 12, 2022

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

wishicouldcode
Copper Contributor
Jul 12, 2022
thanks for your time here.