macro to save sheet as pdf on desktop regardless of user

[skf786]

Hi,

Want to save active sheet only, want to save as PDF and would like the file to open in pdf once it is saved, on the desktop, regardless of user. Thanks for your help in advance!!

Ive got very very limited know how of <acronym title="visual basic for applications">VBA</acronym>!

 

[CalcSux78]

There are a bunch of "Special Folders" that you can use. Read more here: https://msdn.microsoft.com/en-us/library/0ea7b5xe(v=vs.84).aspx

Sub DoPDF()
Dim sName$
Dim Path$


sName = ActiveSheet.Name


Path = CreateObject("WScript.Shell").specialfolders("Desktop")
On Error Resume Next
ThisWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Path & "\" & sName & ".pdf", _
    OpenAfterPublish:=True
If Err.Number > 0 Then MsgBox "Error saving pdf."
End Sub

 

[Tetra201]

Try this:

Sub Test()
    With ActiveSheet
        .ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
            .Name & ".pdf", OpenAfterPublish:=True
    End With
End Sub

 

[skf786]

calcsux! this is awesome! thanks so much. My desired file name is in cell e5. Plus I would like dd/mm/yyyy in the file name as well.

 

[skf786]

Thanks Tetra 201! My desired file name is in cell e5. Plus I would like dd/mm/yyyy in the file name as well. . Thanks in advance!

 

[Tetra201]

Thanks Tetra 201! My desired file name is in cell e5. Plus I would like dd/mm/yyyy in the file name as well. . Thanks in advance!

Here is the code as requested -- except: you cannot have a "/" in the file name.

Sub ExportPDF()
    With ActiveSheet
        .ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
            .Range("E5").Value & "_" & Format(Date, "dd-mm-yyyy") & ".pdf", OpenAfterPublish:=True
    End With
End Sub

 

[skf786]

Too Good! Thanks Tetra201. this might get me a promotion

 

[Tetra201]

You are welcome.
Good luck.

 

[skf786]

To make it even more user friendly: would like to add an 'email report' button which attaches the active sheet as pdf to a new email. Subject of the email shall be taken from cell p6 in the sheet. email address shall be taken from p7. However I would not like the email to be sent without the user pressing the send button. Would like to allow them to add text of their choice in the email. The email shall go through outlook of the respective users as the file is being shared with users in different locations.

Again, thanks so much for everything!

 

[Tetra201]

To make it even more user friendly: would like to add an 'email report' button... The email shall go through outlook...

At the moment, I am unable to test any Outlook-related code. So, I cannot help you with this project. Hopefully somebody else will chime in on this.