循环"Batch Export"崩溃 - 处理器或代码错误？

Question

为什么Excel不能遍历大型数据集？！

我有 2 种不同的文档表单，需要导出数百种 PDF。我从互联网上提取了批量导出脚本，并根据我的使用对其进行了修改，以便它根据"批量PDF打印机"工作表上选中的复选框处理这些表单中的任何一个。

一切运行良好 - 对于循环访问的前 10-15 个工作簿，然后它崩溃。每个 Excel 文档都冻结(未响应(，并且宏当前访问的页面部分打开，没有可见数据或单元格。此时，"发布"消息框也可能冻结。一旦它报告了内存不足错误 - 但我无法重复这一点。 Excel不应该删除未使用的缓存以免使内存过载吗？如果一段时间运行不佳，我会怀疑这是一个流浪汉循环。我听说没有办法在"缓存转储"或类似性质的东西中编写脚本。是糟糕的代码，还是我对处理器的要求过高？

Sub Convert2PDF()
'Update the checkbox linked formulas on the GUI workbook
Sheet1.Range("A2").Formula = Sheet1.Range("A2").Formula
Sheet1.Range("B2").Formula = Sheet1.Range("B2").Formula
Sheet1.Range("C2").Formula = Sheet1.Range("C2").Formula
Dim strFolder As String
Dim strXLFile As String
Dim strPDFFile As String
Dim wbk As Workbook
Dim lngPos As Long
' set folder
strFolder = ThisWorkbook.Path & "putfileshere" & ""
Application.ScreenUpdating = False
' Get first filename
strXLFile = Dir(strFolder & "*.xls*")
' Loop through Excel workbooks in folder
Do While strXLFile <> ""
' Open workbook
Set wbk = Workbooks.Open(Filename:=strFolder & strXLFile)
' Assemble the PDF filename
lngPos = InStrRev(strXLFile, ".")
strPDFFile = Left(strXLFile, lngPos) & "pdf"
' Export to PDF
'Do the next 8 lines crash the Macro because they recalculate for every sheet? Page1, Page2, Page3 value are the same for all workbooks processed in a batch
Dim Page1 As String
Dim Page2 As String
Dim Page3 As String
Dim Page4 As String
Page1 = ThisWorkbook.Sheets("Batch PDF Printer").Range("A2")
Page2 = ThisWorkbook.Sheets("Batch PDF Printer").Range("B2")
Page3 = ThisWorkbook.Sheets("Batch PDF Printer").Range("C2")
If ThisWorkbook.Sheets("Batch PDF Printer").Range("C2") = "" Then 
wbk.Sheets(Array(Page1, Page2)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "pdfsgohere" & "" & wbk.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
'run process for format option 2
Else:
wbk.Sheets(Array(Page1, Page2, Page3)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "pdfsgohere" & "" & wbk.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
'Tried killing the finished document to improve function
Dim xFullName As String
xFullName = Application.ActiveWorkbook.FullName
ActiveWorkbook.Saved = True
Application.ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill xFullName
Application.ActiveWorkbook.Close False
End If
' Close workbook - didn't seem to help (can't do it when the workbook is gone)
'wbk.Close SaveChanges:=False
' Get next filename
strXLFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "All Done"

感谢您的帮助。几天来我一直在试图弄清楚这一点。

Answer 1

这在>30 个文件上运行了我没有问题：

Sub Convert2PDF()
Dim strFolder As String, strXLFile As String
Dim strPDFFile As String
Dim wbk As Workbook
Dim lngPos As Long
Dim pages(1 To 4) As String
Dim shtBatch As Worksheet, arr
Set shtBatch = ThisWorkbook.Sheets("Batch PDF Printer")
shtBatch.Range("A2:C2").Calculate '<< assume this was the point of resetting the formulas?
pages(1) = shtBatch.Range("A2").Value
pages(2) = shtBatch.Range("B2").Value
pages(3) = shtBatch.Range("C2").Value
'what pages to print?  Only need to do this once
arr = IIf(Len(pages(3)) = 0, Array(pages(1), pages(2)), _
Array(pages(1), pages(2), pages(3)))
strFolder = ThisWorkbook.Path & "putfileshere"
strXLFile = Dir(strFolder & "*.xls*")
Do While strXLFile <> ""
Set wbk = Workbooks.Open(Filename:=strFolder & strXLFile, ReadOnly:=True)
lngPos = InStrRev(strXLFile, ".")
strPDFFile = Left(strXLFile, lngPos) & "pdf"
wbk.Sheets(arr).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "pdfsgohere" & strPDFFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
wbk.Close False
strXLFile = Dir
Loop
MsgBox "All Done"
End Sub

Answer 2

即使您的可见系统 RAM 没有过载，Excel 应用程序的内部容量似乎也会在短时间内超出。在应用程序进入自动重启之前，我终于能够查看消息框"没有足够的系统资源完全显示"。尝试简化循环访问的工作簿。如果您的工作簿需要一段时间才能启动，这可能表明后台进程(计算和 VBA 订阅(很重。DoEvents 可以通过请求更多的处理时间来帮助代码更流畅地运行，以便系统可以对其需求进行排序。最终

应用程序.计算 = xl手动

在循环的顶部足以减少对 20 GB 系统的计算需求(我从未预料到它会过载(。

Answer 3

如果您在导出中链接了图像。

导出的链接图像会在内核中留下位或字节，这些位或字节会累积并最终破坏 excel。

我在互联网上只找到 1 个位置，我再也找不到它了，但它通过删除链接的图像让我从 200 秒到 1000 遍 VBA 宏。

VBA代码中没有任何帮助，我使用了暂停，保存工作簿以清除内存，禁用事件等......

我在这里写了一个问题的答案：https://stackoverflow.com/a/53600884/10069870

如果导出:)中没有链接的图像，请忽略