Bert Posted October 3, 2008 Share Posted October 3, 2008 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 F52 second pauseGUI appears with all CommandBars showingCommandBars start hiding one at a timeAll command bars hiddenNow, 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. expandcollapse popup#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 ;-------------------- The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
erik7426 Posted October 3, 2008 Share Posted October 3, 2008 Try this.... expandcollapse popup#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 More sharing options...
Bert Posted October 3, 2008 Author Share Posted October 3, 2008 I'm using 2003. I'm getting a COM error when I run your script. No such interface supported. The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
LarryDalooza Posted October 3, 2008 Share Posted October 3, 2008 Maybe... If .CommandBars("Standard") Then .CommandBars("Standard").Enabled = False granted I was too lazy to read the whole post... sorry. Lar. AutoIt has helped make me wealthy Link to comment Share on other sites More sharing options...
Bert Posted October 4, 2008 Author Share Posted October 4, 2008 Maybe...If .CommandBars("Standard") Then .CommandBars("Standard").Enabled = Falsegranted I was too lazy to read the whole post... sorry.Lar.I already had it in there. Line 32hmmm....this one is puzzling.. The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
ResNullius Posted October 4, 2008 Share Posted October 4, 2008 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 More sharing options...
ResNullius Posted October 4, 2008 Share Posted October 4, 2008 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 More sharing options...
Bert Posted October 6, 2008 Author Share Posted October 6, 2008 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? The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted October 6, 2008 Author Share Posted October 6, 2008 In looking around, I found a mention of ActiveSheet.UsedRange but I'm not sure on how to use it. Any thoughts? The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
ResNullius Posted October 7, 2008 Share Posted October 7, 2008 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 More sharing options...
Bert Posted October 7, 2008 Author Share Posted October 7, 2008 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 The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted October 7, 2008 Author Share Posted October 7, 2008 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 The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted October 7, 2008 Author Share Posted October 7, 2008 I'm sure it is a COM entry in my function that will fix it, but I can't for the life of me to find a complete list of COM for excel. Can anyone point me to it? The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Bert Posted October 27, 2008 Author Share Posted October 27, 2008 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 The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now