True PDF Software
for you!

  • Deutsch
  • English

Automate PDF Creation from Excel Macros

This code example will show you how to automate PDF printing from a Microsoft Excel macro.

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.

  1. Sub PrintSheetAsPDF()
  2.     Dim obj_printer_settings As Object
  3.     Dim save_path As String
  4.     Dim file_name As String
  5.     Dim xmldom As Object
  6.     Dim progid As String
  7.    
  8.     Rem -- Read the info xml
  9.     Set xmldom = CreateObject("MSXML.DOMDocument")
  10.     If Not xmldom.Load(ActiveWorkbook.Path & "\info.xml") Then
  11.         MsgBox "Error loading info.xml from """ & ActiveWorkbook.Path & """.", vbCritical
  12.         Exit Sub
  13.     End If
  14.    
  15.     Rem -- Get the program id of the automation object.
  16.     progid = xmldom.SelectSingleNode("/xml/progid").Text
  17.    
  18.     Rem -- Create the object to control the printer settings
  19.     Set obj_printer_settings = CreateObject(progid)
  20.        
  21.     Rem -- Prompt the user for a file name
  22.     save_path = Environ("USERPROFILE") & "\Desktop\"
  23.     file_name = InputBox("Save PDF to desktop as:", "Sheet '" & _
  24.         ActiveSheet.Name & "' to PDF...", ActiveSheet.Name)
  25.    
  26.     Rem -- Abort the process if the user cancels the dialog
  27.     If file_name = "" Then Exit Sub
  28.    
  29.     Rem -- Make sure that the file name ends with .pdf
  30.     If LCase(Right(file_name, 4)) <> ".pdf" Then
  31.         file_name = file_name & ".pdf"
  32.     End If
  33.    
  34.     Rem -- Write the settings to the printer
  35.     Rem -- Settings are written to the runonce.ini
  36.     Rem -- This file is deleted immediately after being used.
  37.     With obj_printer_settings
  38.         .SetValue "output", save_path & file_name
  39.         .SetValue "showsettings", "never"
  40.         .WriteSettings True
  41.     End With
  42.    
  43.     Rem -- Print the active work sheet
  44.     ActiveSheet.PrintOut
  45.  
  46. End Sub
  47.  
AttachmentSize
Package icon Example files16.49 KB