Excel Window in a GUI

[strate]

Not sure if this is possible, doubt it though. I want to create a excel window (classname=XLDESK1) into a GUI the same way you can create a instance of IE. Attached is the example of IE.

Is this possible?

Internet.au3

[Valuater]

no doubt here

just serach "excel" in help

found these two, so i know it can be done in the same was as IE

Here is an example that 'automates' Microsoft Excel:

 

$oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
$oExcel.Visible = 1                                        ; Let Excel show itself
$oExcel.WorkBooks.Add                                      ; Add a new workbook
$oExcel.ActiveWorkBook.ActiveSheet.Cells(1,1).Value="test" ; Fill a cell
sleep(4000)                                                ;See the results for 4 seconds
$oExcel.ActiveWorkBook.Saved = 1                           ; Simulate a save of the Workbook
$oExcel.Quit                                               ; Quit Excel  


 oÝ÷ Ù©Ýjëh×6

$oExcel = ObjCreate("Excel.Application")
$oExcel.visible =1
$oExcel.workbooks.add

With $oExcel.activesheet
    .cells(2,2).value = 1
    .range("A1:B2").clear
Endwith

$oExcel.quit

8)

Edited August 31, 2006 by Valuater

[strate]

Thanks I know how to do that though. I don't want Excel to open entirely I just want the window. So that it feels like my script has a spreadsheet in it.

EDIT: Just like in the example. When ran you don't get all of the tool bars and all of the other fun micro$oft stuff.

Edited August 31, 2006 by strate

[SvenP]

Thanks I know how to do that though. I don't want Excel to open entirely I just want the window. So that it feels like my script has a spreadsheet in it.

EDIT: Just like in the example. When ran you don't get all of the tool bars and all of the other fun micro$oft stuff.

He's a test script I wrote about a year ago, testing the ActiveX GUI functions:

Before you run it, change line 14 with a filename of an existing excel sheet.

expandcollapse popup

