Forum Discussion

wishicouldcode's avatar
wishicouldcode
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 path to go to any users desktop when they run the same code? 

Macros and VBA

4 Replies

  • HansVogelaar's avatar
    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's avatar
      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's avatar
        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

Resources