ExcelBanter - Using VBA to intercept PDF's Save file dialog box

Hi Bill

I'm assuming you have the Acrobat writer as well as the reader and you want
to print an Excel workbook into a "pdf" format.

Firstly, go to the site below, download the Ghostscript and GSView files and
follow the general setup procedures outlined on that site.
http://www.rcis.co.za/dale/info/pdfguide.htm

Once you have done that, you can convert an Excel file to a pdf file with a
specified name and path using code something like the following.....

Sub PrintWorkbookAsPDF()
'Need to set a reference to Acrobat Distiller
Dim PSFileName As String
Dim PDFFileName As String
Dim myPDF As PdfDistiller
Dim x As String, y As String
Dim iprinter As String
iprinter = Application.ActivePrinter
Application.ActivePrinter = "Acrobat Distiller on Ne02:"

'Set the name and path of the pdf file
If ActiveWorkbook.Path = "" Then
x = CreateObject("WScript.Shell").SpecialFolders("Desk top") & "\"
y = ActiveWorkbook.Name
Else
x = ActiveWorkbook.Path & "\"
y = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
End If

' Print the Excel workbook to the postscript file
PSFileName = x & y & ".ps"
PDFFileName = x & y & ".pdf"
ActiveWorkbook.PrintOut , prtofilename:=PSFileName

' Convert the postscript file to .pdf
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

'Kill the postscript file
Kill (PSFileName)

'Reset original pinter
Application.ActivePrinter = iprinter

'Open the pdf file - amend path as necessary
'If text wraps, the following is one line
Shell "C:\Program Files\Adobe\Acrobat 6.0\Reader\AcroRd32.exe " +
PDFFileName, 1

End Sub

--
XL2002
Regards

William

"Bill Agee" wrote in message
k.net...
| I would like to use VBA to automatically store a PDF file with a internal
| name into a specified directory without having to use the PDF "Save"
button
| to save a file into a directory. Any helpful ideas???????
|
|