Create PDF from Excel using VB

TheChos
TheChos used Ask the Experts™
on
I am trying to create a PDF from an Excel spreadsheet.  My code creates a PDF but when I try to open it, I receive an error that the "document is not a suppoted file type or because the file has been damaged".  My active printer (default printer) is currently set to Adobe PDF.

Attached is a snippet of my code.

Thanks in advance.
   Dim oExcel As New Excel.Application
   Dim oWorkbook As Excel.Workbook
   Dim oSheet As Excel.Worksheet
 
   Set oWorkbook = oExcel.Workbooks.Open("c:\temp\temp.xlsx")
   Set oSheet = oWorkbook.Sheets("Sheet1")
   oSheet.PrintOut PrToFileName:="C:\temp\test_pdf.pdf"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Basically there is a utility you can try, from Microsoft, that adds a Save As PDF option to Office, instead of having to use the Print method. You could have more luck with this.


download pdfCreator... (http://sourceforge.net/projects/pdfcreator)
there's a class to use to print excel to pdf...

and ive been using it...


game-master
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!



here are some example how to use it...


http://www.excelguru.ca/node/22


i hope i can give u idea...


game-master
Top Expert 2008
Commented:
If you already have Adobe Acrobat installed then you can use the code below to print to PDF files.

The routine below prints one or more worksheets to a single PDF file. The worksheets are printed to the PDF document in the order specified. The code requires Adobe Acrobat 6.0 or later. See the documentation in the code for how to use the routine.

[Begin Code Segment]

Public Function PrintSheetsToPDF( _
      ByVal SheetsToPrint As Variant, _
      ByVal PDFFilePath As String, _
      Optional ByVal ReorderSheets As Boolean, _
      Optional ByVal Workbook As Workbook _
   ) As Boolean

' Print the specified sheets to a PDF file in the order specified. Requires
' Adobe Acrobat 6.0 and a reference to Acrobat Distiller. Returns True if the
' print was successful, False otherwise.
'
' Syntax
'
' PrintSheetsToPDF(Sheets, PDFFilePath, [ReorderSheets], [Workbook])
'
' SheetsToPrint - Array of sheet names to be printed. The sheets included are
'   sorted in that order and then printed in one print job. When the printing
'   is complete the original order is restored.
'
' PDFFilePath - Full path to the PDF file.
'
' ReorderSheets - Pass True to reorder the sheets to be printed in the order
'   specified, False to not sort. optional. If omitted then False is assumed.
'
' Workbook - The workbook containing the sheets to print. Optional. If ommited
'   then the workbook in which this code resides is assumed.
'
' Example
'
' Print sheets "Sheet4", "Sheet10", and "Sheet1" in that order:
'   PrintSheetsToPDF Array("Sheet4", "Sheet10", "Sheet1"), "C:\Output.PDF"
   
   Dim Errors As Boolean
   Dim OriginalActiveWorksheet As Object
   Dim OriginalOrderNames As Variant
   Dim Index As Long
   Dim PDFDistillerApplication As PdfDistiller
   Dim TempPFFilePathName As String
   Dim PDFLogPathName As String
   Dim Result As Long
   
   ' Normalize the sheets to print parameter
   If Not IsArray(SheetsToPrint) Then SheetsToPrint = Array(SheetsToPrint)
   For Index = LBound(SheetsToPrint) To UBound(SheetsToPrint)
      If TypeName(SheetsToPrint(Index)) = "Worksheet" Then SheetsToPrint(Index) = SheetsToPrint(Index).Name
   Next Index
   
   ' Normalize the output pdf file name
   If LCase(Right(PDFFilePath, 4)) <> ".pdf" Then PDFFilePath = PDFFilePath & ".pdf"
   
   ' Normalize the workbook parameter
   If Workbook Is Nothing Then Set Workbook = ThisWorkbook
   
   ' Save the current active worksheet
   Set OriginalActiveWorksheet = Workbook.ActiveSheet
   
   If ReorderSheets Then
   
      ' Save the current sheet order
      ReDim OriginalOrderNames(1 To Workbook.Sheets.Count)
      For Index = 1 To Workbook.Sheets.Count
         OriginalOrderNames(Index) = Workbook.Sheets(Index).Name
      Next Index
     
      ' Reorder the worksheets
      For Index = UBound(SheetsToPrint) To LBound(SheetsToPrint) Step -1
         If Workbook.Sheets(SheetsToPrint(Index)).Index > 1 Then
            Workbook.Sheets(SheetsToPrint(Index)).Move Before:=Workbook.Sheets(1)
         End If
      Next Index
   
   End If
   
   ' Print the worksheets
   
   TempPFFilePathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "pf"
   PDFLogPathName = Left(PDFFilePath, InStrRev(PDFFilePath, ".")) & "log"
   On Error Resume Next
   Kill TempPFFilePathName
   Err.Clear
   Workbook.Worksheets(SheetsToPrint).PrintOut ActivePrinter:="Adobe PDF", PrintToFile:=True, Collate:=True, PrToFilename:=TempPFFilePathName
   If Err.Number <> 0 Then
      MsgBox "To prevent this error from occurring in the future, open the Properties window for the 'Adobe PDF' printer, click the command button 'Printing Preferences', and uncheck the option 'Do not send fonts to ""Adobe PDF""'. Before the changes will take effect Excel must be quit and restarted."
      Errors = True
   End If
   On Error GoTo 0

   If ReorderSheets Then
      ' Restore the original worksheet order
      For Index = 1 To Workbook.Sheets.Count
         If Workbook.Sheets(OriginalOrderNames(Index)).Index <> Index Then
            Workbook.Sheets(OriginalOrderNames(Index)).Move Before:=Workbook.Sheets(Index)
         End If
      Next Index
   End If
   
   ' Restore the original active worksheet
   OriginalActiveWorksheet.Activate

   If Not Errors Then
      ' Convert the postscript file to .pdf
      Set PDFDistillerApplication = New PdfDistiller
      Result = PDFDistillerApplication.FileToPDF(TempPFFilePathName, PDFFilePath, "")
      On Error Resume Next
      Kill TempPFFilePathName
      If Result = 1 Then Kill PDFLogPathName
      On Error GoTo 0
   End If
   
   PrintSheetsToPDF = Not Errors
   
End Function

[End Code Segment]

Kevin

Author

Commented:
Excellent answer.  Thank you!!!
I've been waiting for this thread to close, so I could throw out a few comments...

game-master, dude, you rock!  I inadvertently happened across this thread today and saw your reference to PDFCreator and its programmability from within Excel VBA.  You solved a problem we've been beating our heads over for about a week now in the office:  applying unique passwords to pdf files created from Excel files.  The passwords were provided by the client (as in "this file's password should be xxx").  Couldn't find an app to do it, and we were looking at manually doing it - for, like, a couple hundred files!  Your links and references were sweet and I now have an install of PDFCreator and the code to finish the project automagically.  Thanks a ton!

zorvek:  Interesting post.  Where does one find reference material for manipulating Acrobat from VBA?  We *do* have three systems with Acrobat (9 Pro) installed - but one of 'em ain't mine.  It would be nice to be able to work with it through Excel VBA if necessary.

Thanks for the postings!  You guys have solved a real headache for me...


CThomp2005,

Im glad i could help on little ways... Unfortunately, my answer was not accepted....:-(




game-master
It was here...  I'll mail you 500 points... :-)

Thanks again.
Top Expert 2008

Commented:
>Where does one find reference material for manipulating Acrobat from VBA?

The API for Acrobat is poorly documented, if it is even documented at all these days. I had to scour the Internet to find the information needed to do what I did, and then a number of hours with trial and error and a lot of testing in a variety of environments.

I tried to implement a solution using Reader's API so that I could print PDF documents when users did not have Acrobat...it was painful and never really worked that well. I ended up purchasing a third party solution.

Kevin


Lol...

How come u mail 500 points....:-)


Well, if I had posted my question, I would have assigned it 500 points, and your answer would have been the solution.. = 500 pts.

Along those lines...have run into a problem using the Excel VBA - PDFCreator connection.  Don't think this is the proper forum to discuss it, though.  Would this be a viable topic in the Excel section?  It's seems more of a "programming PDFCreator" topic than Excel.  I'm thinking of also dropping a line to the gentleman at the site you linked to, and/or possibly the PDFCreator people...

Trying to run a batch process, and it crashes - hanging Excel and PDFCreator....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial