This code example will show you how to automate PDF printing from a Microsoft Excel macro. The prerequisite for this code example is that 7-PDF Printer has been defined as default printer!
You will see how you can locate the correct printer name and use the COM interface to automate the printer settings. It only shows you the basics, but you can build your own functionality using this example as an inspiration. This examples works on both 32 and 64 bit Windows.
The fairly old version of Microsoft Office 2003 might experience a runtime error while executing the code sample. If this is the case, Microsoft has released a patch for Office 2003. Further information incl. Patchlink can be found here...
With newer versions of Microsoft Office, there should be no runtime errors.
- Sub PrintSheetAsPDF()
- Dim obj_printer_settings As Object
- Dim save_path As String
- Dim file_name As String
- Dim xmldom As Object
- Dim progid As String
- Rem -- Read the info xml
- Set xmldom = CreateObject("MSXML.DOMDocument")
- If Not xmldom.Load(ActiveWorkbook.Path & "\info.xml") Then
- MsgBox "Error loading info.xml from """ & ActiveWorkbook.Path & """.", vbCritical
- Exit Sub
- End If
- Rem -- Get the program id of the automation object.
- progid = xmldom.SelectSingleNode("/xml/progid").Text
- Rem -- Create the object to control the printer settings
- Set obj_printer_settings = CreateObject(progid)
- Rem -- Prompt the user for a file name
- save_path = Environ("USERPROFILE") & "\Desktop\"
- 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 Sub
- 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"
- .WriteSettings True
- End With
- Rem -- Print the active work sheet
- End Sub
|Example file||16.49 KB|