Embedded Excel Problem with Menus/Commandbars/Toolbars etc

[Ashish]

Hello Everyone:

I been having this weired embedded excel problem. I am trying to create a
commandbar control in embedded excel with some buttons in it. It is able to
create everything when I am creating an Excel.Application. However, I am not
able to create any commandbar or button or anything when I am creating
Excel.sheet / embedded excel. I am using the following turorial:
http://support.microsoft.com/kb/q194906/ , which definetly works in case of
Excel.Application. But the code below doesn't work.


TRY
{
//Get the document associated with this view, and be sure that it is
//valid.
CSG3GDoc* pDoc = GetDocument();
ASSERT_VALID(pDoc);

// Create a new item associated with this document, and be sure that it is
valid.
pItem = new CSG3GCntrItem(pDoc);
ASSERT_VALID(pItem);

// Get a Class ID for the Excel sheet. This is used in creation.

CLSID clsid;
if(FAILED:CLSIDFromProgID(L"Excel.sheet",&clsid)))
AfxThrowMemoryException();

// Create the Excel embedded item.
if(!pItem->CreateNewItem(clsid))
AfxThrowMemoryException();

// Make sure the new CContainerItem is valid.
ASSERT_VALID(pItem);

// Start the server to edit the item.
pItem->DoVerb(OLEIVERB_SHOW, this);

/// save Control pointer
m_pSelection = pItem;

LPDISPATCH lpDisp = pItem->GetIDispatch();

_Application ap; ap.AttachDispatch(lpDisp);

ap = ap.GetApplication();

pExcelEvHandle = new CExcelEvHandle(this);
pExcelEvHandle->AddRef();
pExcelEvHandle->AttachToSource(ap.m_lpDispatch);
Col = r.GetColumn();
HRESULT hr;
VARIANT vResult;
char buf[1024]; // General purpose message buffer
// Convenient values declared as ColeVariants.
COleVariant covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
long lCount = 0;
CommandBarControls oNewControls;
_CommandBarButton oNewButton2, oButton, oNewButton1;
CommandBar oNewBar;


//build a new commandbar for excel and add the commandbar to xl collection
hr = ap.m_lpDispatch->GetIDsOfNames(IID_NULL, &strCBs, 1,
LOCALE_SYSTEM_DEFAULT, &dispID);

if (FAILED(hr))
{
sprintf(buf,"Failed to GetIDsOfNames() ... Error = %08lx",
(long)hr);
AfxMessageBox(buf,MB_SETFOREGROUND);
}

//Get a dispatch pointer to commandbars. Use that of running
application's existing menu
//confuguration. "vresult" is a VARIANT. It is declared above.
ap.InvokeHelper(dispID, //commandbars in this case
DISPATCH_METHOD | DISPATCH_PROPERTYGET,
VT_VARIANT, //type of return value
(void*)&vResult, //address of var reciving IDispatch of CmdBrs
NULL); //Pointer to parameters string

_CommandBars cbs(vResult.pdispVal); //construct the commandBars object
and attach the IDispatch
//pointer to it


lCount = cbs.GetCount(); // Word has 92!!??
// MSOffice reconfigures for each
// user-application.
//CString tmp; tmp.Format("LCount=%d", lCount); AfxMessageBox(tmp);


vResult.pdispVal = cbs.GetActiveMenuBar(); //returns a LPDISPATCH pointer
of the commandbar object that
//represents the active menu bar in the container application; that is MS
office configuration

CommandBar oBar(vResult.pdispVal); // Construct a new
// CommandBar object
// & attach the LPDispatch
// of the active menu bar

VARIANT vName;
vName.vt = VT_BSTR;
vName.bstrVal = SysAllocString(L"Groups Command Bar"); //Variant for the
name of new bar
oNewControls = oBar.GetControls();


VARIANT vPosition;
vPosition.vt = VT_I2;
vPosition.iVal = 1; //4 = Floating; 0 = Left;
//Variant for position of new bar
oBar = (CommandBarPopup)cbs.Add(vName, vPosition, covFalse, covTrue);




//CommandBar oNewBar;

//AfxMessageBox("Now adding new bar to cbs collection");

try {
oNewBar = cbs.Add(vName, //const Variant Name = Groups Command Bar
vPosition, //const Variant Position = At top
covFalse, //const variant (replace) Menubar
covTrue); //const variant temporary

}
catch (COleException &e)
{
AfxMessageBox("error occured in add function");
}
oNewBar.SetVisible(TRUE);

oNewControls = oNewBar.GetControls();
//object reference to collection
AfxMessageBox("test");

VARIANT vType;
vType.vt = VT_I4;
vType.iVal = 1;
//Control type is button

oNewButton2 = oNewControls.Add(vType, covOptional, //Id
covOptional, //Parameter
covOptional, //Before
covTrue); //temporary
oNewButton2.SetStyle(3); //msoButtonIconAndCaption
oNewButton2.SetCaption("End Group");
oNewButton2.SetTooltipText("Delete Group");
oNewButton2.SetVisible(TRUE);
oNewButton2.SetState(0); //msoButtonUp
oNewButton2.SetFaceId((long) 2186);
oNewButton2.SetOnAction("CloseExcel");


oNewButton1 =
oNewControls.Add(vType, // Type = msoControlButton
covOptional, // Id
covOptional, // Parameter
COleVariant((long)1), // Before
covTrue // Temporary
);
oNewButton1.SetStyle(3); // msoButtonIconAndCaption
oNewButton1.SetCaption("Macro");
oNewButton1.SetTooltipText("Run Macro");
oNewButton1.SetVisible(TRUE);
oNewButton1.SetState(0); // msoButtonUp
oNewButton1.SetFaceId((long) 186); // commented for temporary test
oNewButton1.SetOnAction("TestMacro");
AfxMessageBox("Buttons in place. Click 'Macro' to start Excel");



oNewButton1.ReleaseDispatch();
oNewButton2.ReleaseDispatch();
oNewControls.ReleaseDispatch();
oNewBar.ReleaseDispatch();
oBar.ReleaseDispatch();
cbs.ReleaseDispatch();






}

// Clean up if something went wrong.

CATCH(CException, e)
{
if (pItem)
{
pItem->Delete();
delete pItem;
}
AfxMessageBox("Failed to load Excel Application");
result = false;
}
END_CATCH


You can create an embedded excel worksheet using the following article:
http://support.microsoft.com/kb/q184663/

Any help would be greatly appreciated.

Thanks,
Ashish Misra