2

A while back I built a macro that looks at a list of names, and from that list of names it creates workbooks based on each name of the list. The macro adds some content for each new workbook created, and then saves and closes the new workbook before it then goes to the next name on the list and starts the process over again. The problem I've been having is that Excel will randomly crash after any bit of code that closes any of the new workbooks. When I say randomly, I mean that sometimes it might go through 3 names on the list and crash, other times it might go through 10 names on the list and then crash, but it's always after the portion of code that closes the new workbook.

I've tried a handful of different ways to close the workbooks, but all of them create the same problem.

Workbooks("Workbook_Name").Close SaveChanges:=False(I've tried setting to both true and false)

ActiveWindows.Close

ActiveWorkbook.Close

Application.Windows("Workbook_Name").Close

The reason I know the issue is with the close function is because when I take it out completely, and just let it run while keeping each new workbook open, it doesn't crash. Obviously, this isn't practical because it puts a huge strain on my system as I open and leave open hundreds of workbooks.

I thought originally that the issue might be timing, and that the workbook was trying to close before other bits of code at finished running, but I have added Application.Wait (Now + TimeValue("00:00:00")) both before and after the close function with varying wait times, and the issue still persists.

Here is an example of the code immediately surrounding the close function: 

'Save workbook
  ChDir "Directory_Path"
    ActiveWorkbook.SaveAs Filename:="File_Path\"Workbook_Name", _
        FileFormat:=xlOpenXMLWorkbook
    Application.Wait (Now + TimeValue("00:00:08"))
     ActiveWorkbook.Close False
   Application.Wait (Now + TimeValue("00:00:03"))
'Delete names from list
   Windows("LDS_Test.xlsm").Activate
   Sheets("names").Select
            Rows("3:3").Select
   Selection.Delete Shift:=xlUp

Lastly, I know it isn't a corrupt workbook as I have just recently rebuilt this macro from scratch in a new, clean workbook, hoping that a fresh build out would fix the issue.

Any suggestions?

Jcmoney1010
  • 912
  • 7
  • 18
  • 41
  • What do you mean by "crash"? – Kyle Jul 08 '16 at 17:26
  • 1
    @Kye I get the message that `Excel has stopped working` and then it attempts to find a solution, but just closes Excel and then restarts it. – Jcmoney1010 Jul 08 '16 at 17:31
  • 1) Refactor the code to remove [`.Select` and `Active...`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) If the activeworkbook is not what you expect at time of closing, it may be causing an issue. 2) How about using the `False` argument for save changes if you can? 3) `ActiveWorkbooks` is not even proper syntax. – Scott Holtzman Jul 08 '16 at 17:31
  • 1
    @ScottHoltzman Thanks for the comment. I will work on refactoring the code to take out `.Select` and `.Activate` but it seems weird that those two pieces would cause the issue, seeing as how they are after when the crash occurs. As for using the `False` argument, I have tried this in the past, and it does not fix the issue. Lastly, the `ActiveWorkbooks` in the code set is a typo, and I will be correcting that after I type this message. – Jcmoney1010 Jul 08 '16 at 17:50
  • @Jcmoney1010 - `ActiveWorkbook.Close False` *is* the crash line, according to what you wrote. The actual `ActiveWorkbook` may not be the one you think it is. (Although it may be correct, as well). I am curious what happens when you create one workbook manually, add the content, save it, and then close it - does the close crash Excel then? – Scott Holtzman Jul 08 '16 at 18:36
  • @ScottHoltzman no it does not crash Excel when I perform the task manually. As I mentioned before, the crashes are random. For example I just ran the program and it produced 6 workbooks before crashing, where as when I ran it a few minutes ago I only got through 3 workbooks. I'm thinking the inconsistency of the crashes could point to a memory issue? I don't know. – Jcmoney1010 Jul 08 '16 at 18:57
  • Have you found any solution for this? I'm having the same thing. – M. Coutinho Sep 07 '17 at 15:20

0 Answers0