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
