Need Help to convert Excel sheets to pdf file

[moniquenguyen]

Hi,

I had the code to print each excel sheet to postscript file then convert to pdf file. Some how when I am put them in the loop to iterate each tab in the excel book. the function FileToPDF fail.

Here are the code

Do Until rsTabs.EOF
Set objSheet = objBook.Sheets(rsTabs.Fields(1).Value)
objSheet.Activate
objSheet.Select
stPriceCode = objSheet.Range("L5").Value
stTab = rsTabs.Fields(1).Value

PSFileName = stPath & stTab & ".ps"
PDFFileName = stPath & stTab & ".pdf"
LogFileName = stPath & stTab & ".log"


Worksheets(stTab).Select
ActiveWindow.SelectedSheets.Copy

Worksheets(stTab).Select
ActiveSheet.Buttons.Delete

ActiveWorkbook.SaveAs Filename:="\\Test\" & stTab & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'convert to postscript file
ActiveSheet.PrintOut Copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

ActiveWorkbook.Save
ActiveWorkbook.Close
rsTabs.MoveNext
Loop

I can not figure out why that happen. Thank you for your help in advance.