C++ – Excel OpenText method

ccomexcel

I keep getting the ambiguous error code of 0x800A03EC.

I've been searching quite a bit to see if I could find a specific reason for the error but unfortunately that code seems to cover a multitude of possible errors. I will copy and paste the code that seems to be giving me problems and hopefully someone will be able to provide me with some feedback on how I might solve the problem. I am using a method called AutoWrap that I came across in this kb21686 article.

I'll add that method here:

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
    // Begin variable-argument list...
    va_list marker;
    va_start(marker, cArgs);

    if(!pDisp) {
        //MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
        MessageBox(NULL,_T("IDispatch error"),_T("LError"),MB_OK | MB_ICONEXCLAMATION);
        _exit(0);
    }

    // Variables used...
    DISPPARAMS dp = { NULL, NULL, 0, 0 };
    DISPID dispidNamed = DISPID_PROPERTYPUT;
    DISPID dispID;
    HRESULT hr;
    char buf[200];
    char szName[200];


    // Convert down to ANSI
    WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

    // Get DISPID for name passed...
    hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
    if(FAILED(hr)) {
        sprintf_s(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
        MessageBox(NULL, CString(buf), _T("AutoWrap()"), MB_OK | MB_ICONEXCLAMATION);
        _exit(0);
        return hr;
    }

    // Allocate memory for arguments...
    VARIANT *pArgs = new VARIANT[cArgs+1];
    // Extract arguments...
    for(int i=0; i<cArgs; i++) {
        pArgs[i] = va_arg(marker, VARIANT);
    }

    // Build DISPPARAMS
    dp.cArgs = cArgs;
    dp.rgvarg = pArgs;

    // Handle special-case for property-puts!
    if(autoType & DISPATCH_PROPERTYPUT) {
        dp.cNamedArgs = 1;
        dp.rgdispidNamedArgs = &dispidNamed;
    }

    // Make the call!
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
    if(FAILED(hr)) {
        sprintf_s(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
        MessageBox(NULL, CString(buf), _T("AutoWrap()"), MB_OK | MB_ICONEXCLAMATION);
        _exit(0);
        return hr;
    }
    // End variable-argument section...
    va_end(marker);

    delete [] pArgs;

    return hr;
}

Everything works fine up until I make this call:

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"OpenText",18,param1,vtMissing,vtMissing,paramOpt,paramOpt,
                vtMissing,vtMissing,vtMissing,paramTrue,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
                ,vtMissing,vtMissing);

The parameters passed to the function are initialized as:

       VARIANT param1,paramOpt,paramFalse,paramTrue;
        param1.vt = VT_BSTR;
        paramOpt.vt = VT_I2;
        paramOpt.iVal = 1;
        paramFalse.vt = VT_BOOL;
        paramFalse.boolVal = 0;
        paramTrue.vt = VT_BOOL;
        paramTrue.boolVal = 1;
        //param1.bstrVal = ::SysAllocString(L"C:\\Documents and Settings\\donaldc\\My Documents\\DepositSlip.xls");
        param1.bstrVal = ::SysAllocString(L"C:\\logs\\TestOut.txt");

If I uncomment the commented out param1 and make a call to Open and pass it that version of param1 everything works wonderfully. Unfortunately when Invoke is called on the OpenText method I get the 0x800A03EC error code. 90% of what I find when searching is performing automation using interop in C# and the other 10% is doing the same thing in VB and while the C# examples are helpful they don't help to explain the parameters being passed when using C++ very well. I feel like it's all a problem with parameters but I'm having difficulty in figuring out exactly what the problem with them is.

Thanks in advance for any help you can offer and pelase let me know if I need to post more code.

Best Answer

From the KB article you linked to:

One caveat is that if you pass multiple parameters, they need to be passed in reverse-order.

From MSDN, the parameters to OpenText are:

expression.OpenText(Filename, Origin, StartRow, DataType, 
TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, 
Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, 
ThousandsSeparator, TrailingMinusNumbers, Local)

So if param1 holds your filename then you are currently trying to pass that as the Local parameter and you aren't passing anything to the Filename parameter which is required

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

C# – How to properly clean up Excel interop objects

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

Best Answer

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

C# – How to properly clean up Excel interop objects

Related Topic