Jul 12 2022 07:47 AM
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?
Jul 12 2022 08:22 AM
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?
Jul 12 2022 08:25 AM
Jul 12 2022 08:35 AM
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