Global Save as PDF

[kgkev]

I found the following code on this site that appears to do the job I need. However I want to add this workbook to my "personal" sheet and run it from the ribbon.

I get the correct PDF document but the name and filepath are those of the personal sheet.


how do I get around this so I can save any excel sheet to PDF?

Original posted by Xenou
http://www.mrexcel.com/forum/excel-questions/521111-macro-save-pdf-excel-2010-a.html

    Sub Save_as_pdf()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String


    Set FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName
    
    If FSO.FileExists(s(0)) Then
        '//Change Excel Extension to PDF extension in FilePath
        s(1) = FSO.GetExtensionName(s(0))
        If s(1) <> "" Then
            s(1) = "." & s(1)
            sNewFilePath = Replace(s(0), s(1), ".pdf")
            
            '//Export to PDF with new File Path
            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sNewFilePath, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=True
        End If
    Else
        '//Error: file path not found
        MsgBox "Error: this workbook may be unsaved.  Please save and try again."
    End If
    
    Set FSO = Nothing


End Sub

 

[VoG]

I think this should do it

s(0) = ActiveWorkbook.FullName