Excel 2010 64-bit and VBA

[mcvickerp]

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]

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

[evos]

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)

[admin]

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

[Alexiaymej]

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 ... ya_21.jpeg[/img]
итоги февральских моразмов...