VBA printing PNG - Dialog PopUp problem

[louiekw]

Dear All,

I have written a VBA to print 3 pages of Excel Sheet to PNG files. However, sometimes the print dialog is displayed after printing the 3 excel sheets. I cannot discover the triggering conditions. 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

[jr]

Hi,

The thing is that you create a settings file for the printer. The settings are picked up when the next print job is processed. If you overwrite your settings file before it is picked up then your settings and your print jobs will go out of sync. Posting a job to the print queue and the actual printing are async processes.

You must wait for the print job to be picked up before you write your next settings file as shown in this example:

http://www.biopdf.com/guide/examples/mi ... ll_sheets/

If you are suddenly missing a settings file then it will default to the normal settings, which normally shows a dialog.

Best regards,
Jacob

[glenndm]

Hi,
I struggle with the same problem.
The solution Jacob suggests, relies on the creation of a statusfile and, in code, on the class PDFUtil.

Could it be this PDFUtil class is not present in the freeware PDF Printer?
In vba, I only have a PFDPrinterSettings class.
The referenced dll : bzpdfc.dll with internal version 9.2.0.54
Bullzip version: 9.8.0.1599

best regards
glenn

[jr]

None of these classes are left out of the freeware version. All this should be possible with the free version

/Jacob