0

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
Community
  • 1
  • 1
Gitty
  • 166
  • 8
  • Related to memory, are you using x86 or x64 version of Excel? x86 is capped regardless of your hardware. – Cyril Jan 06 '20 at 18:36
  • @Cyril I'm using x64 – Gitty Jan 06 '20 at 18:37
  • Why are you not closing Wkb3.Close False after you copy from it? – mooseman Jan 06 '20 at 18:49
  • @mooseman the False indicates that it should not save any changes to the file, but just close it as is. – Gitty Jan 06 '20 at 18:53
  • 1
    How many do you have and how large are your source workbooks - how many rows of data/sheets in each one? – Tim Williams Jan 06 '20 at 19:03
  • Have you reviewed: https://stackoverflow.com/a/10382861/3233363 for another solution to looping through the directory? Edited as Tim hit the other point. – Cyril Jan 06 '20 at 19:03
  • @TimWilliams not large. Between 10kb and 20kb for each individual file. Definitely less than 50 rows per sheet. One sheet per file. At most 50 files per folder. – Gitty Jan 06 '20 at 19:09
  • @Cyril I'll try changing the loop method but not sure if that's actually the issue... – Gitty Jan 06 '20 at 19:10
  • 1
    @Gitty Purely going off of a previous issue I recollect where `dir()` was causing an issue... i don't have that post offhand, but remembered the post we used to mark that post `as duplicate` when it was closed. Sorry that's a fairly cryptic description. I believe it boiled down to `dir` versus `dir()` – Cyril Jan 06 '20 at 19:14
  • @Cyril that was in a different bit of code. I fixed it and it worked well- no issues on that end. – Gitty Jan 06 '20 at 19:20
  • Any event handlers in any of these files? Maybe try turning off Events while processing the files. – Tim Williams Jan 06 '20 at 19:24
  • @TimWilliams events are turned off. (There is a macros preceding this one that takes care of that). – Gitty Jan 06 '20 at 19:26
  • In that case I'm out of guesses - maybe some problem in the other procedures called from your posted code. – Tim Williams Jan 06 '20 at 19:53
  • Opening and closing workbooks is a lot for VBA, the code could be just running ahead of excel being able to preform all these operations. When it bombs or exits, check task manager and details to see how many instances of excel are running. – mooseman Jan 07 '20 at 18:04
  • @mooseman at the crash there is always only 2 excel workbooks open. As you can see per the code each Wkb2 closes as soon as the operation is done, so there is at tops three workbooks open at a time. – Gitty Jan 07 '20 at 19:10
  • @Cyril by changing the code from `dir()` to `dir`, I've been able to eliminate the issue of the macro randomly exiting. Now my only issue is the "out of memory" error. I've been able to isolate that it only happens in conjunction with "Run Time error 1004: Method 'Open' of object 'Workbooks' failed", with code line `Set Wkb3 = Workbooks.Open(filename:=Path & "\" & filename)` – Gitty Jan 07 '20 at 19:13
  • @Gitty glad that random recollection helped! What is `filename` when the error occurs? Additionally, it may behoove you to change your variable `filename` to anything but what is "filename" from VBA's native use of that term. – Cyril Jan 07 '20 at 19:57
  • @Cyril `filename` displays the correct path when the error occurs. The issue seems to be with the actual opening of the file. – Gitty Jan 07 '20 at 20:03
  • I'm noticing that the memory usage constantly goes up until it reaches about 3,000MB, at which point the error occurs (even though there's 32GB of memory on this PC). I'm not sure why the worksheets aren't released from memory once they are closed. Any idea how to force a memory release? I've tried setting everything to `Nothing` at the end of each macro, but that doesn't help the issue. – Gitty Jan 08 '20 at 17:17
  • Inserting `Save` after each worksheet is added did not help either (I've seen instances where saving releases memory). – Gitty Jan 08 '20 at 17:18

2 Answers2

1

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.

Gitty
  • 166
  • 8
  • Don't do this. The most efficient way to pull data from a set of workbooks is to open them with ADODB instead of using the Excel Open command. It will make your macro run orders of magnitude faster and use less memory. – HackSlash Jul 28 '21 at 21:00
-1

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

António Cardoso
  • 1