“Out of Memory”错误是由Excel未释放内存引起的。

huangapple go评论72阅读模式
英文:

"Out of Memory" Error due to Excel not releasing memory

问题

以下是代码的翻译部分:

我将工作表导入一个文件,然后以不同位置的新名称保存该文件。

该宏在 Excel 的内存使用量达到约 3,000MB 时有效,此时会出现“内存不足”错误(此计算机上有 32GB 内存)。

错误出现在此行:Set Wkb3 = Workbooks.Open(filename:=Path & "\" & filename),可能是因为没有足够的内存来打开另一个文件。

导入工作表后,将关闭源文件 Wkb3。

包含导入工作表集合的 Wkb2 在导入完成后保存并关闭。

Wkb1 是唯一保持打开的工作簿。

通常在崩溃之前我能进行大约 40 次迭代,因此显然尽管所有的 Wkb2 和 Wkb3 都已关闭,但某些东西仍然保留在 Excel 的内存中。

我尝试在每次导入后保存 Wkb2 以释放内存。

我尝试将对象设置为无。

以下是我的宏:

Option Explicit
Sub CombineFiles()
Call NewBook '此宏创建一个新文件,用于保存导入的工作表

Dim Wkb1 As Workbook '具有宏的工作簿
Set Wkb1 = ThisWorkbook

Dim Aname As String
Aname = Wkb1.Sheets(1).Range("A1").Value & "\Master File\Master File.xlsx" '单元格 A1 包含每个包含需要合并的文件的单独文件夹的路径

Dim Wkb2 As Workbook 'MasterBook
Set Wkb2 = Workbooks.Open(filename:=Aname)
Dim Wkb3 As Workbook 'DataSource
Dim ws1  As Worksheet '具有宏的工作簿
Set ws1 = Wkb1.Worksheets(1)
Dim ws3  As Worksheet 'DataSource

Dim MyOldName As String
MyOldName = Wkb2.FullName

Dim Path As String
Path = ws1.Range("A1").Value

Dim filename As String
filename = Dir(Path & "\*.xlsx", vbNormal)

Dim Path2 As String
Dim filename2 As String
Path2 = Path & "\Master File\"

Do Until filename = ""
Set Wkb3 = Workbooks.Open(filename:=Path & "\" & filename)
For Each ws3 In Wkb3.Worksheets
ws3.Copy after:=Wkb2.Sheets(Wkb2.Sheets.Count)
Next ws3
Wkb3.Close False
filename = Dir()
Loop

Application.DisplayAlerts = False
filename2 = Wkb2.Worksheets(2).Range("A2").Text
Wkb2.SaveAs filename:=Path & filename & ".xlsx"
Wkb2.Close True
Kill MyOldName
Call KillFiles
Application.DisplayAlerts = True
End Sub

如果您需要任何进一步的帮助,请随时提问。

英文:

I import sheets into a file, then save the file with a new name in a different location.

The macro works until the memory usage for Excel reaches about 3,000MB, at which point an "Out of Memory" error occurs. (There is 32GB of memory on this PC.)

The error occurs on this line, Set Wkb3 = Workbooks.Open(filename:=Path & "\" & filename) presumably because there isn't enough memory to open another file.

Wkb3, which is the source file where the sheet is being imported from, is closed after the import.

Wkb2, which contains the collection of imported sheets is saved and closed after the imports are done.

Wkb1 is the only one that is constantly open.

I usually manage to go through 40 or so iterations before the crash, so clearly even though all Wkb2 and Wkb3 are being closed, something is staying in Excel's memory.

I tried saving Wkb2 after each import to see if that will release memory.

I tried setting Objects to nothing.

Here's my macro:

Option Explicit
Sub CombineFiles()
Call NewBook 'this marco creates a new file that will hold the imported sheets

Dim Wkb1 As Workbook 'Wkb with Macro
Set Wkb1 = ThisWorkbook

Dim Aname As String
Aname = Wkb1.Sheets(1).Range("A1").Value & "\Master File\Master File.xlsx" 'cell A1 holds the path for each individual folder that holds files that need to be combined

Dim Wkb2 As Workbook 'MasterBook
Set Wkb2 = Workbooks.Open(filename:=Aname)
Dim Wkb3 As Workbook 'DataSource
Dim ws1  As Worksheet 'Wkb with Macro
Set ws1 = Wkb1.Worksheets(1)
Dim ws3  As Worksheet 'DataSource

Dim MyOldName As String
MyOldName = Wkb2.FullName

Dim Path As String
Path = ws1.Range("A1").Value

Dim filename As String
filename = Dir(Path & "\*.xlsx", vbNormal)

Dim Path2 As String
Dim filename2 As String
Path2 = Path & "\Master File\"

Do Until filename = ""
    Set Wkb3 = Workbooks.Open(filename:=Path & "\" & filename)
    For Each ws3 In Wkb3.Worksheets
        ws3.Copy after:=Wkb2.Sheets(Wkb2.Sheets.Count)
    Next ws3
    Wkb3.Close False
    filename = Dir()
Loop

Application.DisplayAlerts = False
filename2 = Wkb2.Worksheets(2).Range("A2").Text
Wkb2.SaveAs filename:=Path & filename & ".xlsx"
Wkb2.Close True
Kill MyOldName
Call KillFiles
Application.DisplayAlerts = True
End Sub

答案1

得分: 1

很遗憾,我能想到的唯一解决方法是关闭Excel并通过Windows任务计划重新启动宏。这并不能解决“内存不足”的错误,但至少它会不时重新启动宏,这样我就不会浪费时间在这个错误上。

英文:

Unfortunately, the only fix I was able to come up with was to Kill excel and restart the Macro via the Windows Task Scheduler. It doesn't fix the "out of memory" error, but at least it restarts the Macro every now and then so I don't lose time on it being stuck on this error.

答案2

得分: -1

如果你在运行32位的Excel,限制是4GB。

英文:

If you are running Exce32 bits, the limit is 4Gb.

huangapple
  • 本文由 发表于 2020年1月7日 02:24:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/59617082.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定