Excel 2010 64-bit and VBA

About the programming interface for the virtual PDF printer driver.

Moderator: jr

Excel 2010 64-bit and VBA

Postby mcvickerp » Mon Jan 17, 2011 11:43 pm

I have been trying to convert the example code for excel vba to work in the 64-bit environment. I have gotten past the .GetPrinterName property not working for Bullzip.PDFSettings. I was able to get it from Bullzip.PDFUtil.defaultprintername. I cannot get past getting the Bullzip.PDFSettings.printername . The code below will give an run-time error "-2146233088 (80131500)": This PrinterName property must be set before calling the GetFileSettingsFilePath Method.

Function PrintSheetAsPDF(file_name As String, merge_file_name As String, doc_title As String, author As String, save_path As String, subject_name As String, keywords As String)
Dim obj_printer_settings As Object
Dim obj_printer_util As Object
Dim current_printer As String
Dim progid As String
Dim utilprogid As String
Dim printername As String
Dim full_printer_name As String
Dim New64bitOS As Boolean

New64bitOS = False

#If VBA7 Then
' code running the new vba7 editor
#If Win64 Then
' code is running in 64-bit version of Microsoft office
New64bitOS = True
#End If
#End If

Rem -- Set the program id of the automation object.
If New64bitOS Then
progid = "Bullzip.PdfSettings"
utilprogid = "Bullzip.PDFUtil"
Set obj_printer_util = CreateObject(utilprogid)
Rem -- Create the object to control the printer settings
Set obj_printer_settings = CreateObject(progid)
Rem -- Get default printer name
printername = obj_printer_util.defaultprintername
Else
progid = "Bullzip.PDFPrinterSettings"
utilprogid = ""
Rem -- Create the object to control the printer settings
Set obj_printer_settings = CreateObject(progid)
Rem -- Get default printer name
printername = obj_printer_settings.GetPrinterName
End If

Rem -- Get the full name of the printer
full_printer_name = FindPrinter(printername)
full_printer_name = GetFullNetworkPrinterName(full_printer_name)

Rem -- Prompt the user for a file name
' file_name = InputBox("Save PDF to desktop as:", "Sheet '" & _
' ActiveSheet.Name & "' to PDF...", ActiveSheet.Name)

Rem -- Abort the process if the user cancels the dialog
If file_name = "" Then Exit Function
Rem -- Make sure that the file name ends with .pdf
If LCase(Right(file_name, 4)) <> ".pdf" Then
file_name = file_name & ".pdf"
End If

Rem -- Write the settings to the printer
Rem -- Settings are written to the runonce.ini
Rem -- This file is deleted immediately after being used.
With obj_printer_settings
.SetValue "output", save_path & file_name
.SetValue "showsettings", "never"
.SetValue "ConfirmOverwrite", "no"
.SetValue "ShowPDF", "no"
.SetValue "Target", "prepress"
.SetValue "Author", author
.SetValue "Title", doc_title
.SetValue "Subject", subject_name
.SetValue "Keywords", keywords
.SetValue "UseThumbs", "no"
.SetValue "AutoRotatePages", "all"
.SetValue "Linearize", "yes"
.SetValue "Res", "3600"
If merge_file_name <> "" Then
.SetValue "MergeFile", save_path & merge_file_name
.SetValue "MergePosition", "bottom"
End If
.WriteSettings True
End With

Rem -- Change to PDF printer
current_printer = ActivePrinter
ActivePrinter = full_printer_name

Rem -- Print the active work sheet
ActiveSheet.PrintOut

Rem -- Restore the printer selection
ActivePrinter = current_printer
End Function
mcvickerp
 
Posts: 2
Joined: Mon Jan 17, 2011 11:26 pm

Re: Excel 2010 64-bit and VBA

Postby mcvickerp » Fri Jan 21, 2011 10:44 pm

Got it working in Office 2010 64-bit. Here is my code.

Function PrintSheetAsPDF(file_name As String, merge_file_name As String, doc_title As String, author As String, save_path As String, subject_name As String, keywords As String, filetype As String)
Dim obj_printer_util As Object
Dim obj_printer_settings As Object
Dim current_printer As String
Dim printername As String
Dim full_printer_name As String

Rem -- Get default printer name
Set obj_printer_util = CreateObject("Bullzip.PDFUtil")
printername = obj_printer_util.defaultprintername
Rem -- Create the object to control the printer settings
Set obj_printer_settings = CreateObject("Bullzip.PDFSettings")
obj_printer_settings.printername = obj_printer_util.defaultprintername
obj_printer_settings.LoadSettings (True)

