Jump to content

Excel and CommandBars


Bert
 Share

Recommended Posts

I have an example script that is doing something weird, and I can't explain it. What I'm doing is to embed a excel spreadsheet into a GUI. It works, but I need to make sure all the CommandBars are hidden. In testing, I first have all the CommandBars showing in Excel. To show them all, open Excel, then you go to on the menu bar to VIEW > TOOLBARS > and make sure all options are checked. When I run my script, it will hide them, but what happens when I run it is this:

  • Press F5
  • 2 second pause
  • GUI appears with all CommandBars showing
  • CommandBars start hiding one at a time
  • All command bars hidden

Now, the weird part is I see the GUI BEFORE GUISetState (@SW_SHOW) is passed. How can I see it when that command hasn't ran yet? I tried again by removing GUISetState (@SW_SHOW). I still see the GUI and the CommandBars disappearing. HUH??? :)

When you run the script, you may get a COM error. This has to do with the CommandBar listed in the script is not on your PC, such as your version of office may be different. Just remark that line out to get past that. Also, you need a simple dummy book1.xls file in the root of C or where ever you have it.

#include <GUIConstants.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 = 'C:\book1.xls'
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 Excel Test", @DesktopWidth - 50, @DesktopHeight - 150, 10, 10, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )
    $GUI_ActiveX = GUICtrlCreateObj( $oExcelDoc, 10, 60 , @DesktopWidth - 70 ,@DesktopHeight - 270 )
;GUISetState (@SW_HiDE)
    With $oExcelDoc
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("3-D Settings").Enabled = False
        .CommandBars("Borders").Enabled = False     
        .CommandBars("Chart").Enabled = False
        .CommandBars("Chart Menu Bar").Enabled = False
        .CommandBars("Circular Reference").Enabled = True
        .CommandBars("Compare Side by Side").Enabled = False
        .CommandBars("Control Toolbox").Enabled = False
        .CommandBars("Diagram").Enabled = False
        .CommandBars("Drawing").Enabled = False     
        .CommandBars("Drawing Canvas").Enabled = False
        .CommandBars("Exit Design Mode").Enabled = False
        .CommandBars("External Data").Enabled = False
        .CommandBars("Forms").Enabled = False       
        .CommandBars("Formula Auditing").Enabled = False    
        .CommandBars("Full Screen").Visible = False     
        .CommandBars("List").Enabled = False            
        .CommandBars("Organization Chart").Enabled = False
        .CommandBars("Picture").Enabled = False 
        .CommandBars("PivotTable").Enabled = False
        .CommandBars("Protection").Enabled = False      
        .CommandBars("Reviewing").Enabled = False
        .Application.CommandBars("Shadow Settings").Visible = False
        .CommandBars("Stop Recording").Enabled = False
        .CommandBars("Text to Speech").Enabled = False
        .CommandBars("Visual Basic").Enabled = False        
        .CommandBars("Watch Window").Enabled = False
        .CommandBars("Web").Enabled = False 
        .CommandBars("WordArt").Enabled = False     
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Acrobat Connect").Visible = False
        .CommandBars("Acrobat PDFMaker 8.0").Visible = False    
        .CommandBars("sforce Connector").Visible = False        
        .Application.DisplayStatusBar = False
        .Application.DisplayFormulaBar = False
        .Application.DisplayScrollBars = True
        .CommandBars("Toolbar List").Enabled = False
    EndWith
;GUISetState (@SW_SHOW)
  ; 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


;--------------------
Link to comment
Share on other sites

Try this....

#include <GUIConstants.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 = 'C:\book1.xls'
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 = ObjCreate("Excel.Application")

if IsObj($oExcelDoc) then
    With $oExcelDoc
        .Visible = 0
        .WorkBooks.Open($FileName)
    EndWith
    GUICreate ( "Embedded Excel Test", @DesktopWidth - 50, @DesktopHeight - 150, 10, 10, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )
    $GUI_ActiveX = GUICtrlCreateObj( $oExcelDoc, 10, 60 , @DesktopWidth - 70 ,@DesktopHeight - 270 )
