1

I am printing worksheets to one single PDF file with one chunk of code. With this PDF file open, If I attempt another print to PDF from this same excel file I get a VB error: "Document not saved" and debug takes me here in the code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
    strFilename & " " & wedate_text & " Time", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True

HERE IS CODE:

Sub PrintAnadarkoTicketsToPDF()
Worksheets("Cover").Visible = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Dim strFilename As String
Dim rngRange As Range
Dim wedate As Date
Dim wedate_text As String
Set rngRange = Worksheets("Cover").Range("A5")
strFilename = rngRange.Value
wedate = Worksheets("Cover").Range("B24").Value
wedate_text = Format$(wedate, "mm.dd.yyyy")
Dim myArray() As Variant
    Dim i As Integer
    Dim j As Integer
    j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Visible = True Then
            ReDim Preserve myArray(j)
            myArray(j) = i
            j = j + 1
        End If
    Next i
    Sheets(myArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
    strFilename & " " & wedate_text & " Time", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    True
Worksheets("Cover").Visible = True
Sheets(1).Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

My question is: How do I print this second PDF without crashing the script? I would like to close the previous PDF or create the second PDF with a different file name. Thanks for the suggestions. Randy

Randy
  • 23
  • 1
  • 5
  • did you try [this](http://stackoverflow.com/questions/25714915/check-if-a-certain-pdf-file-is-open-and-close-it) solution? – Dani Aya Mar 29 '16 at 13:33
  • i guess you are not providing valid filename along with extension, provide valid filename like below "c:\path\filename.pdf". – Kiran Maroju Mar 30 '16 at 09:26
  • @Kiran Maroju The filename is being assembled via cell references in the code. – Randy Apr 06 '16 at 14:03

1 Answers1

0

I'm not sure why you want to have a loop that counts the number of non-hidden sheets. Plus, you could export the sheets inside that loop. That may fix your issue:

For i = 1 To Sheets.Count
    If Sheets(i).Visible = True Then
        Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            strFilename & Trim(Str(i)) & " " & wedate_text & " Time", Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
            True
    End If
Next i

Note also the addition of the workbook number to the file name, because it's trying to save to the same file.

Michael Foster
  • 420
  • 6
  • 12
  • I need to print all sheets to one single PDF. This prints out separate PDF for each sheet. – Randy Apr 06 '16 at 13:52
  • In that case, you may want to put the data all on one sheet. You may even go so far as to create a new sheet, put the data into it, and delete it when you are done exporting it as a single file. Of course, you can do all of this in your code. If you `ExportAsFixedFormat` on a worksheet, then you get a single file out of it. – Michael Foster Apr 06 '16 at 17:22