Excel to PDF using VBA without Acrobat (answered)

[greeman]

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.

 

[SteveO59L]

There are plenty of good freeware pdf applications available, try a google search (but you do get what you pay for)

 

[DominicB]

Good morning greeman

Does this thread from 2004 help?

http://www.mrexcel.com/board2/viewtopic.php?t=119332

You WILL need a third party application to convert from Excel to PDF - but not necessarily Adobe. The one recommended in the baove thread is free.

HTH

DominicB

 

[loadimmediate]

how can I set an avatar on this chat board??......

 

[DominicB]

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.

HTH

DominicB

 

[greeman]

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

' CYCLE THROUGH DEALERS
' 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
   .cClearCache
      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

 

[Doug.T]

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"

 

[Jon Peltier]

Doug -

In the VB Editor:

Tools menu > References

Scroll down the list until you find PDFCreator, and check the box in front of it.

 

[moneytastesbad]

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?

 

[moneytastesbad]

Oops... I meant to say Acrobat Standard 6.0