Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
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"
ActiveWorkbook.Close
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.
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
- The PhlebobLv 71 decade agoFavorite 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.
- halyardLv 45 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.
- Anonymous1 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.
