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