BullZip Home

Dear All,

I have written a VBA to print 3 pages of Excel Sheet to PNG files. However, the print dialog is displayed after printing the 3 excel sheets. Sometimes the last page png is not export. What is the problem? Deallocation of Object? or any other issue?

Private Sub PrintSheets()

PrintPNG "FileName1.png", Path, "Sheet1"
PrintPNG "FileName2.png", Path, "Sheet2"
PrintPNG "FileName3.png", Path, "Sheet3"
END sub

Public Sub PrintPNG2(FileName As String, SavePath As String, SheetName As String)
Dim oPrinterSettings As Object
Dim oPrinterUtil As Object
Dim sFolder As String
Dim sCurrentPrinter As String
Dim xmldom As Object
Dim sProgId As String
Dim sPrintername As String
Dim sFullPrinterName As String

Set oPrinterSettings = CreateObject("Bullzip.PdfSettings")
Set oPrinterUtil = CreateObject("Bullzip.PdfUtil")

sPrintername = oPrinterUtil.DefaultPrintername
oPrinterSettings.PrinterName = sPrintername

Rem -- Get the full name of the printer
sFullPrinterName = FindPrinter(sPrintername)
sFullPrinterName = GetFullNetworkPrinterName(sFullPrinterName)

With oPrinterSettings
.SetValue "Device", "png16m"
.SetValue "Output", SavePath & FileName
.SetValue "ShowSettings", "never"
.SetValue "ShowPDF", "no"
.SetValue "ShowSaveAs", "never"
.SetValue "ShowProgressFinished", "no"

.SetValue "ConfirmOverwrite", "no"
.WriteSettings True
End With

sCurrentPrinter = ActivePrinter
ActivePrinter = sFullPrinterName

ActiveWorkbook.Sheets(SheetName).PrintOut

ActivePrinter = sCurrentPrinter

Set oPrinterUtil = Nothing
Set oPrinterSettings = Nothing
End Sub

Anyone can help to solve this problem?