Re: How to create an OLE control in EXCEL sheet? - COM Discussion Boards

27-Apr-11 23:46

Hi, all!

I had met a problem when I developing an addin for Excel. I want to create a new OLE control which developed by myself into Excel sheet. But an error occurred when I add it.

I had try to do this by two ways. One is to call the method "Add" of the class "OLEObjects". Another is to call the method "AddOLEObject" of the class "Shapes". Both are all failure.

When I call OLEObject.Add, an exception was throwed which said a com_error occured in Excel. The code I used is as below:

CComPtr<MSEXCEL::_Workbook> spBook;
HRESULT hr = m_spAppExcel->get_ActiveWorkbook(&spBook);
if(spBook == NULL)
{
    return;
}
CComPtr<MSEXCEL::Sheets> spSheets;
spBook->get_Sheets(&spSheets);
CComPtr<MSEXCEL::OLEObjects> spObjs;
CComVariant vtIndex(1);
spSheets->get_Item(vtIndex,(IDispatch**)&spObjs);
VARIANT vtResult;
VariantInit(&vtResult);
CComVariant ClassType(_MYPROGID);
CComVariant vtFalse(VARIANT_FALSE);
CComVariant left,top;
int x,y;
GetInsertPosX(x,y);
left=x; top = y;
CComVariant vEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
CComPtr<MSEXCEL::_OLEObject> pObj;
try
{
    pObj = spObjs->Add(ClassType,vEmpty,vtFalse,vtFalse,vEmpty,vEmpty,vEmpty,left,top,g_iCtrlInitWidth,g_iCtrlInitHeight);
    if(pObj == 0)
    {
        //Failure
        return;
    }
}
catch(...)
{
    DWORD dwError = GetLastError();
    TCHAR info[10];
    wnsprintf(info,10,L"%x",dwError);
    MessageBox(NULL,info,L"Error",MB_OK);
    return;
}

When I call Shapes.AddOLEObject, the calling is success. But the value it returned can't be converted to the pointer I need. The code is as below:

CComPtr<MSEXCEL::_Workbook> spBook;
HRESULT hr = m_spAppExcel->get_ActiveWorkbook(&spBook);
if(spBook == NULL)
{
    return;
}
CComPtr<MSEXCEL::_Worksheet> spSheet;
spBook->get_ActiveSheet((IDispatch**)&spSheet);
CComPtr<MSEXCEL::Shapes> spShapes;
spSheet->get_Shapes(&spShapes);
ATLASSERT(spShapes);
VARIANT vtResult;
VariantInit(&vtResult);
CComVariant ClassType(_MYPROGID);
CComVariant vtFalse(VARIANT_FALSE);
CComVariant left,top;
int x,y;
GetInsertPosX(x,y);
left=x; top = y;
CComVariant vEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
CComPtr<MSEXCEL::Shape> spCtrl;
spCtrl = spShapes->AddOLEObject(ClassType,vEmpty,vEmpty,vtFalse,vEmpty,0,vEmpty,left,top,g_iCtrlInitWidth,g_iCtrlInitHeight);
if(spCtrl == 0)
{
    //Failure
    return;
}
CComPtr<MSEXCEL::OLEFormat>spOleFormat;
spOleFormat = spCtrl->get_OLEFormat();
IDispatch *pObj;
pObj = spOleFormat->get_Object();
CComQIPtr<ICASign> cpCASign(pObj);//Here an error occur. The value of pObj is valid. But the value of cpCASign is zero!
if(cpCASign == 0)
{
    return;
}

When I use the second way, I had found the constructor of CCASign had been executed. But when constructor is return, the value of cpCASign is still zero. I think maybe ICASign isn't matched with CCASign. But this object can be inserted into Word correctly. When adding this object into word, the same procedure is done. The value after converted is correct. So I don't know the reason of this error. I hope someone could be kind to help me. Thank you!

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman28-Apr-11 18:52

28-Apr-11 18:52

CComPtr<MSEXCEL::OLEFormat>spOleFormat;
spOleFormat = spCtrl->get_OLEFormat();
IDispatch *pObj;
pObj = spOleFormat->get_Object();
CComQIPtr<ICASign> cpCASign(pObj);//Here an error occur. The value of pObj is valid. But the value of cpCASign is zero!

