Sign in to follow this  
Followers 0
strate

Excel Window in a GUI

17 posts in this topic

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


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by Valuater

NEWHeader1.png

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by strate

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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.

#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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by randallc

Share this post


Link to post
Share on other sites

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.

#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.

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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...


INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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.

#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

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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


My Contributions _StringMultiReplace PC Builders Console - Secure PDF Creator - Cisco VPN Installer MS DNS Server Backup Script - MS DHCP Backup Script IT Admin Console - Toggle Admin Mode - MyMovies-Add Discs Script - IT Help Desk and System Information Tool - Set On Lid Close Power Option - Streaming Media Server & Website "I believe that when we leave a place, part of it goes with us and part of us remains... Go anywhere, when it is quiet, and just listen.. After a while, you will hear the echoes of all our conversations, every thought and word we've exchanged.... Long after we are gone our voices will linger in these walls for as long as this place remains."

Share this post


Link to post
Share on other sites

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


My Contributions _StringMultiReplace PC Builders Console - Secure PDF Creator - Cisco VPN Installer MS DNS Server Backup Script - MS DHCP Backup Script IT Admin Console - Toggle Admin Mode - MyMovies-Add Discs Script - IT Help Desk and System Information Tool - Set On Lid Close Power Option - Streaming Media Server & Website "I believe that when we leave a place, part of it goes with us and part of us remains... Go anywhere, when it is quiet, and just listen.. After a while, you will hear the echoes of all our conversations, every thought and word we've exchanged.... Long after we are gone our voices will linger in these walls for as long as this place remains."

Share this post


Link to post
Share on other sites

Same as ken82m,

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

Share this post


Link to post
Share on other sites

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

Any suggestions?


[center][/center]

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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


[center][/center]

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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......

#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 by gicungduoc

[topic='27925']WebcamExample[/topic] || [topic='68866']WebcamUDF[/topic] || [topic='138222']DSShowCaptureFilter[/topic] || [topic='20121']ScreenResolutionChange[/topic] || [topic='139426']WebcamDS[/topic] || [topic='156613']ObfuscatorUTF8[/topic] || [topic='156219']VideoColorSystemPAL/NTSC[/topic] || [topic='31963']ExcelWindowsInGUI[/topic] || [topic='110473']4Webcam[/topic] || [topic='83763']RichEdit[/topic] || [topic='83763']RichEditUDF[/topic] || [topic='142384']RichEditLabel[/topic] ||[topic='96986']GUITFLabel[/topic] || [topic='32144']AccessCom[/topic] || [topic='38353']BetaPad[/topic] || [topic='31963']EmbeddedExcel[/topic]

Share this post


Link to post
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
Sign in to follow this  
Followers 0