troubleshooting Question

Adding new worksheets to an automatically generated Excel spreadsheet causes error in Excel upon closing

Avatar of jribble
jribble asked on
C++
2 Comments1 Solution547 ViewsLast Modified:
I am generating charts in Excel.  Everything is working well, except that, when I open the generated Excel file, I get a "Memory can not be read" error upon closing Excel the first time (it doesn't happen on subsequent tries).  Otherwise, the Excel spreadsheet behaves normally.  This error only occurs when I exercise the code that adds a worksheet (this happens when there are more than 3 worksheets).

Here is the code that appears to be causing this issue...

                  VARIANT prevSheetVar = {0};
                  prevSheetVar.vt = VT_DISPATCH;
                  prevSheetVar.pdispVal = objSheet.m_lpDispatch;
                  objSheet.m_lpDispatch->AddRef();
                  objSheets.Add(covOptional,prevSheetVar,covOptional,covOptional);


Below is the entire test function I am executing.

Thanks,

Joe

void CQaControlLimitsReportMediator::generateSimpleChart()
{
      // TODO: Add your control notification handler code here

      // Common OLE variants. These are easy variants to use for
      // calling arguments.
      COleVariant
            covTrue((short)TRUE),
            covFalse((short)FALSE),
            covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      _Application objApp;

      _Workbook objBook;
      Workbooks objBooks;
      Worksheets objSheets;
      _Worksheet objSheet;
      Range range;
      LPDISPATCH lpDisp;
      ChartObjects chartobjects;
      Charts charts;
      _Chart chart;
      CString keyValue;

      // Instantiate Excel and start a new workbook.
      objApp.CreateDispatch("Excel.Application");
      objApp.SetVisible(TRUE);
      objBooks = objApp.GetWorkbooks();
      objBook = objBooks.Add(covOptional);
      objSheets = objBook.GetWorksheets();

      CString tmp;

      tmp = objBook.GetName();

      CStringArray *letters = new CStringArray();
      letters->Add("A");
      letters->Add("B");
      letters->Add("C");
      letters->Add("D");
      letters->Add("E");
      letters->Add("F");

      CString chartTitle;
      

      CIterator* iterator = getControlLimitsGroups()->ValueIterator();

      int worksheetIndex = 1;

      for (int i = 0; i < 4; i++) {

            if (worksheetIndex > 3) {
                  VARIANT prevSheetVar = {0};
                  prevSheetVar.vt = VT_DISPATCH;
                  prevSheetVar.pdispVal = objSheet.m_lpDispatch;
                  objSheet.m_lpDispatch->AddRef();
                  objSheets.Add(covOptional,prevSheetVar,covOptional,covOptional);
            }

            lpDisp = objSheets.GetItem( COleVariant((short)(worksheetIndex)) );
            ASSERT(lpDisp);

            // Attach the lpDisp pointer to a Worksheet object.
            objSheet.AttachDispatch(lpDisp);

            CString sheetName;
            sheetName.Format("SimpleChart %d", i+1);
            objSheet.SetName(sheetName);

            objSheet.Activate();


            lpDisp = objSheet.GetRange(COleVariant("A1"), COleVariant("W2"));
//            lpDisp = objSheet.GetRange(COleVariant("A1"), COleVariant("W40"));
                                             // The range is from A1 to W40.
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                                  // to the range object.
            range.Clear();  // Could be ClearContents().

            lpDisp = objSheet.GetRange(COleVariant("A1"), COleVariant("A1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Unload Date"));

            lpDisp = objSheet.GetRange(COleVariant("B1"), COleVariant("B1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Control Min"));

            lpDisp = objSheet.GetRange(COleVariant("C1"), COleVariant("C1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Control Max"));
            
            lpDisp = objSheet.GetRange(COleVariant("D1"), COleVariant("D1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Spec Min"));
            
            lpDisp = objSheet.GetRange(COleVariant("E1"), COleVariant("E1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Spec Max"));

            lpDisp = objSheet.GetRange(COleVariant("F1"), COleVariant("F1"));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("Test Result"));

            CString cell;

            cell.Format("B2");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("1.5"));
            cell.Format("B3");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("1.7"));

            cell.Format("C2");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("2"));
            cell.Format("C3");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("3"));

            cell.Format("D2");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("4"));
            cell.Format("D3");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("4"));

            cell.Format("E2");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("5"));
            cell.Format("E3");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("5"));

            cell.Format("F2");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("3.2"));
            cell.Format("F3");
            lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);
            range.SetValue(COleVariant("3.5"));
