Print Excel to Adobe Distiller

[marble]

Hi All,

I hope someone can help. I check the FAQ with llittle success.

I have a script that will create a temp file, then open an excel document. I then print that open document to adobe distiller.

The issue is that I am always prompted with a saveas dialog box to save the pdf file.

I am using the same function to create a pdf from word and that works great, no prompting. I think the issue is with the wdo.ActiveWorkbook.PrintOut line.

I hope someone can help...




Function XLS2PDF( txtInFile, txtOutFile, txtPrint )
on error Resume Next

Set wdo = CreateObject("Excel.Application")
Set wdocs = wdo.Workbooks

wdop.visible = false
wdo.DisplayAlerts = FALSE


' create temp file and directory from system
with objFso
sTempFile = .GetSpecialFolder(TemporaryFolder) + "\" + .GetTempName()

sDocFile = .GetAbsolutePathName(txtInFile) : ' get windows version of filename
sFolder = .GetParentFolderName(sDocFile) : ' get windows version of parent folder

' outfile is not avaiilable then use the input file base anme and directory to output the pdf
If Len(txtOutFile)=0 Then
txtOutFile = .GetBaseName(sDocFile) + ".pdf"
End If

' output directory is not avaiilable then use the input file base name and directory to output the pdf
If Len(.GetParentFolderName(txtOutFile))=0 Then
txtOutFile = sFolder + "\" + txtOutFile
End If
end with


' Remember current active printer
sPrevPrinter = wdo.ActivePrinter

' "Adobe PDF"
wdo.ActivePrinter = txtPrint

' Open the Excel document
Set wdoc = wdocs.Open(sDocFile)


' generate a postscript (.ps) (temporary) file
call wdo.ActiveWorkbook.PrintOut (,,1,false,txtPrint,false)


wdoc.Close WdDoNotSaveChanges
wdo.ActivePrinter = sPrevPrinter
wdo.Quit WdDoNotSaveChanges



' Distill the postscript file to PDF
oDistiller.FileToPDF sTempFile, txtOutFile, "Print"

' Delete the temporary postscript file...
objFso.DeleteFile( sTempFile )


If Err.Number <> 0 Then
'call DisplayErrorInfo("XLS2PDF")
else
Objects("Status").text = "PDF created successfully!"
End If

end function



Thaks in advance....

 

[PHV]

Replace this:
wdoc.Close WdDoNotSaveChanges
By this:
wdoc.Close False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marble]

PH,

Thankyou for the quick reply.

Unfortunately this did not work, I am still prompted to save the file.

Thanks again,

Marble

 

[PHV]

And what about this ?
Call wdo.ActiveWorkbook.PrintOut(,,1,False,txtPrint,True,,sTempFile)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marble]

PH,

I was on the same wavelength, I just had too many commas at the beginning.


My old code was
'call wdo.ActiveWorkbook.PrintOut (, , , , txtPrint, , sTempFile)


However, your code works great.

Again, thanks for all the help.

Marble