Hi,
I found this discussion between RC and Darcy from July last year in Excel
General Questions with the header €śMacro printing to a file€ť and I managed to
solve the PDF saving problem. The original suggestion was to save numerous
files, Im saving only one at a time. Im running Excel 2003 and Acrobat 6.0.
The trick to get rid of the saving prompt was to change the printer
properties in the Control Panel €“ Printers and Faxes €“ Adobe PDF €“ Properties
€“ Printing defaults.
You cannot set these properties permanently within the properties in Excel,
as soon you have reset to your Standard Printer the options are selected
again.
Clear the selections for €śPrompt for Adobe PDF filename€ť AND €śDo not send
fonts to €śAdobe PDF€ť€ť. Both are important, the latter for the driver to make
the Postscript files thatll be deleted after conversion to PDF-files.
Heres my code:
'**** PDF Creator
Public Sub PrintPDF(DCSel, IXSel)
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim pdfPrinter, pdfName
Set fs = CreateObject("Scripting.FileSystemObject")
StdPrinter = Application.ActivePrinter
pdfUser = "pdf" & Application.UserName
pdfPrinter = Range(pdfUser).Value
pdfFilePath = "P:\"
pdfName = Range(DCSel) & " - " & Range(IXSel) & " - " _
& Range("PN1") & " - " & Format(Range("DocDate"), "YYMMDD")
psFileName = pdfFilePath & "\" & pdfName & ".PS"
pdfFileName = Left(psFileName, Len(psFileName) - 2) & "pdf"
Sheets("PrintDoc").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False,
ActivePrinter:=pdfPrinter, _
printtofile:=True, collate:=True, PrToFileName:=psFileName
pdfDist.FileToPDF psFileName, pdfFileName, ""
Set pdfDist = Nothing
fs.DeleteFile pdfFilePath & "\*.PS"
fs.DeleteFile pdfFilePath & "\*.LOG"
Application.ActivePrinter = StdPrinter
End Sub
'*** End PDF Creator
A couple of notes:
As my colleagues are using the same program but does not necessarily have
the same Adobe driver on the same port, (mine is €śAdobe PDF on NE06:€ť)
I have put the various users printer names in the workbook and the program
looks the appropriate user up and select his/her printer.
Furthermore I created our special naming structure of the document set by
the programs previous selections/conditions. Please note that the pdfName
shall not have any €ś.pdf€ť extension.
Good Luck!
"Jlorga" wrote:
That would do the trick, i copied your example, and change it to my own
configuration, but i couldn't make it work, i do this in the excel
macro:
nameFile = "F:\proj\teste.pdf"
Application.ActivePrinter = "Adobe PDF em Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
WaitTimer = TimeSerial(newHour, newMinute, newSecond)
Application.Wait WaitTimer
SendKeys (nameFile)
SendKeys ("{ENTER}")
but when i run the macro i got the dialog box to choose where to print
the file and its name.
I need to do anything to get SendKeys to work?
And why exactly you use the timer?
Thanks again
--
Jlorga
------------------------------------------------------------------------
Jlorga's Profile: http://www.excelforum.com/member.php...o&userid=31707
View this thread: http://www.excelforum.com/showthread...hreadid=514081