Write data to Excel cells to an Excel sheet that's embedded in a GUI?

[robertocm]

On 3/31/2021 at 12:55 PM, Bagel said:

I've created a GUI that has an embedded Excel workbook

Thanks, i was doing some testing, just for learning from your code.

Here an example for running macros in the excel workbook from a child gui "panel". The attached excel file hides the ribbon on open, because i wasn't able to use ExecuteExcel4Macro from AutoIt.

expandcollapse popup

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

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)

;Help: GUI Reference; Advanced GUIGetMsg and Multiple Windows
;When called with the 1 parameter instead of returning an event value an array will be returned,
;array contains the event (in $aArray[0]) and extra information such as the window handle (in $aArray[1])
Global $aMsg = 0

Global $sFilePath = @ScriptDir & '\TestBook.xlsm'
If Not FileExists($sFilePath) Then
    MsgBox(0, "ERROR", "File not found")
    Exit
EndIf

#===== EXCEL =====
Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

;Basic GUI
Global $oExcelDoc = ObjGet($sFilePath) ; Get an excel Object from an existing filename
If IsObj($oExcelDoc) Then
   Global $hMainGUI = GUICreate("viewer", 1000, 750, -1, -1, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
   ;$hMainGUI = GUICreate("viewer", 820, 303, 0, 196, $WS_MINIMIZEBOX + $WS_CLIPCHILDREN)
   Global $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 0, 0, 992, 688)
Else
   MsgBox(0, "", "The Excel workbook to display in main GUI could not be found.")
   Exit
EndIf

Global $hChild = GUICreate("Child", 990, 42, 1, 1, $WS_POPUP, $WS_EX_MDICHILD, $hMainGUI)
;Global $hChild = GUICreate("Child", 986, 42, 1, 1, BitOR($WS_SIZEBOX,$WS_THICKFRAME,$WS_SYSMENU,$WS_POPUP), $WS_EX_MDICHILD, $hMainGUI)
GUISetBkColor(0xFFFFFF)

Global $iLbPrint = GUICtrlCreateLabel("Print", 4, 4, 200, 36, $SS_CENTER + $SS_CENTERIMAGE)
GUICtrlSetBkColor(-1, 0xD8D8D8)
GUICtrlSetColor(-1, 0x439467)
GUICtrlSetFont(-1, 12, Default, Default, "Segoe UI Light")
GUICtrlSetCursor(-1, 0)

GUISetState(@SW_SHOW, $hChild)
GUISetState(@SW_SHOW, $hMainGUI)

While 1
  ;Assign to $aMsg the advanced GUI messages.
  $aMsg = GUIGetMsg(1)

   ;Switch from GUIs
   Switch $aMsg[1]
      Case $hMainGUI
      ;The event comes from the $hMainGUI
         ;Switch from event ID
         Switch $aMsg[0]
            Case $GUI_EVENT_CLOSE
               $oExcelDoc.Close
               ;Sleep(1000)
               $oExcelDoc.Application.Quit
               GUIDelete($hMainGUI)
               GUIDelete($hChild)
               ExitLoop
            ;Case $cDummy
               ;Consolewrite("aaa" & @CRLF)
         EndSwitch

     Case $hChild
         Switch $aMsg[0] ;Event
            Case $GUI_EVENT_CLOSE
               GUISetState(@SW_HIDE, $hChild)
            Case $iLbPrint
               ;$oExcelDoc.Application.Run("PrintSheet") ;ok
               $oExcelDoc.Application.Run("Module1.PrintSheet")
               ;$oExcelDoc.Application.Run("TestBook.xlsm!Module1.PrintSheet") ;not working
         EndSwitch
   EndSwitch
WEnd

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

;This is a custom error handler
Func ErrFunc()
   Local $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

 

TestBook.zip

[water]

Which macro did you try to run with ExecuteExcel4Macro?
Did you get any error message?

[water]

Depending on the version of Excel you run you should use

; $oExcelDoc.Application.ExecuteExcel4Macro("show.toolbar(""Ribbon"",True)") ; Does not work with Excel 2016
$oExcelDoc.Application.CommandBars.ExecuteMSO("HideRibbon") ; Works with Excel 2016

