Excel VBA- Create PDF Report

By Ajeet Kumar

EXCEL VBA TO GENERATE PDF FILE FROM A WORKSHEET

Following logical steps are used in the following procedure:

1. An instance of Word Application Object is created using late binding.
2. An instance of Word Document is created and added to Documents Collection.
3. The instance of Word Documents is made visible.
4. The target range of worksheet is copied and pasted to the Word Document.
5. The Word Table is formatted using the Enumeration constants in Numeric terms.
6. The Document is saved as PDF File.
7. The Document is closed.
8. The Word Application is closed.
9. The Word Application Object is set to Nothing to release the memory.

---------------------------------------------------------------------------------------------

Sub pGenPDF()
    Dim objWApp As Object
    Dim strPath As String
    
    On Error GoTo lblPDF
    strPath = ThisWorkbook.Path & Application.PathSeparator
    Set objWApp = CreateObject("WORD.Application")

    With objWApp
        .Documents.Add
        .Visible = True
    End With
    
    Range("A1:D200").Copy

    objWApp.ActiveDocument.Content.Paste
    Application.CutCopyMode = False
    objWApp.Documents(1).Tables(1).PreferredWidth = 0
    objWApp.Documents(1).Tables(1).Rows.Alignment = 1
    objWApp.ActiveDocument.SaveAs2 strPath & "MyPDF.pdf", 17
    objWApp.ActiveDocument.Close SaveChanges:=0
        
    objWApp.Quit
    Set objWApp = Nothing
    MsgBox "PDF File Generated.", vbInformation
    
lblPDF:
    If err.Number <> 0 Then
        MsgBox "Error Number: " & err.Number & vbCrLf & "Error Description: " & err.Description
    End If
Application.ScreenUpdating = True
End Sub

---------------------------------------------------------------------------------------------