write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Tuesday, 31 January 2012

Excel : Create PDF from excel worksheet

you can use below code  for Excel to PDF conversion


Sub CreatePDF()
    Dim wksSheet As Worksheet
    Dim blnFlag As Boolean
    Dim intI As Integer
    Dim intResult As Byte
 
    intI = 0
    intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0 For Active Worksheets")
    If intResult = 0 Then
    Set wksSheet = ActiveSheet
            wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    ThisWorkbook.Path & "\" & wksSheet.Name, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                    False
            Exit Sub
    End If
    For Each wksSheet In ThisWorkbook.Worksheets
        If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then
            If wksSheet.Visible = xlSheetHidden Then
                wksSheet.Visible = xlSheetVisible
                blnFlag = True
            End If
            wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    ThisWorkbook.Path & "\" & wksSheet.Name, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                    False
                    intI = intI + 1
            If blnFlag = True Then
            wksSheet.Visible = xlSheetHidden
            blnFlag = False
            End If
        End If
    Next
    MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation
   End Sub

1 comment: