Excel to PDF using VBA without Acrobat (answered)


New Member
Jul 31, 2005
I have read a number of the pdf related topics here but have not seen one that answers my needs:

I have a supplier orders data table which I am reporting in a pivot table. I currently have a macro that cycles through each supplier with active orders and copies the pivot table as format and values into a new sheet, saves the sheet using supplier name in the filename, and then emails the sheet to the supplier based on a seperate data table that includes their email maintained manually.

I have now been asked to have this send as pdf rather than an excel sheet. I have the code for the emailing sorted, but I can't get the PDF creation done. The person who will be running the macro doesn't have Acrobat.

Any pointers or suggestions greatly appreciated.

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
There are plenty of good freeware pdf applications available, try a google search (but you do get what you pay for)
Upvote 0
Hi loadimmediate

Welcome to the board!

Click on the Profile link at the top of this page, then scroll right to the bottom of the page that opens to the Avatar Control Panel.


Upvote 0
Thanks for the Help - Here's What I ended up with

Thanks for the help, particularly Dominic's advice which got me going in the right direction.

I had a look at the pdf995 option, but wasn't thrilled with the code or the fact it launches a website every time a pdf is produced, particularly given I would be running 100+ reports per macro run.

Anyway I ended up successfully using the open source PDFCreator: http://sourceforge.net/projects/pdfcreator.

Here's the code I ended up with (I am a bit of a VBA Novice, so the code may be a bit basic, but let me know if I can help with anything further.)

Option Explicit

    Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

' Add a reference to PDFCreator
    Public PDFCreator1 As PDFCreator.clsPDFCreator

Public ReadyState As Boolean, DefaultPrinter As String

Sub Dealer_Report()
' Dealer_Report Macro
' Written by Gary Connolly 05-Sep-2006

' The purpose of this macro is to cycle through dealers with orders
' filtering the Order Pivot Table to each dealer, and printing a
' pdf copy of the file.

' Reference PDFCreator
    Set PDFCreator1 = New clsPDFCreator

' Dim Strings
    Dim PDFFilePath As String
    Dim DealerToGet As String
    Dim Recipient As String
    Dim PDFFilename As String
    Dim FinalRow As Integer
    Dim i As Integer

'String PDFFilepath
    Application.Goto Reference:="Home"
    Application.Goto Reference:="File_Path"
    PDFFilePath = Range("G6").Value
' Refresh Relevant Dealer Order Pivot Tables
    Sheets("Dealer Orders").Select
    ActiveSheet.PivotTables("Dealer Orders").PivotCache.Refresh
    Sheets("Dealers with Orders").Select
    ActiveSheet.PivotTables("Dealers with Orders").PivotCache.Refresh

' String Dealer Related Info
    Sheets("Dealers with Orders").Select
    FinalRow = Range("A15000").End(xlUp).Row
    For i = 5 To FinalRow
    Sheets("Dealers with Orders").Select
    DealerToGet = Range("A" & i).Value
    PDFFilename = Range("C" & i).Value
' Filter Pivot Table to Current Dealer
    Sheets("Dealer Orders").Select
    ActiveSheet.PivotTables("Dealer Orders").PivotFields("Dealer").CurrentPage = DealerToGet
' Print the Excel page to PDF
    PDFCreator1.cGetPDFCreatorPrinters.Item (1)
    With PDFCreator1
   .cOption("UseAutosave") = "1"
   .cOption("UseAutosaveDirectory") = "1"
   .cOption("AutosaveDirectory") = PDFFilePath
   .cOption("AutosaveFilename") = PDFFilename
   .cOption("AutosaveFormat") = 0                            ' 0 = PDF
      End With
      ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
      Sleep 5000
'Loop Back to Select Next Dealers
    Next i
'Finish and Return to Home Cell
    Application.Goto Reference:="Home"
End Sub
Upvote 0
I am testing different pdf conversion methods and the third line in the post above is giving me an error.

Public PDFCreator1 As PDFCreator.clsPDFCreator

"Compile Error: user defined type is not defined"
Upvote 0
Doug -

In the VB Editor:

Tools menu > References

Scroll down the list until you find PDFCreator, and check the box in front of it.
Upvote 0
I am trying to do the same thing, but I do have Acrobat Standard 2.0. How can I modify this code to work with Adobe Acrobat?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back