/*/
            for (int j = 0; j < currentGroup->GetCount(); j++) {

                  CTableData *element = (CTableData *)currentGroup->ElementAt(j);

                  chartTitle.Format("%s, %s (%s)\n%s (%s)\n%s",
                        element->Get("NAME"),
                        element->Get("SHIPPER_LOCATION"),
                        element->Get("QA_SHIPPER_NUMBER"),
                        element->Get("INGREDIENT_NAME"),
                        element->Get("INGREDIENT_CODE"),
                        element->Get("QA_ASSAY_TYP_NA"));

                  CString cell;
                  CString unloadDate = element->Get("UNLOAD_DATE");

                  cell.Format("A%d", j+2);
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("UNLOAD_DATE")));


                  
                  cell.Format("B%d", j+2);
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("MIN_CONTROL_VALUE")));

                  cell.Format("C%d", j+2);
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("MAX_CONTROL_VALUE")));

                  cell.Format("D%d", j+2);
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("RESULT_MINIMUM")));

                  cell.Format("E%d", j+2);
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("RESULT_MAXIMUM")));

                  cell.Format("F%d", j+2);
                  CString testResult = element->Get("QA_TEST_RESULT");
                  lpDisp = objSheet.GetRange(COleVariant(cell), COleVariant(cell));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant(element->Get("QA_TEST_RESULT")));
            }
*/
            // The cells are populated. To start the chart,
            // declare some long variables and site the chart.
            long left, top, width, height;
            left = 100;
            top = 10;
            width = 450;
            height = 350;

            lpDisp = objSheet.ChartObjects(covOptional);

            ASSERT(lpDisp);
            chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
                                                                      // for ChartObjects to the chartobjects
                                                                   // object.
            ChartObject chartobject = chartobjects.Add(left, top, width, height);
                                    //defines the rectangle,
                                    // adds a new chart at that rectangle and
                                    // assigns its object reference to a
                                    // ChartObject variable named chartobject
            chart.AttachDispatch(chartobject.GetChart()); // GetChart() returns
                                                                                // LPDISPATCH, and this attaches
                                                                                // it to your chart object.

            CString startRange, endRange;
            startRange = "A1";
            endRange.Format("F3");
            lpDisp = objSheet.GetRange(COleVariant(startRange), COleVariant(endRange));
             // The range containing the data to be charted.
            ASSERT(lpDisp);
            range.AttachDispatch(lpDisp);

            VARIANT var; // ChartWizard needs a Variant for the Source range.
            var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
                                     // Struct. Its value is a union of options.
            var.pdispVal = lpDisp; // Assign IDispatch pointer
                                      // of the Source range to var.

            chart.ChartWizard(var,                    // Source.
                              COleVariant((short)4),   // Line type
                              covOptional,             // Format, use default.
                              COleVariant((short)2),   // PlotBy: xlRows.
                              COleVariant((short)1),   // CategoryLabels.
                              COleVariant((short)1),   // SeriesLabels.
                              COleVariant((short)TRUE),  // HasLegend.
                              COleVariant(chartTitle),  // Title.
                              COleVariant("Unload Date"),    // CategoryTitle.
                              COleVariant("Test Result"),  // ValueTitles.
                              covOptional              // ExtraTitle.
                              );

            worksheetIndex++;
      }
      delete letters;
      delete iterator;

      resetControlLimitsSortedKeys();

      VARIANT v;
      CString s;
      VariantInit(&v);
      v.vt = VT_BSTR;

      // Create a unique filename so we won't get prompted when saving.
      CMesTime *currentTime = new CMesTime(CMesTime::GetCurrentTime());
      CString currentTimeAsString = currentTime->toString();
      currentTimeAsString.Replace(":",".");

      CString logPath = CQaControlLimitsReportApp::getCfg()->getLogPath();
      s.Format("%s\\Control Limits Report (Ingredient-Assay-QA Shipper) %s.xls", logPath, currentTimeAsString);
      delete currentTime;

      v.bstrVal = s.AllocSysString();

      objBook.SaveAs(v, COleVariant(objBook.GetFileFormat()));
      objBook.SetSaved(TRUE);
      objApp.Quit();
      objApp.ReleaseDispatch();
}
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros