1

I am trying to find a code that works to convert my excel into PDF (Adobe). So far my research to find the perfect code doesn't work.

So here is how my company manually converts and save at their own file ===>

Click "File" -> Click "Print" -> (configure the Set Up to their own preferences like portrait or landscape etc) -> Click "Print" button -> Select "CutePDFWriter" -> (configure the Properties to their own preferences) -> Click "OK" button -> Click "Save" (to whichever file they want to save in)

Please do help me if you have any sort of knowledge in this. Thank you very very much.

0m3r
  • 12,286
  • 15
  • 35
  • 71
Syukrindang
  • 41
  • 5

1 Answers1

4
Option Explicit
Sub SaveAsPDF()
    Dim FSO As Object
    Dim s(1) As String
    Dim FilePath 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)
            FilePath = Replace(s(0), s(1), ".pdf")

            '// Export to PDF with new File Path
            ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=FilePath, _
            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

To Export the workbook Try changing ActiveSheet To ActiveWorkbook

To Export only multiple sheets selections try using Sheets(Array("Sheet4", "Sheet5"))

Example:

        ThisWorkbook.Sheets(Array("Sheet2", "Sheet3")).Select
        Selection.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FilePath, _
0m3r
  • 12,286
  • 15
  • 35
  • 71
  • Hello @Omar. I've tried to run the coding that you gave me. Thank you so much for your help. However, it has an error. It says "Variable to defined" for xlTypePDF . Is there any way to clear this up ? – Syukrindang Jul 30 '15 at 00:38
  • 2
    I have updated the answer please don't forget to [accept](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) also see [What should I do when someone answers my question?](http://stackoverflow.com/help/someone-answers) – 0m3r Jul 30 '15 at 01:18
  • 1
    How do I convert many excel sheets into one PDF file? As the coding you gave, it works. However, it does the conversion sheet by sheet. – Syukrindang Jul 30 '15 at 08:41
  • How to save to a particular ActiveSheet ? Eg. I have a 5 module that has charts. So is there a way a to convert only this 5 charts into PDF ? @Omar – Syukrindang Aug 06 '15 at 01:01