#include <GUIConstants.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"

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
        
    ; Create a simple GUI for our output
    GUICreate ( "Embedded ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN ) 
    ; Create a test File Menu
    $GUI_FileMenu   = GUICtrlCreateMenu     ("&File")
    $GUI_FileNew    = GUICtrlCreateMenuitem ("&New"         ,$GUI_FileMenu)
    $GUI_FileSave   = GUICtrlCreateMenuitem ("&Save"        ,$GUI_FileMenu)
    $GUI_FileSaveAs = GUICtrlCreateMenuitem ("Save As..."   ,$GUI_FileMenu)
    $GUI_FileSepa   = GUICtrlCreateMenuitem (""             ,$GUI_FileMenu)    ; create a separator line
    $GUI_FileExit   = GUICtrlCreateMenuitem ("E&xit"        ,$GUI_FileMenu)
    $GUI_ActiveX    = GUICtrlCreateObj      ( $oExcelDoc,   30, 90 , 400 , 300 )
    
    
    GUISetState ()       ;Show GUI
    
    $oExcelDoc.Windows(1).Activate                  ; I don't think this is necessary.
    
    ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE or $msg = $GUI_FileExit
                ExitLoop
            Case $msg = $GUI_FileSave
                $oExcelDoc.Save         ; Save the workbook
            Case $msg = $GUI_FileSaveAs
                $NewFileName=FileSaveDialog("Save Worksheet as",@scriptdir,"Excel Files (*.xls)")
                if not @error and $NewFileName <> "" Then
                    $oExcelDoc.SaveAs($NewFileName)     ; Save the workbook under a different name
                EndIf
                
        EndSelect
    Wend

    GUIDelete ()

    ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close        ; Close the Excel workbook
    
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

Excel starts in this script with the 'default' toolbars. To change the excel toolbars or other fancy stuff, you might need to read the VBA help in Excel itself. Those are properties of the Excel Application, not AutoIt.

Regards,

-Sven

[randallc]

Hi,

You can see integrated in ptrex's GUI for SQl; [sticky in scrips and scraps)]

This example now shows an integrated EXCEL object, which can be used to export data form the LV to EXCEL

SQLite_Gui.au3

Also in my SQL in my signature (in SQL GUIView scripts)

Best ,Randall

[lukeneo]

Hi,

You can see integrated in ptrex's GUI for SQl; [sticky in scrips and scraps)]

SQLite_Gui.au3

Also in my SQL in my signature (in SQL GUIView scripts)

Best ,Randall

Can somebody show me

How to i save the values / contents of a cell to a text file from the embeded Excel Object ?

[randallc]

Hi,

This shows retrieving the used range to a 2D array; then save the array to the file; or an individual item?

Best, Randall

Case $msg = $GUI_FileRead
                $s_GUIBookName = $oExcelDoc.Application.ActiveWorkBook.fullname
                $Ar_var = $oExcelDoc.Application.activesheet.UsedRange.value
                _ArrayViewText($Ar_var, '$s_GUIBookName='&$s_GUIBookName)

[PS or substtitute "usedrange" for a named or cell-defined range or cell, to get individual cell etc]

Excel_XL_Embed.au3

Edited September 1, 2006 by randallc

[strate]

He's a test script I wrote about a year ago, testing the ActiveX GUI functions:

Before you run it, change line 14 with a filename of an existing excel sheet.

expandcollapse popup

#include <GUIConstants.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"

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
        
    ; Create a simple GUI for our output
    GUICreate ( "Embedded ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN ) 
    ; Create a test File Menu
    $GUI_FileMenu   = GUICtrlCreateMenu     ("&File")
    $GUI_FileNew    = GUICtrlCreateMenuitem ("&New"         ,$GUI_FileMenu)
    $GUI_FileSave   = GUICtrlCreateMenuitem ("&Save"        ,$GUI_FileMenu)
    $GUI_FileSaveAs = GUICtrlCreateMenuitem ("Save As..."   ,$GUI_FileMenu)
    $GUI_FileSepa   = GUICtrlCreateMenuitem (""             ,$GUI_FileMenu)    ; create a separator line
    $GUI_FileExit   = GUICtrlCreateMenuitem ("E&xit"        ,$GUI_FileMenu)
    $GUI_ActiveX    = GUICtrlCreateObj      ( $oExcelDoc,   30, 90 , 400 , 300 )
    
    
    GUISetState ()       ;Show GUI
    
    $oExcelDoc.Windows(1).Activate                  ; I don't think this is necessary.
    
    ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE or $msg = $GUI_FileExit
                ExitLoop
            Case $msg = $GUI_FileSave
                $oExcelDoc.Save         ; Save the workbook
            Case $msg = $GUI_FileSaveAs
                $NewFileName=FileSaveDialog("Save Worksheet as",@scriptdir,"Excel Files (*.xls)")
                if not @error and $NewFileName <> "" Then
                    $oExcelDoc.SaveAs($NewFileName)     ; Save the workbook under a different name
                EndIf
                
        EndSelect
    Wend

    GUIDelete ()

    ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close        ; Close the Excel workbook
    
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

Excel starts in this script with the 'default' toolbars. To change the excel toolbars or other fancy stuff, you might need to read the VBA help in Excel itself. Those are properties of the Excel Application, not AutoIt.

Regards,

-Sven

This is great and almost what I need. I do need to get rid of the tool bars.... hopefully I can figure this out. Thank you.

[strate]

This is great and almost what I need. I do need to get rid of the tool bars.... hopefully I can figure this out. Thank you.

Adding:

With $oExcelDoc
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formula Bar").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
    EndWith

After you create the Obj control will remove all of the needed bars except the formula bar which i don't need either..... I'll update if i find anything...

[strate]

Success!! The following code is from SvenP's first post it with the changes I've found will leave only the grid.

Thank you everyone that helped.

Consider this a closed/resolved topic.

expandcollapse popup

#include <GUIConstants.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 = 'G:\Packaging\Process Sheets\136041.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 ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN ) 
    $GUI_ActiveX    = GUICtrlCreateObj  ( $oExcelDoc, 1, 95 , 400 , 300 )
    
    With $oExcelDoc
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .Application.DisplayFormulaBar = False
    EndWith
    
    GUISetState ()    ;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

[ken82m]

Does anyone have an update on this:

With $oExcelDoc
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .Application.DisplayFormulaBar = False
EndWith

It's not removing the toolbars in excel 2007. In fact the toolbar is partially covering the sheet.

But my objective is to get rid of the tools bars anyway

Thanks,

Kenny

[ken82m]

Well I've been able to figure out that this seems to be done using XML files now.

Does anyone know of a freeware tool to create the XML file.

And more importantly how I can apply the XML using the above?

Thanks,

Kenny

[Donnovan]

Same as ken82m,

Anyone know how to get ride of the Excel 2007 tool bar?

[Affe]

Still having the same issue with Excel 2007 - the menu bar stubbornly stays visible on top of the GUI.

Any suggestions?

[BrewManNH]

1st suggestion, don't resurrect 6 year old threads when you're not adding any information to it. How about opening a new thread, reference this one, post the script that you used to try it and see if that gets you any help?

Please don't necro post...this post predates Windows XP SP3, that's how old it is.

[Affe]

OK, for anyone who ends up on this page with the same issue, I reposted my question

[gicungduoc]

Hi All,

I'm trying this Script for embbeded excel but getting problem with " $oExcelDoc.Save" function, Bug came up from AutoitCOM Test as below:

err.windescription: Call was rejected by callee.

err.number is: 80010001

Please help me to solve this, i'm testing with Excel 2010. Excel file can not save after modifying even $oExcelDoc.Close Function take no effect......

expandcollapse popup

#include <GUIConstants.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"

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
        
    ; Create a simple GUI for our output
    GUICreate ( "Embedded ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN ) 
    ; Create a test File Menu
    $GUI_FileMenu   = GUICtrlCreateMenu     ("&File")
    $GUI_FileNew    = GUICtrlCreateMenuitem ("&New"         ,$GUI_FileMenu)
    $GUI_FileSave   = GUICtrlCreateMenuitem ("&Save"        ,$GUI_FileMenu)
    $GUI_FileSaveAs = GUICtrlCreateMenuitem ("Save As..."   ,$GUI_FileMenu)
    $GUI_FileSepa   = GUICtrlCreateMenuitem (""             ,$GUI_FileMenu)    ; create a separator line
    $GUI_FileExit   = GUICtrlCreateMenuitem ("E&xit"        ,$GUI_FileMenu)
    $GUI_ActiveX    = GUICtrlCreateObj      ( $oExcelDoc,   30, 90 , 400 , 300 )
    
    
    GUISetState ()       ;Show GUI
    
    $oExcelDoc.Windows(1).Activate                  ; I don't think this is necessary.
    
    ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE or $msg = $GUI_FileExit
                ExitLoop
            Case $msg = $GUI_FileSave
                $oExcelDoc.Save         ; Save the workbook
            Case $msg = $GUI_FileSaveAs
                $NewFileName=FileSaveDialog("Save Worksheet as",@scriptdir,"Excel Files (*.xls)")
                if not @error and $NewFileName <> "" Then
                    $oExcelDoc.SaveAs($NewFileName)     ; Save the workbook under a different name
                EndIf
                
        EndSelect
    Wend

    GUIDelete ()

    ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close        ; Close the Excel workbook
    
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

Edited October 25, 2013 by gicungduoc