VBA printing PNG - Dialog PopUp problem

About the programming interface for the virtual PDF printer driver.

Moderator: jr

Post Reply
Posts: 4
Joined: Sat Apr 06, 2013 6:16 am

VBA printing PNG - Dialog PopUp problem

Post by 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


ActivePrinter = sCurrentPrinter

Set oPrinterUtil = Nothing
Set oPrinterSettings = Nothing
End Sub
Site Admin
Posts: 500
Joined: Sun Mar 26, 2006 12:28 pm

Re: VBA printing PNG - Dialog PopUp problem

Post by jr »


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,
Posts: 1
Joined: Thu Jun 27, 2013 2:39 pm

Re: VBA printing PNG - Dialog PopUp problem

Post by glenndm »

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
Bullzip version:

best regards
Site Admin
Posts: 500
Joined: Sun Mar 26, 2006 12:28 pm

Re: VBA printing PNG - Dialog PopUp problem

Post by jr »

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

Post Reply