Cannot get Excel VBA to create PDF files

I am on Excel 2003, Adobe Acrobat PDFWriter 5.0

I am trying to set up an Excel spreadsheet so that the user can push a button and create multiple PDF files without having to answer prompts to name them. I have done some research and tried some of the suggested code, but I keep getting files without the .PDF extension that have 0 kb.

Here is my code - in the first IF, I tried to print using the PDF_Distiller; in the remaining IFs, I simply tried to print using the Acrobat PDFWriter.

Sub Print_All_Detail()

Dim I As Integer
Dim objPDF_Distiller As PdfDistiller

I = 1

Do While Sheet6.range("F" & I).Value <> ""

    Sheet1.range("R7") = I
    Sheet1.Activate
    ActiveSheet.PageSetup.PrintArea = "$P$3:$Q$50"
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = "$A:$A"
        .LeftMargin = application.InchesToPoints(1)
        .RightMargin = application.InchesToPoints(1)
        .TopMargin = application.InchesToPoints(1)
        .BottomMargin = application.InchesToPoints(1)
        .HeaderMargin = application.InchesToPoints(0)
        .FooterMargin = application.InchesToPoints(0)
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
   
    Set objPDF_Distiller = New PdfDistiller
           
   
    If I = 1 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, Collate:=True, _
        ActivePrinter:="Acrobat PDFWriter on LPT1", _
        PrToFileName:="C:\temp.ps"
        objPDF_Distiller.FileToPDF "C:\temp.ps", "C:\AAA DLS\Excel\CobbLPO.pdf", ""
        Kill "c:\temp.ps"
        Set objPDF_Distiller = Nothing
    ElseIf I = 2 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\CherConLPO"
    ElseIf I = 3 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\DawsonLPO"
    ElseIf I = 4 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\ForsythLPO"
    ElseIf I = 5 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\FairburnLPO"
    ElseIf I = 6 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\GwinnettLPO"
    ElseIf I = 7 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\HallLPO"
    ElseIf I = 8 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\HenryLPO"
    ElseIf I = 9 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\RockNewLPO"
    ElseIf I = 10 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\WGALPO"
    ElseIf I = 11 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\MarLPO"
    Else
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        PrintToFile:=True, _
        ActivePrinter:="Acrobat PDFWriter", _
        PrToFileName:="C:\AAA DLS\Excel\AtlRgnLPO"
    End If
   
   
    I = I + 1

Loop

MsgBox ("Report files have been created.")

End Sub


Thanks in advance for your help!
Start Free Trial