cozma raul
02-16-2024, 04:31 AM
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
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