Problem Exporting worksheet to PDF in a loop

[jhonyjhon]

Hi,

I have set up a worksheet which is set to export (print) to a PDF file. However it is a form with changing values (form values changes before each export), that's why i have put the code in a loop.
The problem is when i have to print (export) the worksheet multiple times excel gives an error;
"Run-time error '1004': Document not saved. The document may be open, or an error may have been encountered when saving."

Sometimes this happens after 30-ish export later, sometimes it never happens. The weirdest thing is when i try to debug the problem and do the steps manually no matter how many exports i take it never gives an error. I have tried setting up a time delay after each export thinking that maybe system needs a break after an export but this didn't helped either.

I need an expert eye on this code (keep in mind that i'm quite new to vba coding)

Here is the code below;

Incase you might need more context please contact me, I can provide the whole code,

Dim txtID As VariantDim iRow As Integer
Dim iCount As Integer




iRow = 3
iCount = 0
Do Until IsEmpty(Worksheets("KAYIT").Cells(iRow, 34))
    
    'set the filtered id numbers one by one
    txtID = Worksheets("KAYIT").Cells(iRow, 34).Value
    Worksheets("PUANTAJ").Range("H7").Value = txtID
    Worksheets("PUANTAJ").Activate
    
    'take a print
        'Print
        '
        Dim ws As Worksheet
        Dim strPath As String
        Dim myFile As Variant
        Dim strFile As String
        Dim yil_ay As String
        Dim sicil As String
        On Error GoTo errHandler
        
        'get the turkish year and month names
        'get the id number from the worksheet
        yil_ay = Worksheets("PUANTAJ").Range("H13").Value
        sicil = Worksheets("PUANTAJ").Range("H7").Value
        Set ws = ActiveSheet
        
        'define a file name for the printout
        ' it will look like PUANTAJ_SICIL NO_YIL_AY
        '  and the directory will be the same as the workbook
        strFile = Replace(ws.Name, " ", "") _
                    & "_" _
                    & sicil & "_" _
                    & yil_ay _
                    & ".pdf"
        strFile = ThisWorkbook.Path & "\" & strFile
                
    'Application.Wait (Now + TimeValue("0:00:1"))
    
                ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=strFile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
    
    
    iRow = iRow + 1
    iCount = iCount + 1
Loop
            ' Enable screen refreshing.
            Application.ScreenUpdating = True
            
            MsgBox (iCount & " Records have been taken as PDF")
        
exitHandler:
            
                Exit Sub
            ' Enable screen refreshing.
            Application.ScreenUpdating = True
                
errHandler:
                
                MsgBox "Encountered an error while saving!"
                Resume exitHandler
            ' Enable screen refreshing.
            Application.ScreenUpdating = True

 

[mole999]

just above loop you might try DoEvents that could allow the system to pick its optimal transition

I would also be tempted to change your exit and error handlers, turning on the screen updating before exit sub or msgbox

 

[jhonyjhon]

just above loop you might try DoEvents that could allow the system to pick its optimal transition

I would also be tempted to change your exit and error handlers, turning on the screen updating before exit sub or msgbox

Thank you so much, I have changed my code as you've described but it didn't helped. Nonetheless, I've learned a new command. I really appreciate your help.