;GUISetState (@SW_HiDE)
    With $oExcelDoc
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("3-D Settings").Enabled = False
        .CommandBars("Borders").Enabled = False     
        .CommandBars("Chart").Enabled = False
        .CommandBars("Chart Menu Bar").Enabled = False
        .CommandBars("Circular Reference").Enabled = True
        .CommandBars("Compare Side by Side").Enabled = False
        .CommandBars("Control Toolbox").Enabled = False
        .CommandBars("Diagram").Enabled = False
        .CommandBars("Drawing").Enabled = False     
        .CommandBars("Drawing Canvas").Enabled = False
        .CommandBars("Exit Design Mode").Enabled = False
        .CommandBars("External Data").Enabled = False
        .CommandBars("Forms").Enabled = False       
        .CommandBars("Formula Auditing").Enabled = False    
        .CommandBars("Full Screen").Visible = False     
        .CommandBars("List").Enabled = False            
        .CommandBars("Organization Chart").Enabled = False
        .CommandBars("Picture").Enabled = False 
        .CommandBars("PivotTable").Enabled = False
        .CommandBars("Protection").Enabled = False      
        .CommandBars("Reviewing").Enabled = False
        .Application.CommandBars("Shadow Settings").Visible = False
        .CommandBars("Stop Recording").Enabled = False
        .CommandBars("Text to Speech").Enabled = False
        .CommandBars("Visual Basic").Enabled = False        
        .CommandBars("Watch Window").Enabled = False
        .CommandBars("Web").Enabled = False 
        .CommandBars("WordArt").Enabled = False     
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Acrobat Connect").Visible = False
        .CommandBars("Acrobat PDFMaker 8.0").Visible = False    
        .CommandBars("sforce Connector").Visible = False        
        .Application.DisplayStatusBar = False
        .Application.DisplayFormulaBar = False
        .Application.DisplayScrollBars = True
        .CommandBars("Toolbar List").Enabled = False
        .Visible = 1
    EndWith
;GUISetState (@SW_SHOW)
 ; 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

I get an error when it tries to execute GUICtrlCreateObj. What version are you running?

Link to comment
Share on other sites

Take $WS_VISIBLE out of your styles,

GUICreate ( "Embedded Excel Test", @DesktopWidth - 50, @DesktopHeight - 150, 10, 10,$WS_OVERLAPPEDWINDOW+$WS_CLIPCHILDREN)

Don't forget to uncomment your "GUISetState (@SW_SHOW)"

Link to comment
Share on other sites

And I think you can shorten you toolbar hiding/disabling like this:

CODE

#include <GUIConstants.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 = 'C:\book1.xls'

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 Excel Test", @DesktopWidth - 50, @DesktopHeight - 150, 10, 10, $WS_OVERLAPPEDWINDOW + $WS_CLIPCHILDREN)

$GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 60, @DesktopWidth - 70, @DesktopHeight - 270)

For $Bar In $oExcelDoc.CommandBars

If $Bar.Enabled = True Then $Bar.Enabled = False

If $Bar.Visible = True Then $Bar.Visible = False

Next

GUISetState(@SW_SHOW)

; 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

;--------------------

Link to comment
Share on other sites

I think that has got it. I just tested it on my work PC, and the code seems to be working perfectly. Now the question is will it work in field trials. I'm concerned that where the command bars are stopped in the viewer, it will change the setting so that they are disabled when a user opens a excel window. In my testing, that has not happened, but wider field trials will need to be done first.

;-------------------------------------

I do have a second question. I noticed when resizing, the GUICtrlCreateObj will not resize correctly. I tried GUICtrlSetResizing ( $GUI_ActiveX, 1 ), and it does not want to work for this control correctly. I'm losing my scroll bars when I shrink the window. Not sure on how to handle this one. Any suggestions?

Link to comment
Share on other sites

I think that has got it. I just tested it on my work PC, and the code seems to be working perfectly. Now the question is will it work in field trials. I'm concerned that where the command bars are stopped in the viewer, it will change the setting so that they are disabled when a user opens a excel window. In my testing, that has not happened, but wider field trials will need to be done first.

