Using VBA to intercept PDF's Save file dialog box

[Bill Agee]

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???????

 

[Vasant Nanavati]

Not quite sure what you mean. Do you want to copy an existing PDF file into
another location? Or move it?

If you mean an open but unsaved/unnamed PDF file, I don't think you can save
it using VBA.

 

[William]

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("Desktop") & "\"
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

(e-mail address removed)

| 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???????
|
|