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
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
Code:
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