change that to ...

CComPtr<MSEXCEL::OLEFormat>spOleFormat;
spOleFormat = spCtrl->get_OLEFormat();

// changed
CComPtr<IDispatch> pObj;
pObj = spOleFormat->get_Object();
CComPtr<ICASign> cpCASign;

// changed
HRESULT hr=pObj.QueryInterface(&cpCASign);

and at least you'll have a error code to work with

whiteclouds28-Apr-11 21:07

28-Apr-11 21:07

Problem still exist. The value of hr is "E_NOINTERFACE". I feel very strange that the same operation can completed within Word. I check the interface declaration of CCASign many times. No mistake was found. Please continue to help me! Thank you!

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman29-Apr-11 13:55

29-Apr-11 13:55

what's the object written using?

if it's ATL, use the _ATL_DEBUG_QI to watch for it being queried

Not done much with Excel or Word, but it wouldn't surprise me if they use separate threading models, and the E_NOINTERFACE you're getting is because it can't get to an IMarshall interface

have a look at http://msdn.microsoft.com/en-us/library/ms687205(v=vs.85).aspx[^]

whiteclouds1-May-11 19:21

1-May-11 19:21

My control is written in C++. I had read the article you recommand. My control's ThreadModel is Single-Threaded apartment. I had tried the other key-word. Only "Apartment" and "Both" can work. I think maybe I hadn't implement some methods, so the conversion is failure. I hope you can list all methods I must implement. Thank you!

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman1-May-11 19:30

1-May-11 19:30

ok - first things first - you need to find out if this is the problem

If you're using ATL, enable the _ATL_DEBUG_QI define for the project - this will show you what your project is being queried for (as long as you are running your object under the debugger)

If you're not using ATL, you'll have to change your QueryInterface method so it prints out interfaces that are requested, that it doesn't support

whiteclouds2-May-11 15:48

2-May-11 15:48

I had add _ATL_DEBUG_QI define in my project. Then message below is output:

CComClassFactory - IUnknown
CComClassFactory - IClassFactory2 - failed
CComClassFactory - IClassFactory
CCASign - IDispatch
CCASign - IConnectionPointContainer
CCASign - IProvideClassInfo
CCASign - IConnectionPointContainer
CCASign - IProvideClassInfo
CCASign - IOleObject
CCASign - IOleControl
CCASign - {049948D1-5686-11CF-8E0D-00AA00A74C5C} - failed
CCASign - IPersistStreamInit
CCASign - IConnectionPointContainer
CCASign - IProvideClassInfo
CCASign - IOleContainer - failed
CCASign - IDataObject
CCASign - IPersistPropertyBag - failed
CCASign - IRunnableObject - failed
ATL: IDataObjectImpl::QueryGetData not implemented.
_CrtDbgReport: String too long or IO ErrorCCASign - IRunnableObject - failed
CCASign - {049948D1-5686-11CF-8E0D-00AA00A74C5C} - failed
CCASign - {049948D1-5686-11CF-8E0D-00AA00A74C5C} - failed
CCASign - IRunnableObject - failed
ATL: IDataObjectImpl::QueryGetData not implemented.
_CrtDbgReport: String too long or IO ErrorCCASign - IRunnableObject - failed
CCASign - IPersistStreamInit

These message are output when calling Shapes.AddOleObject. I check the interface marked as fail. Such interfaces are not needed to implement except "{049948D1-5686-11CF-8E0D-00AA00A74C5C}". I don't know what interface is "{049948D1-5686-11CF-8E0D-00AA00A74C5C}". My own interface is registered already. And its CLSID isn't {049948D1-5686-11CF-8E0D-00AA00A74C5C}. Is this the reason of problem?

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman2-May-11 17:11

2-May-11 17:11

your addoleobject call is succeeding

You need to look at the debug trace when you call

HRESULT hr=pObj.QueryInterface(&cpCASign);

because that's the call that is failing

whiteclouds3-May-11 0:38

Re: How to create an OLE control in EXCEL sheet? [modified]

3-May-11 0:38

I had make clear that the reason of problem is the interface I need i can't be queried.
When the following code executed, nothing output.

HRESULT hr=pObj.QueryInterface(&cpCASign);

