Export as PDF Loop maxes out at 21 iterations

potterfan

New Member
Joined
Jul 14, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Trying to implement this code to a loop in Excel for Mac 365:

VBA Code:
Sub SaveReportAsPDFIn2020()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'Ben Matson : 5-June-2020
    'Test macro to save as pdf with ExportAsFixedFormat

    Dim filename As String
    Dim Folderstring As String
    Dim FilePathName As String
    Dim Report As Variant
    Dim ws As Sheets
    Dim sh As Worksheet
    
    Set ws = Sheets
    Set Report = ThisWorkbook.Sheets(Array("Cover Sheet", "Ag Loss Sensitivity", _
                        "Experience Rating Sheet", "Loss Ratio Analysis", _
                        "Mod Analysis&Strategy Proposal", "Mod Snapshot", _
                        "Mod & Potential Savings"))
    
    
        ws("Cover Sheet").PageSetup.PrintArea = Range("A1:G37").Address
        ws("Ag Loss Sensitivity").PageSetup.PrintArea = Range("A1:H55").Address
        ws("Experience Rating Sheet").PageSetup.PrintArea = Range("A4:L322,A324:M340").Address
        ws("Loss Ratio Analysis").PageSetup.PrintArea = Range("A1:M54").Address
        ws("Mod Analysis&Strategy Proposal").PageSetup.PrintArea = Range("A1:M44").Address
        ws("Mod Snapshot").PageSetup.PrintArea = Range("A1:O69").Address
        ws("Mod & Potential Savings").PageSetup.PrintArea = Range("A1:L80").Address

    
    
    'Name of the pdf file
    filename = ThisWorkbook.Sheets("Cover Sheet").Range("B20") & "_Emod" & "_" & ThisWorkbook.Sheets("Yearly Breakdown").Range("F2") & ".pdf"
    
    'Path Creation and Setting
    Folderstring = "/Users/ben/Desktop/Emod_Calc/Emods_2"
    FilePathName = Folderstring & Application.PathSeparator & filename
    
    'Selecting what sheets to Print
    Report.Select
   
    'Prints as PD
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
    FilePathName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    'Clears Print Area
    For Each sh In Report
        sh.PageSetup.PrintArea = ""
    Next sh

    'Clears the variables
    Set Report = Nothing
    filename = ""
    Folderstring = ""
    FilePathName = ""
    
    ThisWorkbook.Sheets("Yearly Breakdown").Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

Whenever I run associated loop, on the 21st iteration excel crashes.

Only other thing I see happening is the Memory usage capping at 2GB right before crash.

Any help is appreciated.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that
 
Upvote 0
The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that
ooooh.... could you give e an example of what you mean?
 
Upvote 0
The most memory intensive part of the report is surely the Report variable containing an array of all the sheets

There's no need for this array - you could instead substitute an array of just the sheet names and loop through that - I would try that
Oh you mean loop through to select them then print them?
Can you select multiple sheets in a loop and they all are the same active sheet?
 
Upvote 0
Actually, having looked at the print options, you can specify the sheet number to start from and end on (like Word).
If you can arrange the sheets in the correct order, it's worth trying.

2020-07-28 14_33_19-Workbook.ExportAsFixedFormat method (Excel) _ Microsoft Docs.jpg
 
Upvote 0
Actually, having looked at the print options, you can specify the sheet number to start from and end on (like Word).
If you can arrange the sheets in the correct order, it's worth trying.

View attachment 19109
Tried it out. still does the same thing. also found out that it is not necessarily the memory that is maxing out. its just after 21 iterations it fails. Im thinking its like a hidden memory leak.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top