none
COM Error when modifying and saving a file that has a solver reference RRS feed

  • Question

  • I am using COM to access, modify and save a file that has a reference to the SOLVER component.

    When saving,  we have a COM exception.

    D:\ExcelCOM>excelcom 29811.L6Q.000.oilSheet.xlsm
    Created Excel application
    Opened 29811.L6Q.000.oilSheet.xlsm
    Active sheet is "oil data"
    Accessed "gas data" ranges
    Reading first row
    str: Regular
    num: 2000
    num: 60
    num: 8
    num: 2
    accessed writing sheet "result"
    Succeeded writing to sheet
    Saved
    Com Exception:
            Code = -2146827284
            Code meaning = Unknown error 0x800A03EC
    Quit

    I can provide the C++ code that reproduces the error as well as the Excel xslm file.

    What is the error cause and how to fix it ?

    Tuesday, January 9, 2018 9:30 AM

Answers

  • Hi Frédéric Delhoume - IBM,

    Thanks for the detailed information. After further investigation, the root cause for this issue is that the workbook is not the correct object represent which work you opened when the solver add-in is enabled. You can verify this simply check the name or fullpath property of workbook. It actually is the SOLVER.XLAM. 

    To fix this issue, please use the workbook object from range object. Since I am not familiar with C++, here is a C# code sample for your reference:

            static void Main(string[] args)
            {
                Excel.Application app = new Excel.Application();
                app.DisplayAlerts = false;
    
                Excel.Workbook workbook=app.Workbooks.Open(@"29811.L6Q.000.oilSheet.xlsm");
    
                Excel.Range range = app.Range["RESULT!A2:A4"];
                range[2][2] = 5;
                range[2][3] = 8;
                var workbookName = workbook.Name;
                Excel.Workbook myWorkbook = range.Worksheet.Parent;
                myWorkbook.Save();
                myWorkbook.Close();
                app.Quit();
    
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);
            }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 16, 2018 8:13 AM
    Moderator

