Creating a PDF file programatically from Excel
 

Excel 2003. I am using the following code to create a separate Excel file
and PDF file for each sheet in my workbook. The .SaveAs works fine and
creates a file without prompting me. However, the .PrintOut prompts me for
the file name and location for the PDF file. How may I make it create the
PDF file without a prompt? As a subquestion, how may I set my default
working directory so that the .SaveAs and the .PrintOut direct their output
to this directory? Thanks and God bless.

Sub test()
Dim a As Integer
Dim wb As Workbook
Application.ScreenUpdating = False
For a = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(a).Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\Documents and Settings\doug.GNJPM\My
Documents\Excel\Budgets 2005\Temp\" & wb.Sheets(1).Name & ".xls"
wb.PrintOut , , , , "Adobe PDF"
wb.Close False
Set wb = Nothing
Next a
Application.ScreenUpdating = True
End Sub