to hide the ribbon as described here: https://stackoverflow.com/questions/39201757/vba-auto-hide-ribbon-in-excel-2013

[robertocm]

4 hours ago, water said:

Which macro did you try to run with ExecuteExcel4Macro?

Thanks!

Excel 2007 Spanish, and below the tests: err.number 80020009

I also tried with ";" as separator: no errors returned but not working

expandcollapse popup

Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

$oExcel = ObjCreate("Excel.Application") ; Create an Excel Object

With $oExcel
   .Visible = 1 ; Let Excel show itself
   ;.Windows(1).Visible = 1; Set the first worksheet in the workbook visible
   ;.Application.Visible = 1; Set the application visible (without this Excel will exit)
   .WorkBooks.Add ; Add a new workbook
   .ActiveWorkBook.ActiveSheet.Cells(1, 1).Value = "Text" ; Fill a cell

   ;error 80020009
   .Application.ExecuteExcel4Macro("CALL(""user32"",""SetCursorPos"",""JJJ"",100,100)")
   .Application.ExecuteExcel4Macro('CALL("user32","SetCursorPos","JJJ",100,100)')
   $iEventError = 0 ; Reset after displaying a COM Error occurred
   ;www.autoitscript.com/forum/topic/129244-excel-macros-wont-show
   .Application.ExecuteExcel4Macro('SHOW.TOOLBAR("Ribbon",False)')
   .Application.ExecuteExcel4Macro("SHOW.TOOLBAR(""Ribbon"",False)")
   .ExecuteExcel4Macro('SHOW.TOOLBAR("Ribbon",False)')
   .ExecuteExcel4Macro("SHOW.TOOLBAR(""Ribbon"",False)")
   $iEventError = 0 ; Reset after displaying a COM Error occurred

   ;water, May 16, 2021
   ;https://www.autoitscript.com/forum/topic/205528-write-data-to-excel-cells-to-an-excel-sheet-thats-embedded-in-a-gui/?do=findComment&comment=1481952
   $oExcel.Application.ExecuteExcel4Macro("show.toolbar(""Ribbon"",True)") ; Does not work with Excel 2016
   ;.Application.CommandBars.ExecuteMSO("HideRibbon") ; Works with Excel 2016
   ;.Application.CommandBars.ExecuteMso("MinimizeRibbon")
   ;If $iEventError Then
      ;Consolewrite("Error ExecuteMso: Only in Excel 2010-2016 you can use this to hide the view of only the Ribbon" & @CRLF)
      ;$iEventError = 0 ; Reset after displaying a COM Error occurred
   ;EndIf

   Global $s = .ExecuteExcel4Macro("MID(""texto"",1,4)")
   Consolewrite($s & @CRLF)
   $iEventError = 0

   ;Not error 80020009 returned but not working
   .ExecuteExcel4Macro(Chr(34) & 'SHOW.TOOLBAR(' & '""Ribbon""' & ',False)' & Chr(34))
   .ActiveWorkBook.Application.ExecuteExcel4Macro('"' & 'SHOW.TOOLBAR(' & '""Ribbon""' & ',False)' & '"')
   .ActiveWorkBook.Application.ExecuteExcel4Macro(Chr(34) & 'SHOW.TOOLBAR(' & Chr(34) & Chr(34) & 'Ribbon' & Chr(34) & Chr(34) & ', False)' & Chr(34))
   Global $MyStr = Chr(34) & Chr(34) & 'My Test Header' & Chr(34) & Chr(34)
   .ExecuteExcel4Macro(Chr(34) & 'PAGE.SETUP(' & $MyStr & ')' & Chr(34))
   .ActiveWorkBook.Application.ExecuteExcel4Macro("PAGE.SETUP(""My Test Header"")")
   .ActiveWorkBook.Application.ExecuteExcel4Macro(Chr(34) & 'PAGE.SETUP(""My Test Header"")' & Chr(34))
   If $iEventError Then
      Consolewrite("Error ExecuteExcel4Macro" & @CRLF)
      $iEventError = 0 ; Reset after displaying a COM Error occurred
   EndIf

   Sleep(5000) ; Display the results for 4 seconds
   .ActiveWorkBook.Saved = 1 ; Simulate a save of the Workbook
   .Quit ; Quit Excel
