Issue with Porting Excel 2010 Addin to Excel 365 - Garbage Collection Issue RRS feed

  • Question

  • I have an Excel addin that creates reports from an Azure SQL DB.  It creates a:

    1. PivotTable
    2. Several graphs
    3. Several tables

    It works perfectly in Excel 2010 (32-bit).  It crashes Excel 365 (32-bit).  It was so bad I have to restart Windows 10 to get Office to work properly.  I finally traced problem to the following command:

      Set ws1 = Worksheets.Add(Before:=Worksheets(1), type:=xlWorksheet)    

    The PivotTable is created perfectly but when it creates worksheet for the first graph the worksheet contains the PivotTable structure with no data.  Now if I breakpoint on the Add line for the first graph then hit Continue it works perfectly.  It appears the problem is with Excel 365 garbage collection.   It is in the middle of garbage collection of the PivotTable sheet and when it adds a new sheet it copies the PivotTable structure to the new sheet.  I did 100's of experiments to fix problem and nothing worked so I changed the order and create the PivotTable last.  This fixed problem.  I tried this on another PC and it had the same problem so it isn't my PC problem.

    This is a serious problem with Excel 365.  It makes it very unreliable.

    Monday, February 17, 2020 4:18 PM

All replies

  • To:  mogulman52
    re:  adding a worksheet

    Have you tired specifying the Workbook the new worksheet goes in? ...

    Set ws1 = Workbooks("Name").Worksheets.Add(Before:=Workbooks("Name").Worksheets(1), type:=xlWorksheet)

    The free Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell Or each row against all rows (two worksheets) - choice of compare type - fast)
    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents
    Wednesday, February 19, 2020 4:00 AM
  • Thanks for suggestion however it still fails.
    Wednesday, February 19, 2020 7:57 PM