Printing as PDF

[cv...@mycaf.com]

I have a portion of a sheet that I want to print/save as a pdf to a
folder on my hard drive. The folders location is C:\Documents and
Settings\Compaq_Owner\Desktop\Invoices. I also want the file name to
be the value found in cell j19 on the "PO Form" worksheet. Any help?

[Arvi Laanemets]

Hi

Download and install Cute PDF-Writer (http://www.cutepdf.com/)
It installs as a printer. Now, you can write any documents (Excel, Word,
various pictures, web pages, etc. to PDF format. Simply open the document,
select Print from menu, select CutePDF Printer, and when you click on Print
button, a file save dialog opens.

Arvi Laanemets

<cv...@mycaf.com> wrote in message
news:1140463544.1...@g43g2000cwa.googlegroups.com...

[cv...@mycaf.com]

Thank you for the response. However, I already know and have the
capability of saving the worksheet as a PDF, but what I want to do is
record it in a macro so that it will automatically Print/Save and
insert the value in j19 as the filename. I do not want to have to
enter in the information manually every time.

[Pete_UK]

I had a similar requirement some time ago. What I ended up doing was to
save the sheet as an xls file (for which you can build up the filename
as necessary in a macro) and then change the printer to the PDF printer
and print the xls file to this driver (then reset the printer
afterwards). It was not fully automatic, however, as I still had to
confirm manually which folder to save the file in.

Hope this helps.

Pete

[Dave Peterson]

Maybe you could incorporate some of Jim Rech's code:

http://groups.google.co.uk/group/microsoft.public.excel.misc/browse_frm/thread/78a985c366083a6d/47ee5b61dff842df?lnk=st&q=pdf+ini+group%3A*excel*+author%3Arech&rnum=1&hl=en#47ee5b61dff842df

or
http://snipurl.com/mrp0

--

Dave Peterson

[Pete_UK]

Thanks for this link, Dave - I'll study it and see if it is still valid
for what I was doing last November.

Pete

[Michael Bednarek]

These things can not be recorded in macros, you have to cut the
code manually.

Adobe Acrobat offers a COM interface to the VBA programmer; it's pretty
expensive. Alternatively, the latest version of PDFCreator now also
provides a COM interface and the distribution file includes examples.

Here is a skeleton (taken from actual working code, but not tested
in this incarnation):

Option Explicit
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Sub PrtPDFCreator(theObject As Object)
' Print to the PDFCreator printer
' Needs a reference to PDFCreator.exe

Const theFileRoot As String = "C:\Temp\PDFs\" ' Testing (must exist)
Dim outName As String
Dim strOldPrinter As String

' Add a reference to PDFCreator
Dim myPDFCreator As PDFCreator.clsPDFCreator

outName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "-" _
& Application.ActiveSheet.Name & "-" _
& Format((Now), "HHNN-SS") & Format((Timer() * 100) Mod 100, "00") & ".PDF"

Set myPDFCreator = New PDFCreator.clsPDFCreator
With myPDFCreator
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = theFileRoot
.cOption("AutosaveFilename") = outName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

' Perform the print action
strOldPrinter = ActivePrinter
theObject.PrintOut ActivePrinter:="PDFCreator"

' Wait until the print job has entered the queue
Do Until myPDFCreator.cCountOfPrintjobs = 1
DoEvents
Sleep 100
Loop
Sleep 100
myPDFCreator.cPrinterStop = False

' Wait until the PDF file shows up
Do Until Dir(theFileRoot & outName) <> ""
DoEvents
Loop

myPDFCreator.cClose
Set myPDFCreator = Nothing
Sleep 100
DoEvents
ActivePrinter = strOldPrinter
End Sub

Private Sub myPDFCreator_eError()
MsgBox "ERROR [" & myPDFCreator.cErrorDetail("Number") & "]: " _
& myPDFCreator.cErrorDetail("Description"), vbCritical + vbOKOnly, "PrtPDFCreator"
End Sub

Sub testPDFCreator()
Dim mySheet As Worksheet

For Each mySheet In ActiveWorkbook.Sheets
mySheet.Activate
Call PrtPDFCreator(mySheet)
Next mySheet
MsgBox "Done."
End Sub

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"