Jump to content
SlackerAl

Embed Excel in GUI

Recommended Posts

SlackerAl
Posted (edited)

I have been trying to embed an empty excel sheet into a GUI... Running on Win10

In the following code, un-commenting line 116 leads to AutoIT pausing in the background. Any thoughts? I've tried stripping it to the absolute minimum (i.e. remove error trapping etc. same result).

#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>

Opt('MustDeclareVars', 1)
Opt("GUIOnEventMode", 1)

Local $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Local $FileName = @ScriptDir & "\Book1.xlsx"
Local $oExcelDoc

$oExcelDoc = ObjGet($FileName)
GUICreate("Embed Test", 640, 580, (@DesktopWidth - 640) / 2, (@DesktopHeight - 580) / 2, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit")

; This line causes AutoIT to background and pause/hang
;GUICtrlCreateObj($oExcelDoc, -1, -1)

GUISetState(@SW_SHOW)

While 1
  Sleep(10)
WEnd

Func MenuExit()
  If IsObj($oExcelDoc) Then $oExcelDoc.Close(0)
  GUIDelete()
  Exit
EndFunc

Func MyErrFunc()
  ;from https://www.autoitscript.com/forum/topic/31963-excel-window-in-a-gui/
  $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)
Endfunc

Edit:

Sorry, I should add, you need to create a blank excel file in the script area and call it Book1.xlsx

Edited by SlackerAl
Additional information: Create excel file

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites
SlackerAl

Additional comments / thoughts:

If this can be done without having the empty excel file, that would be great.

When run, you can see that excel starts and runs (process list), but never becomes visible.


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites
SlackerAl

Is anyone able to run this with the critical line uncommented? Thanks.


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites
Andreik

How many rows/columns do you have in your excel? Probably it's just a slow load if you have a lot of data.


When the words fail... music speaks

Share this post


Link to post
Share on other sites
SlackerAl

The file is empty. I'm just seeing what it takes to embed excel into an AutoIt GUI.


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites
water

Use GUICtrlCreateObj to embed an application using an ActiveX control. The example script works for IE.
I once tried MS Word but the result didn't convince me :(


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Something like this (tested with Excel 2017):

#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>

$FileName = @ScriptDir & '\book1.xlsx'
If Not FileExists($FileName) Then
    MsgBox(0, "ERROR", "File not found")
    Exit
EndIf

;Basic GUI
$oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename
If IsObj($oExcelDoc) Then
    $mainGUI = GUICreate("viewer", @DesktopWidth - 50, @DesktopHeight - 150, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 75, @DesktopHeight - 260)
Else
    MsgBox(0, "", "failed")
EndIf
;------------------

;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

GUISetState()

; Loop until the user exits.
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            ExitLoop
    EndSwitch
WEnd

; Delete the previous GUI and all controls.
GUIDelete($mainGUI)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
SlackerAl

Hmmm, same problem for both of those. I have Office 365, which lists the application as Excel 2016. I'm guessing it no longer works as an embedded activex. I can see the excel process start in task manager and after I eventually kill it, if I open excel it shows the test file as not having saved correctly and asks if I would like to recover it - so excel has actually started and opened the file.... but nothing to see on the screen.


Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Share this post


Link to post
Share on other sites
water
Posted (edited)

Seems to be a problem with Office 365 :( 
No Office 365 here, so I can't offer any more assistance regarding this issue :(

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×