Excel 2013 - Closes with no error message when opening workbook or VBA editor

[BigShango]

Hi,

Strange issue. I have a workbook that was working fine this morning. Now when I try to open it Excel looks like its loading the file, then disappears. It doesn't give any sort of error, it just completely closes as if I'd never tried to open it.

If I open it in safe mode it will open but if I try to run VB code it closes again. There's nothing weird with the code, it works fine elsewhere.

One issue I thought it might be is that this spreadsheet has very high sheets numbers (not a high number of sheets) as when testing I'll run code that will create 700 sheets, delete those sheets, change something, run it again etc. This results in the initial sheet names being sheet4000 etc (all are renamed on creation though). Is that perhaps the problem? That I've hit some limit in excels sheet count? Can I fix this?

And how can I stop it happening in the future? If there's 1 sheets, I'd expect the next sheet to be sheet2 not sheet 702 because I deleted 700 sheets before making it.

Thanks. Hope someone can help, I'm kind of f*cked if I can't get this working - it's weeks of work.

 

[Yongle]

Your file may contain a minor corruption.

Microsoft does not limit the number of sheets but computer memory would dictate the limit.
- perhaps Excel suffered a memory issue at some point when creating/deleting hundreds of sheets (resulting in some cumulative debris that Excel is now unable to clear). Did Excel crash unexpectedly previously (perhaps when running VBA) ?

You have not explained the functionality contained in the workbook.
Is it planned that there will be regular ongoing creation & deletion of a large number of sheets ?
- if that is the case it may be safer to create new workbooks each time rather than repeatedly chopping up the same old workbook. The reason I say that is that it reduces the risk of building up cumulative debris.

 

[BigShango]

Yes, that's what happens. There are 5 permanent sheets with reports on them, I dump some data into a sheet and run macros to generate the reports. It also creates roughly 700 sheets with information for individual people. I then do some stuff with this info. A week or so later I run it again, where it deletes the 700 sheets of info and creates them again with that weeks info.

It didn't crash while running, weirdly it ran fine but then the next time I opened it it crashed after loading the file to 100%

Do you think I should have my 5 permanent sheets and my data import sheet as one workbook and then have the 700 weekly sheets build in a separate workbook (with the macros contained in the first one)?

I managed to salvage the file by getting it to open in safe mode and exporting all the vba modules/forms, then importing them to a new sheet and rebuilding the report pages. The good thing was I didn't really need the data, just the workbook that manipulates the data.

 

[Yongle]

Do you think I should have my 5 permanent sheets and my data import sheet as one workbook and then have the 700 weekly sheets build in a separate workbook (with the macros contained in the first one)?

Yes - that's exactly the way I would do it

Rather than deleting & creating 700 sheets simply create a new workbook every time including a date stamp in the name
- delete old versions whenever convenient or within the macro etc

Something like below
- code sits in first workbook (Master)
- creates new workbook (wb)
- saves with name like this FileABC 19 08 0133071.xlsx
- timer element in name a bit OTT when live, but useful when testing to avoid duplicate file names occurring if running repeatedly on same day

Sub CreateNewWorkbook()
    Const aPath = "C:\Folder\SubFolder\etc\FileABC"
    
    Dim Master As Workbook, wb As Workbook, stamp As String, i As Long
    Set Master = ThisWorkbook
    Set wb = Workbooks.Add

    [COLOR=#ff0000][I]VBA to build 700 sheets etc goes here[/I][/COLOR]
    
    
    stamp = Format(Date, " yy mm dd") & Round(Timer, 0)

    wb.SaveAs aPath & stamp, FileFormat:=51     'saves as [B]xlsx[/B] format
    
End Sub

 

[BigShango]

Perfect. I'll do it that way, even without the issue I was having it makes more sense than constantly updating the master file.

That bit of code looks a wee bit more efficient than I'd have written myself too - Thanks!

 

[Yongle]

Thanks for the feedback
Hope it goes well