Remove Excel Menu Bar (Office 2007)

[Affe]

I'm trying to add an excel object into a GUI for the purpose of using it for graphing functions.

I'm currently stuck at the following example set of code, as I cannot seem to remove the menu that comes from Excel (note, you will need an excel file to open with this):

expandcollapse popup

#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
;
; Embedding an Excel document inside an AutoIt GUI
;
; Limitations:
;
; 1. Integrating the GUI Menu with the Objects Menu does not work.
;   (they have seperate menu bars)
;

; Initialize my error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

;~ $FileName=@ScriptDir & "\Worksheet.xls"
$FileName = FileOpenDialog("Pick a file", @ScriptDir, "Excel Files (*.xls;*.xlsx)")

if not FileExists($FileName) then
  Msgbox (0,"Excel File Test","Can't run this test, because it requires an Excel file in "& $FileName)
  Exit
endif

$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename

if IsObj($oExcelDoc) then
    GUICreate ( "Embedded ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_MINIMIZEBOX +$WS_SYSMENU + $WS_CLIPCHILDREN)

    $GUI_ActiveX    = GUICtrlCreateObj  ( $oExcelDoc, 1, 95 , 400 , 300 )

    For $Bar In $oExcelDoc.CommandBars
        If $Bar.Enabled = True Then $Bar.Enabled = False
        If $Bar.Visible = True Then $Bar.Visible = False
    Next
    $oExcelDoc.Application.DisplayFormulaBar = False
    $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
    $oExcelDoc.Application.DisplayScrollBars = True
    $oExcelDoc.Application.DisplayStatusBar = False

    GUISetState (@SW_SHOW)    ;Show GUI

   ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE;or $msg = $GUI_FileExit
                ExitLoop
        EndSelect
    Wend

    GUIDelete ()

   ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close (0)       ; Close the Excel workbook - Save prompt will not open

EndIf


Exit

; This is my custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"   & @CRLF  & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description  & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "     & @TAB & $HexNumber            & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "     & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile   & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc

Is there any way to remove the menu bar? All I search seem to still have this issue unresolved.

[water]

Do you get an error by the COM error handler?

[Affe]

No, I do not.

[water]

I see. Will do some testing tomorrow.

[water]

I searched the web and got the impression that you can't do it the way you do it now. You need to modify the User Interface.

Here is a document that describes what needs to be done.

[Bert]

This is a snippet of code I wrote 4 years ago but it worked then and may give you some direction:

;Turns off all command bars in excel to prevent user from making changes
    For $Bar In $oExcelDoc.CommandBars
        If $Bar.Enabled = True Then $Bar.Enabled = False
        If $Bar.Visible = True Then $Bar.Visible = False
    Next
    $oExcelDoc.Application.DisplayFormulaBar = False
    $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
    $oExcelDoc.Application.DisplayScrollBars = True
    $oExcelDoc.Application.DisplayStatusBar = False

[water]

That's exactly the code the OP posted in his first post. I tried and it didn't work with the embedded Excel object (Excel 2010 32 bit on Windows 7 64 bit).

NB: I just tried with plain Excel and the embedded ActiveX component - doesn't work for both and returns no COM error.

Edited November 15, 2012 by water

[Affe]

Okay, I've been trying to see if I can get anywhere with the link you gave me, water.

This is what I have so far...

Below gives a COM error when trying to set the height (unspecified error, number 80020009) :

$oExcelDoc.Application.CommandBars("Ribbon").Controls(1).Height = 4

Below gives no error, but doesn't actually do anything. Note: Using a MsgBox prompt, I can actually read this information, as well as the height info above, I just can't change it...

$oExcelDoc.Application.CommandBars("Ribbon").Enabled = False
    $oExcelDoc.Application.CommandBars("Ribbon").Visible = False

Below gives me a COM error, stating that "The parameter is incorrect" (number 80020009 again):

$oExcelDoc.Application.CommandBars.ExecuteMso("MinimizeRibbon")

Below just showing the MsgBoxes that I used to see if I was indeed accessing the correct parameters:

MsgBox(0, "", $oExcelDoc.Application.CommandBars("Ribbon").Controls(1).Height)
    MsgBox(0, "", $oExcelDoc.Application.CommandBars("Ribbon").Visible)

Anyone able to expand on this, maybe show me where I'm going wrong?

[l3ill]

test.xls.xlsx

[Mingre]

Hello, sorry if I'm committing the crime of "necro-ing" a thread here but having received lots of help from this community, I just felt obliged to post this working version (at least in my 32-bit Win XP PC with MS Excel 2010) for the benefit of those who still need this:

expandcollapse popup

#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
;
; Embedding an Excel document inside an AutoIt GUI
;
; Limitations:
;
; 1. Integrating the GUI Menu with the Objects Menu does not work.
;   (they have seperate menu bars)
;

; Initialize my error handler
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

;~ $FileName=@ScriptDir & "\Worksheet.xls"
$FileName = FileOpenDialog("Pick a file", @ScriptDir, "Excel Files (*.xls;*.xlsx)")

If Not FileExists($FileName) Then
    MsgBox(0, "Excel File Test", "Can't run this test, because it requires an Excel file in " & $FileName)
    Exit
EndIf

$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename

If IsObj($oExcelDoc) Then
    GUICreate("Embedded ActiveX Test", 640, 580, (@DesktopWidth - 640) / 2, (@DesktopHeight - 580) / 2, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)

    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 1, 95, 400, 300)

    For $Bar In $oExcelDoc.CommandBars
        If $Bar.Enabled = True Then $Bar.Enabled = False
        If $Bar.Visible = True Then $Bar.Visible = False
    Next
    $oExcelDoc.Application.DisplayFormulaBar = False
    $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
    $oExcelDoc.Application.DisplayScrollBars = True
    $oExcelDoc.Application.DisplayStatusBar = False
    ControlHide("Embedded ActiveX Test", "", "[CLASS:EXCEL2; INSTANCE:1]")
    ControlHide("Embedded ActiveX Test", "", "[CLASS:EXCEL2; INSTANCE:2]")
    GUISetState(@SW_SHOW) ;Show GUI

    ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE ;or $msg = $GUI_FileExit
                ExitLoop
        EndSelect
    WEnd

    GUIDelete()

    ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close(0) ; Close the Excel workbook - Save prompt will not open

EndIf


Exit

; This is my custom error handler
Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "AutoItCOM Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1) ; to check for after this function returns
EndFunc   ;==>MyErrFunc

 

I simply added these lines:

ControlHide("Embedded ActiveX Test", "", "[CLASS:EXCEL2; INSTANCE:1]")
ControlHide("Embedded ActiveX Test", "", "[CLASS:EXCEL2; INSTANCE:2]")

 

Edited July 29, 2016 by Mingre
Specified MS Office version