导出多个PDF时出现内存错误 [英] Memory Error when exporting multiple PDFs

问题描述

我有一个宏，该宏通过从工作簿中的工作表中导出一系列单元格来生成数百个PDF.该程序的工作方式是，该程序更改工作表上的几个值，然后公式会填写单元格，然后工作表将导出为PDF.程序生成约150个pdf后，Excel崩溃并显示

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"

如果我注释掉导出pdf的代码行，则可以一遍又一遍地运行该程序，而不会出现任何问题.如果将导出的文件类型更改为excel工作簿，则我也可以正常运行该程序.这是我用来导出pdf的打印功能.

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
DoEvents
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, _
OpenAfterPublish:=False

Set print_area = Nothing
Application.CutCopyMode = False
End Function

我注释掉或更改以防止错误的行是以"print_area.Exportas ..."开头的行.我的打印区域由一对链接的图像和其中包含公式的单元格组成.我注意到删除链接的图像会导致程序在生成大约250 pdf后崩溃，这是一个轻微的改进.我无法想象为什么会发生错误，任何帮助将不胜感激.我真的需要文件为pdf格式，并愿意接受任何建议或其他方式来实现这一目标.

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.

推荐答案

从打印区域删除所有链接的图像可防止程序中断.好像导出图像会导致一些内存泄漏.我已经成功生成了600个PDF，没有发生故障，但是我将一遍又一遍地运行该程序，以查看是否还有其他原因也可能导致泄漏.

Removing all linked images from the print area prevents the program from breaking. Seems like exporting images causes some memory leak. I've generated 600 PDFs successfully without a breakdown yet but I am going to run the program over and over again to see if something else might be contributing to the leak as well.

通过在每次迭代中删除并重新创建打印区域中的每个图像，每50次迭代暂停我的程序，并在暂停后抛出DoEvents，现在问题已得到解决.对于为什么会首先发生错误，尚无明确答案.

By deleting and recreating every image in the print area on every iteration, pausing my program every 50 iterations, and throwing in a DoEvents after the pause, the problem has now been fixed. No clear answer as to why the error occurred in the first place.

这篇关于导出多个PDF时出现内存错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！