Here's an example of BullZip PDF generation in VBA (Excel)

About the programming interface for the virtual PDF printer driver.

Moderator: jr

Post Reply
jce
Posts: 2
Joined: Wed May 02, 2007 10:22 pm

Here's an example of BullZip PDF generation in VBA (Excel)

Post by jce »

Here's an example of a BullZip PDF generation macro in VBA for Excel, if anyone's interested.

[code]
Sub PrintSheetAsPDFwithBullZip()
'must add a reference to BullZip
Dim myobject As New Bullzip.PDFPrinterSettings
Dim SavePath As String, FileName As String
SavePath = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop\"
FileName = InputBox("Save PDF to desktop as:", "Sheet '" & ActiveSheet.name & "' to PDF...", ActiveSheet.name)
If LCase(Right(FileName, 4)) <> ".pdf" Then FileName = FileName & ".pdf"

'see default settings in
'Environ("HOMEDRIVE") & Environ("APPDATA") & "\Bullzip\PDF Printer\settings.ini"
myobject.SetValue "output", SavePath & FileName
myobject.SetValue "showsettings", "never"
myobject.WriteSettings (True) 'writes the settings in a runonce.ini that it immediately deleted after being used.

'change to bullzip printer...
If InStr(ActivePrinter, "BullZip") = 0 Then
Dim storeprinter$, PrinterChanged As Boolean
PrinterChanged = True
storeprinter = ActivePrinter
ActivePrinter = GetFullNetworkPrinterName("BullZip")
End If

ActiveSheet.PrintOut

If PrinterChanged Then ActivePrinter = storeprinter
End Sub

'the following code is from http://www.erlandsendata.no/english/ind ... ngeprinter
Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL")
' might return "HP LaserJet 8100 Series PCL on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
' the network printer was found
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
' remove the line below if you want the function to change the active printer
Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function
[/code]
whbowers
Posts: 1
Joined: Mon May 07, 2007 8:34 pm

VBA Code

Post by whbowers »

jce,

Thanks very much for posting this. I just upgraded BullZIP today to use the API and found the documentation wasn't too comprehensive. I adapted your code for an Access VBA module and saved one heck of a lot of time. Thanks again.

Bill
GaryPod
Posts: 1
Joined: Wed Jun 27, 2007 4:16 pm

Re: VBA Code

Post by GaryPod »

[quote="whbowers"]jce,

Thanks very much for posting this. I just upgraded BullZIP today to use the API and found the documentation wasn't too comprehensive. I adapted your code for an Access VBA module and saved one heck of a lot of time. Thanks again.

Bill[/quote]

Hey Bill I've taken the code from above and have it writing runone.ini from ms access fine but can't figure out how to actually print the file / call the print process from ms access...

any chance you could give me some pointers?

Cheers
jce
Posts: 2
Joined: Wed May 02, 2007 10:22 pm

VBA Printing

Post by jce »

Here's a link where you can enumerate your printers:
http://support.microsoft.com/kb/q166008/

Then you have to add " on Ne??:" where ?? is the number of the printer in the order that they are enumerated in. (01, 02, etc.)

Once you have an enumerated list of all your printers' full names in a VBA array, you can find which one is bullzip using the InStr function. Then use activeprinter=<<full name of bullzip printer>> to set it to print to bullzip.

This is how I do it in VBA in Excel. I don't know how Access is different. Try typing debug.print activeprinter in the immediate window and see what comes up.

You could skip all the complicated printer enumerating and just find the bullzip printer's name by making it the active printer and then typing debug.print activeprinter, then hard coding that in, but if you ever add or delete a printer, that final " on Ne??:" number will change.

Sorry I don't have time to post any more.
cwany
Posts: 4
Joined: Fri Feb 29, 2008 10:51 am
Location: Poland
Contact:

Post by cwany »

btw "activeprinter" feature is some office 2k+? I use office 97 (imagine!), and don't seem to have this one?
Post Reply