All replies

  • Hi Frédéric Delhoume - IBM,

    please try to check that the file you are trying to modify and save is not a Read Only file.

    if it is a Read Only file and you try to save the modification then it can generate an error.

    so if the file is Read Only then remove the Read Only Permission and provide Read Write permission to that file.

    if you are not using and error handling in your code then try to implement it and catch the error using it.

    it will help us to get error description which can help us to understand the issue in better way.

    also try to make a test on other machine and check whether you are able to reproduce the issue or not.

    other thing you can try is to make a test with SaveAs method and check whether it lets you to Save As file or not.

    you can share your code and file here.

    We will try to make a test and try to reproduce the issue on our side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 10, 2018 6:10 AM
    Moderator
  • The file has all access rights, and the only thing that prevents the Save to work is that it contains a reference to the SOLVER component. Without this reference modifying and saving works.

    Here is the C++ code:

    //#import "mso9.dll" no_namespace rename("DocumentProperties", "DocumentPropertiesXL") rename("RGB", "RBGXL")
    //#import "vbe6ext.olb" no_namespace rename("_Windows", "_WindowsXL")
    //#import "C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\EXCEL.exe" rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") \
    //    rename("DocumentProperties", "DocumentPropertiesXL") rename("CopyFile", "CopyFileXL") rename("ReplaceText", "ReplaceTextXL") 
      
    // cl /EHsc excelcom.cpp
    
    #include "mso9.tlh"
    #include "vbe6ext.tlh"
    #include "excel9.tlh"
    
    #include <iostream>
    
    using namespace std;
    
    void DisplayComError(const _com_error& e) {
            cout << "Com Exception:" << endl;
            cout << "\tCode = " << e.Error() << endl;
             cout << "\tCode meaning = " << e.ErrorMessage() << endl;
     		    _bstr_t bstrSource(e.Source());
         if (!bstrSource) {} else {
    		    cout << "\tSource = " << (LPCTSTR) bstrSource << endl;
    	  }
             _bstr_t bstrDescription(e.Description());
          if (!bstrDescription) {} else {
    		   cout << "\tDescription = " << (LPCTSTR) bstrDescription << endl;
    	   }
    }
    
    static char* convert(_bstr_t text) {
        char* str = 0;
        size_t length = strlen((const char*)text);
        str = new char[length+1];
        strncpy(str, (const char*)text, length);
        str[length] = '\0';
        return str;
      }
    
    
    int main(int argc, char* argv[]) {
        CoInitializeEx(0, COINITBASE_MULTITHREADED);
        Excel::_ApplicationPtr pXL;
        try {
        pXL.CreateInstance(L"Excel.Application");
        cout << "Created Excel application" << endl;
        Excel::WorkbooksPtr books = pXL->Workbooks;
        // argv[1] must be full path as we are loading from Excel, not current directory
        Excel::_WorkbookPtr book = books->Open(argv[1], 0);
        cout << "Opened " << argv[1] << endl;
        pXL->PutVisible(0, FALSE);
        Excel::_WorksheetPtr pSheet = pXL->ActiveSheet;
        cout << "Active sheet is \"" << pSheet->Name << "\"" << endl;
        //Excel::RangePtr pRange = pSheet->Cells;
        Excel::RangePtr pRange = pXL->GetRange(L"'gas data'!A2:A4");
        cout << "Accessed \"gas data\" ranges" << endl;
        cout << "Reading first row" << endl;
    
        Excel::WorksheetFunctionPtr func = pXL->GetWorksheetFunction();
    
        // Excel ranges start at 1
        for (int idx = 1; idx < 6; ++idx) {
            _variant_t content = pRange->Item[2][idx];
            if(func->IsNumber(content) == VARIANT_TRUE)
              cout << "num: " << (double)content << endl;
            else if (func->IsText(content) == VARIANT_TRUE) {
                _bstr_t result(content);
              cout << "str: " << (const char*)convert(result) << endl;
            }
        }
        // now write, crashes if sheet does not exists
        // TODO : parse and create the sheet if it does not exists
        //    Excel::_WorksheetPtr newsheet = book->Worksheets->Add();
       //   newsheet->Name = "NewSheet";
       //   cout << "Created new sheet" << endl;
    
        Excel::RangePtr wRange = pXL->GetRange(L"RESULT!A2:A4");
        cout << "accessed writing sheet \"result\"" << endl;
        wRange->Item[2][2] = 1234;
        wRange->Item[2][3] = 7890;
        cout << "Succeeded writing to sheet" << endl;
    
        pXL->PutDisplayAlerts(LOCALE_USER_DEFAULT, VARIANT_FALSE);
    	book->Save();
    	cout << "Saved " << endl;
    	book->Close(vtMissing, vtMissing, vtMissing, 0);
    	cout << "Closed" << endl;
    	} catch(const _com_error& e) {
    		DisplayComError(e);
    	} 
        pXL->Quit();
        cout << "Quit" << endl;
        CoUninitialize();
        return 0;
    }
    How do I insert the Excel xlsm file here ?


    Wednesday, January 10, 2018 4:50 PM
  • Hi Frédéric Delhoume - IBM,

    you can try to share the file using any free online file hosting and then you can post the link here.

    we will try to visit the link and try to get the file.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 12, 2018 6:50 AM
    Moderator
  • https://www.dropbox.com/s/lb196sl2mbloybz/29811.L6Q.000.oilSheet.xlsm?dl=0

    Usage (with compiled code as excelcom) :

    excelcom <path to>29811.L6Q.000.oilSheet.xlsm

    excel file
    Complete project
    Friday, January 12, 2018 9:32 AM
  • Hi Frédéric Delhoume - IBM,

    I am also able to reproduce this issue using the excel file you uploaded. However, the issue is not able to reproduced when I create a new workbook and enable the solver add-in.

    This issue seems to be relative to this specific workbook, would you mind sharing what did you for the specific for the workbook to help us to reproduce this issue?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 15, 2018 9:53 AM
    Moderator
  • The workbook comes from a customer, I think it is just a standard workbook, the Solver add-in comes from 

    Developper/Add-Ins/Solver add-in I guess...

    Sometimes it works, maybe the first time I run after a reboot...

    D:\ExcelCOM>excelcom.exe d:\ExcelCOM\29811.L6Q.000.oilSheet.xlsm
    Created Excel application
    Opened d:\ExcelCOM\29811.L6Q.000.oilSheet.xlsm
    Active sheet is "oil data"
    Accessed "gas data" ranges
    Reading first row
    str: Regular
    num: 2000
    num: 60
    num: 8
    num: 2
    accessed writing sheet "result"
    Succeeded writing to sheet
    Saved
    Closed
    Quit

    D:\ExcelCOM>excelcom.exe d:\ExcelCOM\29811.L6Q.000.oilSheet.xlsm
    Created Excel application
    Opened d:\ExcelCOM\29811.L6Q.000.oilSheet.xlsm
    Active sheet is "oil data"
    Accessed "gas data" ranges
    Reading first row
    str: Regular
    num: 2000
    num: 60
    num: 8
    num: 2
    accessed writing sheet "result"
    Succeeded writing to sheet
    Saved
    Com Exception:
            Code = -2146827284
            Code meaning = Unknown error 0x800A03EC
    Quit

    If you reproduce, then maybe you are able to find out what is happening in the failing workbook ?

    Monday, January 15, 2018 10:15 AM
  • The difference I see between 29811.L6Q.000.oilSheet.xlsm and oilSheet.xlsm is the entry "Reference to SOLVER.XLAM" in References.

    And trying to expand Solver (SOLVER.XLAM) leads to a Password dialog.


    Monday, January 15, 2018 10:34 AM
  • Hi Frédéric Delhoume - IBM,

    Thanks for the detailed information. After further investigation, the root cause for this issue is that the workbook is not the correct object represent which work you opened when the solver add-in is enabled. You can verify this simply check the name or fullpath property of workbook. It actually is the SOLVER.XLAM. 

    To fix this issue, please use the workbook object from range object. Since I am not familiar with C++, here is a C# code sample for your reference:

            static void Main(string[] args)
            {
                Excel.Application app = new Excel.Application();
                app.DisplayAlerts = false;
    
                Excel.Workbook workbook=app.Workbooks.Open(@"29811.L6Q.000.oilSheet.xlsm");
    
                Excel.Range range = app.Range["RESULT!A2:A4"];
                range[2][2] = 5;
                range[2][3] = 8;
                var workbookName = workbook.Name;
                Excel.Workbook myWorkbook = range.Worksheet.Parent;
                myWorkbook.Save();
                myWorkbook.Close();
                app.Quit();
    
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(app);
            }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 16, 2018 8:13 AM
    Moderator
  • Hi,

    That is great news !

    I have not been able to translate Range.Worksheet.Parent; in C++ but it seems that using the Parent of the default sheet works !

    // FIX !
    Excel::_WorksheetPtr aSheet = pXL->ActiveSheet;

    book = aSheet->Parent;

    Then book->Save(); works !

    and the name of book is the xlsm file, not the xlam anymore.

    Tuesday, January 16, 2018 9:49 AM