ExportAsFixedFormat fails sometimes

Viewed 9388

I have a spreadsheet with forms and macros that worked until recently. The first time I saw the described behavior was the first time I tried using this particular function in Excel 2016 (Windows 10), though I'm not sure that's the cause.

The issue I'm having is with the following line

Worksheets("Label Template - 100X150").ExportAsFixedFormat Type:=xlTypePDF, _
  Filename:=pdfFilePath, Quality:=xlQualityMinimum, _
  IncludeDocProperties:=False, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True

Which comes from the following macro:

Sub PDFLabelsSheet()
'On Error GoTo errHandler  

'enter name and select folder for file
strFile = "Labels_PrintGroup-" & lstPrintGroup.Value _
            & "_" _
            & Format(Now(), "yyyy-mm-dd\_hhmm") _
            & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile

Worksheets("Label Template - 100X150").Visible = True
UnprotectTab "Label Template - 100X150"

pdfFilePath = Application.GetSaveAsFilename(InitialFileName:=strFile, _
  FileFilter:="PDF Files (*.pdf), *.pdf", _
  Title:="Select Folder and FileName to save")

If pdfFilePath <> "False" Then

    Worksheets("Label Template - 100X150").Select
    Worksheets("Label Template - 100X150").Range("A1").Select
    Worksheets("Label Template - 100X150").Range("A1").Activate
    Cells.Activate

    Worksheets("Label Template - 100X150").PageSetup.FirstPageNumber = 1

    Worksheets("Label Template - 100X150").ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=pdfFilePath, Quality:=xlQualityMinimum, _
      IncludeDocProperties:=False, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True

End If

exitHandler:
    ExecutionEnd
    Exit Sub
errHandler:
    MsgBox "Something went wrong, a PDF could not be created", vbCritical
    Resume exitHandler

End Sub

I've tried permutations of the ExportAsFixedFormat function, changing it to Worksheets("name"), (number), ActiveSheet.... etc.

Prior to executing this particular Sub, the worksheet is unhidden and unprotected. I've tried setting the entire range as PrintArea, I've tried selecting and activating the range to be exported as well.

The most frustrating part is it works sometimes. I've gone as far as putting in code that spits out every property of the PageSetup collection to make sure that something is not blowing things up since the output depends so strictly on the page setup.

When I navigate to this worksheet manually and choose the export function from the file menu, I get the same error. If I click around in that worksheet for a bit the Export function as well as the Macro work.

The PDFLabelSheets Sub does get called from another Sub (invoked by a form's button press) which compiles a boatload of data into this tab, though I've ruled it out as the culprit as successive runs of just the PDF code will succeed then fail without running the other code.

3 Answers

Frogrammer-Analyst's analysis is great, but the error is not that ambiguous. You will not have this issue if you login to the server using the user that is running the unattended script. But if you sign out and then try rerunning your script unattended you will see that no printers are mapped and {ExportAsFixedFormat} fails for PDF and or XPS formats.

I have found a solution for my case. A true headeache. Maybe it can help others:

Try uncheking the "PDF/A compliant" option under Options dialog when you export as PDF. After that, run the VBA code again.

For my, it was the solution.

PDF Options

Related