EndWith

;This is a custom error handler
Func ErrFunc()
   Local $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

 

[water]

80020009 stands for a "general exception".
Can you please post the WinDescription the COM error handler displays?

[robertocm]

All results from test script:

expandcollapse popup

->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    14
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    15
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    18
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    19
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    20
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    21
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    26
->    We intercepted a COM Error !
->    err.number is:    80020009
->    err.source:   Microsoft Office Excel
->    err.windescription:   Ocurrió una excepción.
->    err.scriptline is:    34

Edited May 17, 2021 by robertocm

[water]

With Excel 2016 I get a lot of additional information from Windescription.
With Excel 2007 it is impossible to debug. I have run out of ideas
Is moving to Excel 2016/2019 an option for you?

[robertocm]

2 hours ago, water said:

Is moving to Excel 2016/2019 an option for you?

Dear water,

Many Thanks for your help, at least i see that testing code for ExecuteExcel4Macro is mainly ok.

Was interested in embedding workbooks as an alternative to ListViews to display data, with more flexible format styles.
But seems that other excel files remain locked while using the ActiveX control in the GUI.

I prefer to remain with "legacy" Excel 2007:
1. Not needing very advance features: accounting, taxes, etc.
2. Compatibility: Windows 7 at work and XP at home
3. Cost-benefit for a new Office version

Thanks!

[water]

[robertocm]

5 hours ago, water said:

I have run out of ideas

seems related to AutoIt because this similar vbs code works ok with same Excel 2007 + XP

'On Error Resume Next
Dim oExcel
Dim oWorkBook
Dim s
Set oExcel = CreateObject("Excel.Application") 'Create Excel
With oExcel
	.DisplayAlerts = False
	.Visible = 1 'Let Excel show itself
	'.Windows(1).Visible = 1 'Set the first worksheet in the workbook visible
	'.Application.Visible = 1 'Set the application visible (without this Excel will exit)
	Set oWorkBook = .WorkBooks.Add() 'Add a new workbook
	oWorkBook.ActiveSheet.Cells(1, 1).Value = "Hello" 'Fill a cell

	WScript.Sleep 2000

   '.Application.ExecuteExcel4Macro("SHOW.TOOLBAR(""Ribbon"",False)") 'ok Excel 2007
   '.ExecuteExcel4Macro("SHOW.TOOLBAR(""Ribbon"",False)") 'ok Excel 2007
 
	'water, May 16, 2021
	'https://www.autoitscript.com/forum/topic/205528-write-data-to-excel-cells-to-an-excel-sheet-thats-embedded-in-a-gui/?do=findComment&comment=1481952
	'VBA auto hide ribbon in Excel 2013
	'https://stackoverflow.com/questions/39201757/vba-auto-hide-ribbon-in-excel-2013
	.Application.ExecuteExcel4Macro("show.toolbar(""Ribbon"",False)") 'ok Excel 2007. Does not work with Excel 2016
	'.Application.CommandBars.ExecuteMSO("HideRibbon") 'Works with Excel 2016
	'.Application.CommandBars.ExecuteMso("MinimizeRibbon")

	WScript.Sleep 2000

	s = oWorkBook.Application.ExecuteExcel4Macro("MID(""texto"",1,4)")
	If Not IsEmpty(s) Then
		oWorkBook.ActiveSheet.Cells(2, 1).Value = s
	End If

	WScript.Sleep 5000
	oWorkBook.Saved = 1 'Simulate a save of the Workbook
	oWorkBook.Close False
	.Quit
End With
'Wscript.Echo "DONE"
'Wscript.Quit

 

[water]

Which version of AutoIt do you run?

[robertocm]

version 3.3.14.5

Also tried changing file encoding without success

Thanks!

[water]

Don't know what to check next

[robertocm]

Many Thanks!

Testing on Windows 7, Excel 2007: same errors, vbs script works ok

Also tried installing an old AutoIt version (3.3.12.0) same errors.