| Author: Vidhya 30 Jul 2008 | Member Level: Gold | Rating:  Points: 1 |
hi,
Here is my code:
'convert to pdfs
Dim pdfDist As New ACRODISTXLib.PdfDistiller
Dim OriginalPrinter, PDFPrinter As String
Dim sName, PSFileName, PDFFileName As String
Dim s As Worksheet
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject") 'Good old FSO
'Identify printers
OriginalPrinter = Application.Printer.DeviceName 'Application.ActivePrinter
'MsgBox OriginalPrinter
PDFPrinter = "Adobe PDF on Ne01:" 'Change this to suit the Acrobat Distiller printer
'Cycle through each sheet, retrieves sheet name and generate PS file in selected folder
For Each s In oWB.Worksheets
sName = s.Name 'Sheet name
If sName = "Access" Then GoTo MoveOn 'Here you can identify sheets you don't want printed
PSFileName = DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\" & sName & ".PS" 'Converts sheet name to file path
PDFFileName = Left(PSFileName, Len(PSFileName) - 2) & "PDF"
'Print to PS file in nominated folder
s.PrintOut Copies:=1, preview:=False, ActivePrinter:=PDFPrinter, printtofile:=True, collate:=True, prtofilename:=PSFileName
'Convert PS to PDF using the method from PDFDistiller class
pdfDist.FileToPDF PSFileName, PDFFileName, ""
MoveOn:
Next s
Set pdfDist = Nothing 'Clears the pipes
'Waits until last PDF has been processed then trashes PS & LOG files
Do Until fs.FileExists(PDFFileName)
Loop
fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.PS"
fs.DeleteFile DLookup("[ExcelTemplatePath]", "tblSetup") & "\" & strOrderID & "\*.LOG"
Application.Printer = OriginalPrinter 'Resets to original printer selection in Excel
Note:
rate this content if it is useful!
|
| Author: Dharmendra Banoliya 30 Jul 2008 | Member Level: Silver | Rating: Points: 2 |
Hi Vidhya
I will defenetely rate your document but
Will u please tell me where to get these reference
ACRODISTXLib.PdfDistiller and worksheet
|
| Author: RamyaNaidu 05 Aug 2008 | Member Level: Silver | Rating: Points: 3 |
Hai Dharmendra Banoliya
u can get this ACRODISTXLib.PdfDistiller in com objects
first goto project properties-->References-->rightclick (addreferences)-->from dialogbox-->com -->Acrobat distiller slect that and enter "ok"
|