Please bear with me I know that this has been talked about previously.
I am trying to write a macro that runs automatically every sunday and
then prints multiple worksheets to pdf. I am able to put the code in
that works without a specific file name but everytime I put a
destination filename in and try to open the file it says it is a
corrupted. These are weekly market files which will always be saved in
the same spot so I don't need to have a user pass a file name. Any help
is appreciated.

Thanks,


--
cooter24
------------------------------------------------------------------------
cooter24's Profile: http://www.excelforum.com/member.php...o&userid=29619
View this thread: http://www.excelforum.com/showthread...hreadid=537883

All dozen or so PDF printers I have tested print in two passes: in the first
pass, they create a PostScript file and in the second, they create the PDF
document. I imagine that you are specifying a file name (with extension PDF)
to which to print; ADOBE is printing the PostScript to it and stopping; that
PDF is not in the PDF format but contains PostScript- hence, it is seen as
corrupt. Don't specify a filename.

As far as I know, there is no way of bypassing the prompt for the filename.

Hi cooter24

This may help - amend to your requirements.....

Sub PrinttoPDF()
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, y As String, wb As Workbook

Set wb = ActiveWorkbook
Application.ActivePrinter = "Adobe PDF on Ne02:"

If wb.Path = "" Then
x = CreateObject("WScript.Shell").SpecialFolders("Desk top") & "\"
y = wb.Name
Else
x = wb.Path & "\"
y = Left(wb.Name, Len(wb.Name) - 4)
End If

PSFileName = x & y & ".ps"
PDFFileName = x & y & ".pdf"

wb.PrintOut , prtofilename:=PSFileName
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

On Error Resume Next
Kill Left(PSFileName, Len(PSFileName) - 2) & "log"
Kill (PSFileName)
End Sub

--

Regards

William

XL2003




"cooter24" wrote in
message ...

Please bear with me I know that this has been talked about previously.
I am trying to write a macro that runs automatically every sunday and
then prints multiple worksheets to pdf. I am able to put the code in
that works without a specific file name but everytime I put a
destination filename in and try to open the file it says it is a
corrupted. These are weekly market files which will always be saved in
the same spot so I don't need to have a user pass a file name. Any help
is appreciated.

Thanks,


--
cooter24
------------------------------------------------------------------------
cooter24's Profile:
http://www.excelforum.com/member.php...o&userid=29619
View this thread: http://www.excelforum.com/showthread...hreadid=537883

cooter24,
To add to the others, from what I understand (but I may be wrong...):
Printing to file gives you a .ps file not a .pdf, despite the fact that you
have given it a .pdf extension. The resulting file can be processed by
Distiller to give a real .pdf file.
You can see this by opening the apparent .pdf in a text editor and comparing
it to a "real" .pdf file.

The easiest way is to by pass this and create a .pdf directly, so forget the
printToFile and filename
The name of the workbook will be used for the file name and the destination
folder will be dependent on the port, as shown in the PrintersAdobe
PDFPropertiesPorts. You can add another port if you want another location.

There are other settings in the pdf print driver that may interfere with
this, one being "Prompt for Adobe PDF filename" which you need to uncheck.

NickHK



"cooter24" wrote in
message ...

Please bear with me I know that this has been talked about previously.
I am trying to write a macro that runs automatically every sunday and
then prints multiple worksheets to pdf. I am able to put the code in
that works without a specific file name but everytime I put a
destination filename in and try to open the file it says it is a
corrupted. These are weekly market files which will always be saved in
the same spot so I don't need to have a user pass a file name. Any help
is appreciated.

Thanks,


--
cooter24
------------------------------------------------------------------------
cooter24's Profile:
http://www.excelforum.com/member.php...o&userid=29619
View this thread: http://www.excelforum.com/showthread...hreadid=537883