Hello,

Try the method I use as under:

1. You have to first to some settings on the Adobe PDF printer. Open
Adobe PDF's printer properites from printer and faxes. Clik on General
Tab. There click on "Printing Preferences." Uncheck boxes 'View Adobe
PDF Results' and 'Prompt for Adobe PDF Filename'. Click on OK.
Click on 'Advanced' tab. There click on 'Printing Defaults'. Again
uncheck above mentioned two boxes.

2. Find the full name of the Adobe PDF Printer as recognized by excel
application. For this in excel open the file click on file menu- Print.
Select Adobe PDF Printer and click on close.
In a module, add following code to display the printer name:
Sub showAdobePrinter()
MsgBox Application.ActivePrinter
End Sub.
Note down the full printer name displayed.
In my case it is 'Adobe PDF on Ne03'

3. Add following code to convert the sheet to PDF.
Sub MakePDF()
Dim lastPrinter As String, myPath As String
Dim myFileName As String, Wb As Workbook
myPath = "C:\Temp\" 'Only a temp. folder name (must exists)
myFileName = "xyz" 'desired pdf filename WITHOUT extension
myFullPath = myPath & myFileName & ".xls"
Worksheets("Sheet1").Copy 'Select your sheetname
Set Wb = ActiveWorkbook
Wb.SaveAs myFullPath
lastPrinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne03:"
Wb.PrintOut
Wb.Close (False)
Kill myFullPath
Application.ActivePrinter = lastPrinter
End Sub

The xyz.pdf file gets saved in the default folder path used by Adobe PDF
printer, defualt is My Documents.

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!