VBA to Save Workbook as pdf

**Jerums** · 05-09-2012, 02:57 PM

I want to put a button on my sheet that will save the entire workbook as a pdf. I want it to save in the same directory in which the excel file is located and I want it to save with the same file name as the excel file. I am sure this is possible and I wouldn't imagine it would be very difficult but I am have a difficult time with it. Any help would be appreciated.

Thanks!

**Paul** · 05-09-2012, 03:09 PM

Hi Jerums,

I'd suggest recording a macro of you saving the workbook as a pdf, and then checking the code it creates. You'll be able to use most of it as recorded, but tweak a few bits relating to the directory path and workbook name.

Let us know how that goes...

**rcharters** · 05-09-2012, 03:41 PM

Try one of these depending on your needs:

Public Sub SaveWorksheetAsPDF()
    'If the sheet has no data an error will occur.
    sFileName = "C:\Test.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Public Sub SaveWorkbookAsPDF()
    'If the sheet has no data an error will occur.
    sFileName = "C:\Test.pdf"
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

**Jerums** · 05-09-2012, 04:43 PM

Thanks guys. I am almost there I think. What I have works, it just saves the pdf with a .xls in the file name. ie. My file name is Test results. My code will create a pdf titled "Test Results.xls.pdf". This will open properly and I have no problems with it, but it would be nice if there was a way to drop the ".xls" on the pdf file. Take a look at my code below and let me know if you have any ideas.

Sub Create_PDF()
'

Application.ScreenUpdating = False


Dim MyFullName As String
MyFullName = ThisWorkbook.FullName

        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        MyFullName, Quality:=xlQualityMinimum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True


Application.ScreenUpdating = True
End Sub

Thanks,
Jeremy

**Paul** · 05-09-2012, 04:53 PM

Jeremy, try:

Sub Create_PDF()
Dim MyFullName As String

Application.ScreenUpdating = False
MyFullName = Left(ThisWorkbook.FullName, InStr(1, ThisWorkbook.FullName, ".") - 1)
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFullName, _
  Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=True
Application.ScreenUpdating = True
End Sub