Use VC++ Automation to hide Excel MenuBar problem

See more:

C++

Windows

I tried to use VC++ MFC automation to hide the Excel "Worksheet menu bar".

However, after CommandBar.SetEnabled(false); the menu bar still exists.

I also tried CommandBar.SetVisible(false); but got an OLEException.

Here is my code, can anyone help me to solve this problem?

void CEx25dView::OnExceloleExecute() 
{
   LPDISPATCH pRange, pWorkbooks, pCommandBars;
   CWnd* pWnd = CWnd::FindWindow("XLMAIN", NULL);
   
   if (pWnd != NULL) {
     TRACE("Excel window found\n");
     pWnd->ShowWindow(SW_SHOWNORMAL);
     pWnd->UpdateWindow();
     pWnd->BringWindowToTop();
   }
    
   m_app.SetSheetsInNewWorkbook(1);

   VERIFY(pWorkbooks = m_app.GetWorkbooks());
   m_workbooks.AttachDispatch(pWorkbooks);

   LPDISPATCH pWorkbook = NULL;
   if (m_workbooks.GetCount() == 0) {
       COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
       pWorkbook = m_workbooks.Add(covOptional); 
   }
   
   LPDISPATCH pWorksheets = m_app.GetWorksheets();
   ASSERT(pWorksheets != NULL);
   m_worksheets.AttachDispatch(pWorksheets);
   
   pCommandBars = m_app.GetCommandBars();
   m_commandbars.AttachDispatch(pCommandBars);
   int CommandBarCount = m_commandbars.GetCount();
   TRACE("CommandBarCount = %d\n", CommandBarCount);
    
   char buf[2048]; 
   TRACE("TRUE %d,   FALSE %d\n", TRUE, FALSE);
   CommandBar oBar(m_commandbars.GetItem(COleVariant((short) 1))); // param 1(index 1) = Worksheet enu bar, equivalent to m_commandbars.GetActiveMenuBar();

   sprintf(buf, "name:%s, nameLocal:%s, Context:%s, Height:%d, Index:%d, Left:%d, width:%d, Type:%d, Visual:%d, Enabled:%d \n"
        , (LPCTSTR)(oBar.GetName()), (LPCTSTR)(oBar.GetNameLocal()), (LPCTSTR)(oBar.GetContext()), oBar.GetHeight(), oBar.GetIndex(), oBar.GetLeft(), oBar.GetWidth(), oBar.GetType(), oBar.GetVisible(), oBar.GetEnabled());

   TRACE(buf);
   oBar.SetEnabled(false);    // no effect, Menubar still exists.
   //oBar.SetVisible(false);  // GOT OLEException
    
   
	// cleanup  
    if (pWorkbook != NULL) {
        pWorkbook->Release();
    }
}


void CEx25dView::OnUpdateExceloleExecute(CCmdUI* pCmdUI) 
{
   pCmdUI->Enable(m_app.m_lpDispatch != NULL);
}

void CEx25dView::OnExceloleLoad() 
{   // if Excel is already running, attach to it, otherwise start it
   LPDISPATCH pDisp;
   LPUNKNOWN pUnk;
   CLSID clsid;
   TRACE("Entering CEx25dView::OnExcelLoad\n");
   BeginWaitCursor();
   ::CLSIDFromProgID(L"Excel.Application.12", &clsid); // from registry
   if(::GetActiveObject(clsid, NULL, &pUnk) == S_OK) {
      VERIFY(pUnk->QueryInterface(IID_IDispatch,
            (void**) &pDisp) == S_OK);
      m_app.AttachDispatch(pDisp);
      pUnk->Release();
      TRACE(" attach complete\n");
  } 
   else {
	   if(!m_app.CreateDispatch("Excel.Application.12")) {
			AfxMessageBox("Excel 97 program not found");
	   }
       TRACE(" create complete\n");
   }
   EndWaitCursor();
OnExceloleExecute() ;
}

void CEx25dView::OnUpdateExceloleLoad(CCmdUI* pCmdUI) 
{
   pCmdUI->Enable(m_app.m_lpDispatch == NULL);
}

My code was modified from the famous book : Inside Visual C++ 6.0 Version 5 demo code CEx25d.<br />
Compile Environment: Visual Studio 2005, Windows XP. Excel 2007.<br />

:)