Rem -- Get the full name of the printer
full_printer_name = FindPrinter(printername)
full_printer_name = GetFullNetworkPrinterName(full_printer_name)

Rem -- Prompt the user for a file name
' file_name = InputBox("Save PDF to desktop as:", "Sheet '" & _
' ActiveSheet.Name & "' to PDF...", ActiveSheet.Name)

Rem -- Abort the process if the user cancels the dialog
If file_name = "" Then Exit Function
Rem -- Make sure that the file name ends with .pdf
If LCase(Right(file_name, 4)) <> ".pdf" Then
file_name = file_name & ".pdf"
End If

Rem -- Write the settings to the printer
Rem -- Settings are written to the runonce.ini
Rem -- This file is deleted immediately after being used.
With obj_printer_settings
.SetValue "output", save_path & file_name
.SetValue "showsettings", "never"
.SetValue "ConfirmOverwrite", "no"
.SetValue "ShowPDF", "no"
.SetValue "Target", "prepress"
.SetValue "Author", author
.SetValue "Title", doc_title
.SetValue "Subject", subject_name
.SetValue "Keywords", keywords
.SetValue "UseThumbs", "no"
.SetValue "AutoRotatePages", "all"
.SetValue "Linearize", "yes"
.SetValue "Res", "3600"
If merge_file_name <> "" Then
.SetValue "MergeFile", save_path & merge_file_name
.SetValue "MergePosition", "bottom"
End If
If filetype = "JPEG" Then
.SetValue "Device", "jpeg"
End If
.WriteSettings True
End With

Rem -- Change to PDF printer
current_printer = ActivePrinter
ActivePrinter = full_printer_name

Rem -- Print the active work sheet
ActiveSheet.PrintOut

Rem -- Restore the printer selection
ActivePrinter = current_printer
End Function
mcvickerp
 
Posts: 2
Joined: Mon Jan 17, 2011 11:26 pm

Re: Excel 2010 64-bit and VBA

Postby evos » Wed Feb 22, 2012 3:55 pm

When trying the above code, I'm getting compiler errors that say that FindPrinter and GetFullNetworkPrinterName have not been defined.
Should I declare these functions? If so, how exactly? I can't find any examples on the web.
BTW I'm a newbie on VBA, so I would appreciate if you could spell it out.
(Excel 2010, 64-bit Windows 7)
Last edited by evos on Wed Feb 22, 2012 9:12 pm, edited 1 time in total.
evos
 
Posts: 1
Joined: Wed Feb 22, 2012 3:45 pm

Re: Excel 2010 64-bit and VBA

Postby admin » Fri Feb 24, 2012 8:24 am

The function you need is defined in this example:

http://www.biopdf.com/guide/examples/microsoft_excel/

Here is the code you need:

Function GetFullNetworkPrinterName(NetworkPrinterName As String) As String
Rem -- Returns the full network printer name
Rem -- Returns an empty string if the printer is not found
Rem -- E.g. GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL")
Rem -- Might return "BIOPDF on Ne04:"
Dim sCurrentPrinterName As String
Dim sTempPrinterName As String
Dim i As Long

sCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
sTempPrinterName = NetworkPrinterName & " on Ne" & Format(i, "00") & ":"
On Error Resume Next
Rem -- Try to change to the network printer
Application.ActivePrinter = sTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = sTempPrinterName Then
Rem -- The network printer was found
GetFullNetworkPrinterName = sTempPrinterName
Exit Do
End If
i = i + 1
Loop
Application.ActivePrinter = sCurrentPrinterName
End Function
admin
 
Posts: 58
Joined: Fri Oct 28, 2011 8:09 pm

Excel 2010 64 bit and VBA

Postby Alexiaymej » Mon Mar 05, 2012 9:46 pm

If the file name changes in some predictable way then you can build the directory and file name as a string in the macro and then use that string with Workbook.Open if its an Excel file or OpenTextFile if not.

If you want to have the user select files, then take a look at FileDialog.

[img]http://fotki.serveftp.com/uploads/posts/2012-03/nevyandex.ru_foto-prikoly_marazmy-fevralya_21.jpeg[/img]
итоги февральских моразмов...
Alexiaymej
 
Posts: 1
Joined: Sun Mar 04, 2012 1:48 pm
Location: Россия


Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron