Excel Not Enough memory error into a export to pdf

[cozma raul]

I created a VBA script to convert an Excel file to PDF. The script functions well; however, the issue arises when attempting to convert multiple files. After processing a few files, an error message stating 'there isn't enough memory to perform this task' appears. Strangely, the occurrence of this error seems random. For instance, during the initial run, I successfully converted 25 files without encountering any problems until the error surfaced. On subsequent attempts, the error occurred after processing the first file. After making adjustments to the script, I was able to convert approximately 10 or 12 files before facing the same memory-related error. Interestingly, a colleague is experiencing the exact same issue.
I searched a little bit on the internet and tried to fix it. I checked the option to 'Ignore other applications that use DDE.' The computer should be okay, with 8 GB RAM and an i5 processor. The system is 64-bit. My colleague's computer is even better, with 16 GB RAM and an i9 processor, I believe.

I tried the 'print to PDF' option, but it's not really suitable for what I want to do because a prompt asking me where I want to save the folder always opens.
I also tried to put a timer between files, thinking that if there is a break between them, maybe it will work. It's not working.
I am pretty sure that the script is fine, i get over some ole action error from a script wich donwload bulk data from SAP, but i can't find nothig on this one.
This is the script. Has anybody else had this issue?

Public Sub OpenExcelFilesAndConvertToPDFWithPause()
    Dim excel_file As String
    Dim wb As Workbook
    Dim COD_ANRE As Variant
    Dim nume_fisier As Variant
    Dim folder_fisier As Variant
    Dim pdf_folder As Variant
    Dim ws As Worksheet
    Dim activeRow As Long

    ' Set the flag to false initially
    ShouldStop = False

    Set ws = ThisWorkbook.Sheets("Sheet1")
    activeRow = ActiveCell.Row

    If ws.Cells(activeRow, 1).Value = "" Then
        MsgBox "Active row is empty. Please select a non-empty row.", vbExclamation
        Exit Sub
    End If

    ' Specify the folder path for PDFs from cell M1
    pdf_folder = ws.Range("M1").Value

    ' Check if the specified folder exists
    If Len(Dir(pdf_folder, vbDirectory)) = 0 Then
        MsgBox "PDF folder not found: " & pdf_folder, vbExclamation
        Exit Sub
    End If

    Do While ws.Cells(activeRow, 1).Value <> "" And Not ShouldStop
        COD_ANRE = ws.Cells(activeRow, 1).Value
        nume_fisier = ws.Cells(activeRow, 13).Value
        folder_fisier = ws.Range("B1").Value

        ' Construct the file path based on the active row
        excel_file = folder_fisier & COD_ANRE & "_CEF_" & nume_fisier & ".xlsx"

        ' Check if the file exists
        If Dir(excel_file) <> "" Then
            ' Open the Excel workbook
            Set wb = Workbooks.Open(excel_file)

            ' Optionally, you can perform additional actions with the opened workbook here

            ' Construct the file path for the PDF using the specified folder
            Dim pdf_path As String
            pdf_path = pdf_folder & COD_ANRE & "_CEF_" & nume_fisier & ".pdf"

            ' Convert the sheet "1. calcul reficienta economica" to PDF
            ConvertSheetToPDF wb, "1. calcul reficienta economica", pdf_path

            ' Close the workbook without saving changes
            wb.Close SaveChanges:=False
        Else
            MsgBox "File not found: " & excel_file, vbExclamation
        End If

        ' Pause for 2 seconds
        Application.Wait Now + TimeValue("00:00:02")

        ' Move to the next row
        activeRow = activeRow + 1
    Loop
End Sub


Public Sub StopScript()
    ' Set the flag to stop the script
    ShouldStop = True
End Sub

[jdelano]

What is this calling ConvertSheetToPDF? You can save the workbook ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=saveLocation.

You will also want to make sure to release the objects you create with Set, Set wb = nothing after you close the workbook. VBA Out of Memory Error - Automate Excel, you can debug the code and watch the Task Manager to see what parts of the macro increase memory usage.

Debugging in Excel VBA (In Easy Steps) (excel-easy.com)

I'd start there.

[cozma raul]

ConvertSheetToPdf is calling a subroutine with sheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard.
I integrated the Set wb = Nothing. Also, I opened Task Manager to see how much of my memory is used while running the script. My script used an extra 10 MB of memory (I checked the memory usage of Excel before and after starting the script). Nevertheless, it didn't reach 100% of my memory. Still, the error appeared again.
As you can see, it's not the VBA out-of-memory error; it's the Excel out-of-memory error. Of course, after this error appears, VBA gives me a 1004 error.

[cozma raul]

error.jpg

[cozma raul]

error1.png

Sorry for posting multiple replies, but I was really strugling to upload these pictures