Excel to PDF using VBA without Acrobat (answered)

greeman

New Member
Joined
Jul 31, 2005
Messages
38
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
There are plenty of good freeware pdf applications available, try a google search (but you do get what you pay for)
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569

ADVERTISEMENT

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

New Member
Joined
Jul 31, 2005
Messages
38
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.)

Code:
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

Code:
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

Board Regular
Joined
Sep 19, 2006
Messages
80

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Aug 28, 2006
Messages
106
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?
 

Forum statistics

Threads
1,141,847
Messages
5,708,946
Members
421,601
Latest member
Garlo

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
Top