Export excel worksheet to PDF coming out as blank pages

Question

I am trying to export a worksheet of charts to PDF that consists of 170 pages (around ~2000 charts) if printed. However, when I ran the macro, it gave me a PDF of 170 pages which were all blank. I've set the page break within the worksheet and adjusted the PrintSetup, but it seems like it's an issue due to the large size of the sheet.

I had success exporting a similar worksheet to PDF that had 50 pages of charts, so I think it has to do with the memory it uses when exporting the sheet to PDF if above 50 pages. A snippet of my code looks like:

    Sheet2.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

Considering the above problem, I wanted to know if I can export the spreadsheet into multiple PDFs so that I can possibly eliminate this issue. Is this possible? Page breaks in the worksheet are set such that each page of the worksheet consists of 9 rows.


Show source
| excel-vba   | vba   2017-01-05 23:01 1 Answers

Answers ( 1 )

  1. 2017-01-05 23:01

    The ExportAsFixedFormat has arguments for From and To, which you can use to limit the size of the ouput (and therefore avoid to blow out all available memory).

    EDIT Also ensure that the sheet is selected:

    With Sheet2
      .Select
      .ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    End With
    
◀ Go back