Worksheets to PDF via AdobePS Adobe Distiller?

[XL-Dennis]

This message was edited by XL-Dennis on 2002-11-15 20:01

 

[Rylandir]

I do this daily.

Have several Excel "reports" that I run every morning (it updates sales figures by running several queries), and then "print" out to our Intranet as a .PDF file using Adobe Distiller. Works like a charm.

 

[WillR]

and me.... love it. Is there anything specific you want to know??

 

[XL-Dennis]

This message was edited by XL-Dennis on 2002-11-15 20:02

 

[Rylandir]

Hehe ... reading code with messages in another verbal language is ... interesting. :b

Unfortunately, I have only printed to Ne01: and via Excel instead of VBA. So I wouldn't know what your missing re: the Adobe-PS driver.

 

[Juan Pablo González]

Dennis, I'm *trying* to work on an utility that converts both Excel workbooks and Word documents to PDF, and then merges them. So far, I haven't been able to actually "print" using early binding, but the merge code works perfectly using that. That means that I use the same method that you ("Acrobat Distiller on Ne00:", but sometimes, on other computers, the Ne is 01, not 00, which begins to causes some issues...

If you're interested I'll put the merging code here, to see if that leads you somewhere... (My comments are in Spanish )

 

[XL-Dennis]

Juan,

It will be highly appreciated to take part of Your work

Looking forward!

TIA,
Dennis

 

[Juan Pablo González]

Dennis, to run it:

mergePDFFiles "C:Archivos de ProgramaAdobeAcrobat 4.0PDF Output", TDir & TFile, PDFList()

where the first parameter is the directory that the .pdf files are located, the second one is the destination file, and the third one is the list of files (.pdf's as well) (Now that I look at it, i should take another look at it !)

Look at this look too:
http://www.mrexcel.com/board/viewtopic.php?topic=9295&forum=2

Here's the function (It's in a module all by itself...) Note I didn't write this, made a few adjusts, but I found this code on the net.

<pre>''Standard module
Public PDFList() As String
Public cntFiles As Integer

'/ This project has referenced (Tools>References) the Acrobat 4.0
'/ Object Type Library - if you reuse the code ensure you make the reference to it.

Public Sub mergePDFFiles(inDirectory As String, outMergeFile As String, fileList() As String)

Dim AcroApp As CAcroApp
Dim PDDoc As CAcroPDDoc
Dim InsertPDDoc As CAcroPDDoc
Dim iNumberOfPagesToInsert As Integer, iLastPage As Integer

Set AcroApp = CreateObject("AcroExch.App")
Set PDDoc = CreateObject("AcroExch.PDDoc")
Set InsertPDDoc = CreateObject("AcroExch.PDDoc")

'/ Is there a trailing backslash on the inDirectory?
If Right(inDirectory, 1) <> "" Then
inDirectory = inDirectory & ""
End If

'/ Hide the Acrobat window
AcroApp.Hide

'/ Create a Blank PDDoc (The Merge File) - Alterations courtesy of KDA.
If PDDoc.Create = False Then
MsgBox "Creation not successful"
End
End If

For goRound = LBound(fileList()) To UBound(fileList())

' Get the total pages less one for the last page num [zero based]
iLastPage = PDDoc.GetNumPages - 1
'/If (iLastPage = -1) Then iLastPage = 0

If fileList(goRound) = "" Then
Exit For
End If

'/ Using the fileList Array access the first index member
If InsertPDDoc.Open(fileList(goRound)) = False Then
'/ MsgBox "Error Opening The File To Insert - " & fileList(goRound), , "MergePDFs - Command Line"
Exit Sub
End If

'/ Get the number of pages to insert from the Current InsertPDDoc object
iNumberOfPagesToInsert = InsertPDDoc.GetNumPages

strOutput = "Go Round Counter for fileList array: " & goRound & vbCrLf & _
"File List Array Item: " & fileList(goRound) & vbCrLf & _
"Insert Document: " & InsertPDDoc.GetFileName & vbCrLf & _
"Last Page Number: " & iLastPage & vbCrLf & _
"Number of Pages to Insert: " & iNumberOfPagesToInsert & vbCrLf & _
"File Name of PDDoc: " & PDDoc.GetFileName & vbCrLf & _
"Directory of In Files: " & inDirectory & vbCrLf & _
"Title from Insert Doc: " & InsertPDDoc.GetInfo("Title") & vbCrLf & _
"Number of Files in Array: " & cntFiles

'/ Insert the pages
If PDDoc.InsertPages(iLastPage, InsertPDDoc, 0, iNumberOfPagesToInsert, True) = False Then
'/MsgBox "Error Inserting Pages - " & inDirectory & fileList(goRound), , "MergePDFs - Command Line"
Exit Sub
End If

' Close the document without saving
If InsertPDDoc.Close() = False Then
'/MsgBox "Error Closing Insert Document - " & fileList(goRound), , "MergePDFs - Command Line"
Exit For
End If

Next
'/ Save the File Optimized
If (PDDoc.Save(&H5, outMergeFile)) = False Then
MsgBox "Error Saving The Merged Document - " & outMergeFile, , "MergePDFs - Command Line"
Exit Sub
End If

' Close the PDDoc
If PDDoc.Close() = False Then
MsgBox "Error Closing The Merged Document - " & fileList(goRound), , "MergePDFs"
Exit Sub
End If
' Close Acrobat Exchange
AcroApp.Exit

Set AcroApp = Nothing
Set PDDoc = Nothing
Set InsertPDDoc = Nothing

'/MsgBox "Merge Completed", vbOKOnly, "MergePDF's - Command Line Version"
Exit Sub

ErrorHandler:
MsgBox Err.Description, , "MergePDFs"
Err.Clear
Exit Sub
End Sub</pre> If you need something else, give me a shot ! (And later, when I can retake the program, I'll take your word for the beta testing...)

 

[XL-Dennis]

Juan,

First of all - Thank You very much for providing me with this function

Q1: Have You tested it with Adobe 5.0 and 5.05?

Tomorrow (my local time) I will explore it in more details.

when I can retake the program, I'll take your word for the beta testing...

Well, someone have to play the devil's laywer
...and since You already planning for a retake it will also be very expensive

BTW, ask Your colleague Ivan - He has already some experience

Have a nice weekend,
Dennis

 

[Juan Pablo González]

Dennis, nope, only 4.0 around here.

And LOL !