Save Excel Sheet as PDF to user's desktop

[cperrak1]

Hello,

I wrote the code to save my spreadsheet as a PDF file. The Macro is working correctly, but I've been asked to make it dynamic and save it to the desktop regardless of user. Any suggestions?

Sub PrintTRENDPDF()

Worksheets("TREND").Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\nperraki\OneDrive - Myriad Genetics\Desktop\TREND.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Worksheets("MACRO TILES").Activate
Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

 

[Skyybot]

Try this:

Sub PrintTRENDPDF()

Worksheets("TREND").Activate

user = Application.Username

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\" & user & "\nperraki\OneDrive - Myriad Genetics\Desktop\TREND.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Worksheets("MACRO TILES").Activate
Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

 

[cperrak1]

Try this:

Sub PrintTRENDPDF()

Worksheets("TREND").Activate

user = Application.Username

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\" & user & "\nperraki\OneDrive - Myriad Genetics\Desktop\TREND.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Worksheets("MACRO TILES").Activate
Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

Try this:

Sub PrintTRENDPDF()

Worksheets("TREND").Activate

user = Application.Username

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\" & user & "\nperraki\OneDrive - Myriad Genetics\Desktop\TREND.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Worksheets("MACRO TILES").Activate
Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

Hello,

Thank you for the reply. I'm not quite there yet. "C:\Users\nperraki\OneDrive - Myriad Genetics\Desktop..." is the path to the desktop of the computer that I'm working off of. Once the excel sheet is converted to PDF, it needs to be saved down to any user's local desktop. Thanks again.

 

[MARK858]

Try (untested)...

Sub PrintTRENDPDF()
    Dim strFolderName As String


    Worksheets("TREND").Activate
    strFolderName = CreateObject("wscript.shell").Specialfolders("Desktop") & "\TREND.pdf"


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    strFolderName, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=False

    Worksheets("MACRO TILES").Activate
    Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

 

[cperrak1]

Try (untested)...

Sub PrintTRENDPDF()
    Dim strFolderName As String


    Worksheets("TREND").Activate
    strFolderName = CreateObject("wscript.shell").Specialfolders("Desktop") & "\TREND.pdf"


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    strFolderName, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=False

    Worksheets("MACRO TILES").Activate
    Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

Thank you for your reply,

I was able to figure it out!

Sub PrintTRENDPDF()

Dim Ws As Worksheet
Dim sPath As String
Dim sFile As String
Set Ws = ActiveSheet
sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
sFile = "TREND"

Worksheets("TREND").Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sPath & "\" & sFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Worksheets("MACRO TILES").Activate
Worksheets("MACRO TILES").Cells(1, 1).Select

End Sub

 

[MARK858]

Happy that you have it working but the bits in red below (and I have tested now)

sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
sFile = "TREND"

Worksheets("TREND").Activate

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sPath & "\" & sFile

do nothing different to

strFolderName = CreateObject("wscript.shell").Specialfolders("Desktop") & "\TREND.pdf"
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                    strFolderName

EDIT: you are also declaring and setting Ws

Dim Ws As Worksheet
Set Ws = ActiveSheet

and then not using Ws, so those lines are obsolete