VB in Excel: Brutal crash when overwriting files?

I want to create a folder, and .xls files in it, but not only once, but repeatedly (updates), without having to delete that folder (and its content), just overwrite it. This is how I do that:

On Error Resume Next

MkDir path & "\Annual by Name"

On Error GoTo 0


<more code>


For x = 1 to <whatever>

ActiveWorkbook.Sheets("INS Annual Template").Copy

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs path & "\Annual by Name\" & DiffNames(x) & ".xls"


Application.DisplayAlerts = True


Next x

For previous applications, where there were less than 20 files in that folder, this works perfectly.

Now I have 30-80 files, and every time x > twentysomething, I am getting "Excel has to close, do you want to tell Microsoft...." (as if that helps, ha-ha).

There is no compiler error, or subscript out of range.

What's going on? Do I need to set anything to Nothing or Nil, or alike?


Not sure about that Memory thing...

Because if I delete that folder and its content manually, before I run the program (hence not overwrite but just write), then there is no problem.

Update 2:

Unfortunately, non of the (first 3) answers did the trick. So I let "the community" decide...

Meanwhile, I solved the problem by first renaming the initial folder, and then creating a new one with all the files.

3 Answers

  • 1 decade ago
    Favorite Answer

    It sounds like you're allocating some resource in the loop and never releasing it. While you're under 20 files you're still in safe territory, but somewhere beyond that you're off the edge of the cliff.

    I notice you're turning off alerts while doing the SaveAs. Try leaving them on. They might give you the information you need.

    Also: I'm not familiar with the .Copy method, but be sure it's not grabbing a resource the .Close isn't giving back.

    Hope that helps.

  • 4 years ago

    i'm afraid you isn't getting that report returned. as quickly as you keep over a report this is long previous perpetually. Undo won't paintings after a keep. Saving clears the Undo buffer. this is real in any software i've got ever worked with. A report restoration utilty can not even help you right here. Sorry approximately that.

  • Anonymous
    1 decade ago

    This sounds like you are using too much memory, this can make it impossible for the program to continue normal operation. Maybe you need to break it into groups of 10 files, allowing the RAM to unload between.

Still have questions? Get your answers by asking now.