Home Memory Error when exporting multiple PDFs
Reply: 0

Memory Error when exporting multiple PDFs

user2034 Published in June 19, 2018, 4:23 pm

I have a macro which generates hundreds of PDFs by exporting a range of cells from a sheet in my workbook. The way the program works is that the program changes a couple values on the sheet, formulas then fill in cells, and then the sheet is exported as a PDF. After the program generates about 150 pdfs, Excel crashes and displays

memory errors like "not enough resources to complete action"

If I comment out the line of code that exports the pdf, I can run the program over and over again with no issues. If I change the exported file type to an excel workbook, I can also run the program with no issues. Here is the print function I use to export pdfs.

Function Prnt(print_area As Range)

Dim myDir As String, mySht As String
Dim supplier As String
supplier = Range("F4").Value
myDir = Range("y2").Value
mySht = Range("y1").Value
mySht = RemoveSpecialChars(mySht)
On Error Resume Next
MkDir myDir
On Error Resume Next
myDir = myDir + "\" + Range("z1").Value
On Error Resume Next
MkDir myDir
On Error Resume Next
supplier = RemoveSpecialChars(supplier)
myDir = myDir & "\" & supplier
On Error Resume Next
MkDir myDir

print_area.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myDir & "\" & mySht, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _

Set print_area = Nothing
Application.CutCopyMode = False
End Function

The line that I comment out or change to prevent the error is the line that starts with "print_area.Exportas...". My print area consists of a couple linked images and cells with formulas in them. I noticed that removing the linked images caused the program to crash after about 250 pdfs were generated which was a slight improvement. I can't imagine why the error is occurring and any help would be appreciated. I really need the files to be in pdf format and am open to any suggestions or alternative ways to achieve this.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.71747 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO