Search code examples
excelvbapdfacrobat

PDF export missing a sheet in some countries

I know there are a load of Excel-to-PDF related questions but I think I've come across a unique case.

I'm using a standard line to export all visible sheets from a workbook, via Access:

xlWb.ExportAsFixedFormat Type:=0, Filename:=fp, Quality:=0, IncludeDocProperties:=True, _
   IgnorePrintAreas:=False, OpenAfterPublish:=True

There are various reasons I'm not using an array of sheet names.

I work for a multi-national. In Europe and Asia Pacific everything works OK but in Africa and the Middle East one worksheet is consistently missing from the PDF export.

Things I've checked / tried:

  • There is a used and filled range
  • Page breaks & print area are appropriate
  • Page orientation is the same as other sheets
  • Page setup DPI setting is the same as other sheets
  • Early vs late binding of Excel
  • Non VBA: manually exporting the workbook as PDF
  • Tested with multiple users in region

It's always the exact same sheet, which happens to be the last visible sheet in the workbook.

Update - additional tests

  • Extended the print area to take up most of a page
  • Reset the page setup to defaults / auto (breaks, print area, scale etc)
  • Added a sheet after the sheet in question (neither sheet printed)
  • Copied the sheet after itself (neither printed)
  • Saved the file before printing PDF
  • Deleted all hidden sheets

What did work was to move the sheet, however the sheet that was now in the same place (last in the file) did not get printed instead.

Solution

  • It turned out that shrinking the columns a few points to fit the page further within the page margins did the trick. Although Excel print previewed the sheet fine I guess Acrobat saw it as over-running the print margins and excluded it from the export.

    Another takeaway is that when converting to PDF page setup settings in one region of the world will not necessarily work as expected in other regions.