;-------------------------------------

I do have a second question. I noticed when resizing, the GUICtrlCreateObj will not resize correctly. I tried GUICtrlSetResizing ( $GUI_ActiveX, 1 ), and it does not want to work for this control correctly. I'm losing my scroll bars when I shrink the window. Not sure on how to handle this one. Any suggestions?

Resizing is definitely an issue with the embedded spreadsheet control. Not sure how to get around this.

Question: Will you be opening an existing Excel sheet? Or just uing the control for data entry of some type?

In looking around, I found a mention of

ActiveSheet.UsedRange

but I'm not sure on how to use it. Any thoughts?

This command refers to the "used range" of cells in the sheet: that is from the first used cell to the last.

If you add this command to your script

$oExcelDoc.ActiveSheet.UsedRange.Select

It will select the used range.

You can also highlight the used range with a colour:

$oExcelDoc.ActiveSheet.UsedRange.EntireRow.Interior.ColorIndex = 4
Link to comment
Share on other sites

Resizing is definitely an issue with the embedded spreadsheet control. Not sure how to get around this.

Question: Will you be opening an existing Excel sheet? Or just uing the control for data entry of some type?

It will be a existing spreadsheet. It is a form users have to fill out. I'm thinking that users will try to resize it depending on their monitor resolution, and or personal preferences. Knowing that, I wanted to have a case statement that monitored for size changes and would reset the object scroolbars when it occurred.

This command refers to the "used range" of cells in the sheet: that is from the first used cell to the last.

If you add this command to your script

$oExcelDoc.ActiveSheet.UsedRange.Select

It will select the used range.

You can also highlight the used range with a colour:

$oExcelDoc.ActiveSheet.UsedRange.EntireRow.Interior.ColorIndex = 4
I can't change the form. That is the reason I'm using the viewer to handle it. The viewer needs to be able to pass information to and from the spreadsheet to a 3rd party application. If I can get the scroolbar thing nailed down, I should be good.

I tried this, and it didn't work. It would highlight the layout area in blue, but the scrollbars would not reset when the viewer was resized.

Case $msg = $GUI_EVENT_MAXIMIZE Or $msg = $GUI_EVENT_MINIMIZE Or $msg = $GUI_EVENT_RESTORE or $msg = $GUI_EVENT_RESIZED
                RepositionExcel()

func RepositionExcel()
    $oExcelDoc.ActiveSheet.UsedRange.Select
EndFunc
Link to comment
Share on other sites

Resizing is definitely an issue with the embedded spreadsheet control. Not sure how to get around this.

Question: Will you be opening an existing Excel sheet? Or just uing the control for data entry of some type?

It will be a existing spreadsheet. It is a form users have to fill out. I'm thinking that users will try to resize it depending on their monitor resolution, and or personal preferences. Knowing that, I wanted to have a case statement that monitored for size changes and would reset the object scroolbars when it occurred.

This command refers to the "used range" of cells in the sheet: that is from the first used cell to the last.

If you add this command to your script

$oExcelDoc.ActiveSheet.UsedRange.Select

It will select the used range.

You can also highlight the used range with a colour:

$oExcelDoc.ActiveSheet.UsedRange.EntireRow.Interior.ColorIndex = 4
I can't change the form. That is the reason I'm using the viewer to handle it. The viewer needs to be able to pass information to and from the spreadsheet to a 3rd party application. If I can get the scroolbar thing nailed down, I should be good.

I tried this, and it didn't work. It would highlight the layout area in blue, but the scrollbars would not reset when the viewer was resized.

Case $msg = $GUI_EVENT_MAXIMIZE Or $msg = $GUI_EVENT_MINIMIZE Or $msg = $GUI_EVENT_RESTORE or $msg = $GUI_EVENT_RESIZED
                RepositionExcel()

func RepositionExcel()
    $oExcelDoc.ActiveSheet.UsedRange.Select
EndFunc
Link to comment
Share on other sites

  • 3 weeks later...

I decided to solve the problem by making the GUI a static size. (In other words, it can't be resized)

I had a couple of other issues with commandbars and I solved it with the following code:

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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...