But i don't know how to fix it. Pls help me. Thx!

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman3-May-11 13:57

3-May-11 13:57

ok - that would imply that the pObj you get back is not your object

does excel manufacture some form of object-site object?

looking at the vba excel docs, your object should also turn up in

OLEObjects

container; I would write some code to see if I get the same, or different behaviour

i.e.
Add object to shapes as you are currently doing
then look for it in the OLEObjects container
then QI that object for your interface

modified on Tuesday, May 3, 2011 8:07 PM

whiteclouds3-May-11 15:29

3-May-11 15:29

Pleasure to see your reply. Thank you very much!
Some days ago, I find a message at MSDN. It said that if you want to add an OLE object into Excel worksheet, you should call OLEObjects.Add(), not Shapes.AddOLEObject(), because Shapes encapsulate the OLEObject returned by AddOLEObject. So the next operation such as OLEFormat.get_Object() can't get the raw point of the object inserted.
I had tried the way it recommend. But I can't get a OLEObjects from worksheet. I find that the reason of failure is the wrong LANGID parameter. I am trying to find the right parameter for it now. If you can't be success from the code above, you can try to do by this way. And if you know what parameter of LANGID is right, I will be very pleased to know it from you. Big Grin | :-D

Sincerely

There is some white cloud floating on the blue sky. That's the landscape I like.

whiteclouds3-May-11 21:16

3-May-11 21:16

The message I get tell me that I should call _WorkSheet.OLEObjects() to get an OLEObjects, then call OLEObjects.Add() to insert my own object. But I get an error whose code is 0x800A03EC. This means I should set the right CultureInfo before calling OLEObjects.Add(). But I can't find any hint to set it besides using .NET framework. Should I using .NET just for this?
I had try to call GetSystemDefaultLCID() and ConvertDefaultLocale() to fix it. But the same error occurred. What shall I do?

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman3-May-11 22:15

3-May-11 22:15

I'm afraid I don't know - sorry ...

Things *I* would look at ...

1. What interfaces are being queried from your object during the OLEObjects.Add; do any of those want or return an LCID - if no interfaces are being queried it may be something missing from your type library

2. I would try doing the Shapes.AddObject as before, but then querying the OLEObjects rather than the object shape returns - the docs suggest the object will turn up in both places

3. If neither of those got me anywhere, I'd start looking at the object Shapes.AddObject returned, querying it for standard container interfaces and see if i could get to the 'real' object underneath (IOleContainer for instance)

best of luck

whiteclouds3-May-11 23:32

3-May-11 23:32

Sorry, maybe I make you nervous. That's my fault. Smile | :)

1. Nothing output when I call OLEObjects.Add. Maybe it's because I call the Dispatch.Invoke() to add. The reason of I call Dispatch.Invoke() instead of OLEObjects.Add() is that an error of 0x800A03EC always occurred for OLEObjects.Add().
2. I can't get the meaning of your second suggestion. You tell me to query OLEObjects at the object Shapes.AddObject returned?
3. Your meaning is that I should query some container interfaces such as IOleContainer for the object returned by Shapes.AddOLEObject(), isn't it? Why?

By the way, the error of 0x800A03EC isn't means wrong cultureinfo. The correct meaning is that another operation background is executed on the same object. I must make the object such as button lost focus. This is a bug for Office 2003. Smile | :)

Following is the explanation from Microsoft:
http://support.microsoft.com/kb/823988/en-us[^][]

I had try to do as the code provided by Microsoft. But nothing changed.

I hope you can help me in advance. Thank you!

There is some white cloud floating on the blue sky. That's the landscape I like.

barneyman4-May-11 18:15

4-May-11 18:15

2. make the previous

spShapes->AddOLEObject(ClassType,vEmpty,vEmpty,vtFalse,vEmpty,0,vEmpty,left,top,g_iCtrlInitWidth,g_iCtrlInitHeight);

call, but then look for your object in the OLEObjects() collection

3. Yes - I am assuming that Shape wraps your object in some form of container, so you *might* be able to get to that container, and ask IT for the enclosed object - alternatively, from within your IOleObject, find out what it's clientSite is, and see if that's the object Shape is giving you

whiteclouds4-May-11 22:16