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

[Bagel]

Is there any way to write data to Excel cells in an already open workbook? I've created a GUI that has an embedded Excel workbook and I need to be able to write to cells in that workbook using another, external, script. I thought I would be able to work it out if I could just make a script write to cells in any Excel workbook that was already open but I can't find a way to do this. I've searched around quite a bit but haven't been able to find anything on this.

Thanks for any help or if anyone can point me in the right direction.

[water]

It depends on how you have embedded the Excel workbook in your GUI (never done this myself). If you have the workbook object you could simply use _Excel_RangeWrite.
Else you could try _Excel_BookAttach.

[Bagel]

But what I need to do is write to the cells from a separate script that isn't the one that has the workbook embedded in it. As a proof of concept I thought I would just try opening an Excel workbook and then creating a script that could access/write to cells in that workbook but I'm not even sure how to proceed with that. The GUI I have has the Excel spreadsheet as an object and then I would need to access that object from the other external script. I don't think there's any way to access the cells as external "controls" and send them text or data. But I'm not sure.

Edited March 31, 2021 by Bagel

[water]

That's where the Excel UDF (comes with AutoIt) comes into play. You do not access controls but objects.
Please post a small test script of what you do (at least the part where you create the GUI and embed the Excel workbook).
Please make sure the test script is runnable

[Danp2]

Not sure that this would work in your scenario, but maybe you could directly update the Excel file from script #2 and have script #1 update the GUI with the latest data.

[Bagel]

Below is test script that I'm working with taken and modified slightly from another script I found online. I'm sure I can access the cells using the Excel UDF functions from within this script but not sure how that can be done with an external script. Wouldn't I need to open the Excel work book in the external script? But it's already open in THIS script...

expandcollapse popup

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

$FileName = @ScriptDir & '\TestBook.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("", 826, 303, 0, 549, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
    ;$mainGUI = GUICreate("viewer", 820, 303, 0, 196, $WS_MINIMIZEBOX + $WS_CLIPCHILDREN)
    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 0,0, 826, 300)
Else
    MsgBox(0, "", "The Excel workbook to display in main GUI could not be found.")
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.Activesheet.Range("A2").Select
$oExcelDoc.Application.ActiveWindow.FreezePanes = True
$oExcelDoc.Application.ActiveWindow.DisplayHeadings = False
$oExcelDoc.Application.DisplayFormulaBar = False
$oExcelDoc.Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled=False
$oExcelDoc.Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Visible=False
;$oExcelDoc.Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",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
            $oExcelDoc.Close
            ExitLoop
    EndSwitch
WEnd

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

 

[Bagel]

One solution I can think of is to create an input control in the main GUI then have the external script write the data in a string to that control. Then from the GUI (the one that opened the workbook) parse the string and plug the data elements into separate cells. But that's such a messy and indirect way of doing it and I think that introduces a lot of overhead that I'd like to avoid.

[Nine]

You could use one of the multiple IPCs available.  I made an example using my WCD-IPC.  As you can see it is very easy to create :

Script 1 (Server) :

expandcollapse popup

#include <Constants.au3>
#include <GUIConstants.au3>
#include "WCD_IPC.au3"

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

;Basic GUI
$oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename
If Not IsObj($oExcelDoc) Then Exit MsgBox($MB_SYSTEMMODAL, "", "The Excel workbook to display in main GUI could not be found.")
Local $hGUI = GUICreate("", 826, 303, -1, -1, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
GUICtrlCreateObj($oExcelDoc, 0, 0, 826, 300)

;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.Activesheet.Range("A2").Select
$oExcelDoc.Application.ActiveWindow.FreezePanes = True
$oExcelDoc.Application.ActiveWindow.DisplayHeadings = False
$oExcelDoc.Application.DisplayFormulaBar = False
$oExcelDoc.Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Enabled = False
$oExcelDoc.Application.CommandBars("Worksheet Menu Bar").Controls("Insert").Visible = False
$oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
$oExcelDoc.Application.DisplayScrollBars = True
$oExcelDoc.Application.DisplayStatusBar = False

GUISetState()

$oSheet = $oExcelDoc.Application.Activesheet

Local $hServer = _WCD_CreateServer ()
If @error Then Exit MsgBox ($MB_SYSTEMMODAL, "Error", "Unable to create WCD server")

Local $aReq, $iData, $aPack
While True
  If _WCD_Server_IsRequestAvail() Then
    $aReq = _WCD_Server_GetRequest()
    $iData = @extended
    $aPack = StringSplit($aReq[1], "|", $STR_NOCOUNT)
    Switch $iData
      Case 1  ; set numeric
        $oSheet.Range($aPack[0]).value = Number($aPack[1])
      Case 2  ; set string
        $oSheet.Range($aPack[0]).value = String($aPack[1])
    EndSwitch
  EndIf
  Switch GUIGetMsg()
    Case $GUI_EVENT_CLOSE
      $oExcelDoc.Close
      ExitLoop
  EndSwitch
WEnd

Script 2 (Client) :

#include <Constants.au3>
#include <GUIConstants.au3>
#include "WCD_IPC.au3"

Opt ("MustDeclareVars", 1)

Global $hWnd = _WCD_CreateClient ("Test WCD Client")
Global $hWndServer = _WCD_GetServerHandle ()
ConsoleWrite ("Server = " & $hWndServer & "  Client = " & $hWnd & @CRLF)

_WCD_Send($hWnd, $hWndServer, 1, "C5|123.4") ; set numeric data
Sleep (500)
_WCD_Send($hWnd, $hWndServer, 2, "C6|Once upon") ; set string
Sleep (500)

See my signature for Fast and Simple WCD-IPC.

[Bagel]

Interesting. I've never even heard of WCD-IPC. I'll have to look into it.

[water]

It's possible with pure AutoIt as well.
This little script grabs the first workbook and writes "xxx" to cell "A7".

#include <Excel.au3>

Global $oExcel = _Excel_Open()
MsgBox(0, "BookOpen", @error)
$aBooks = _Excel_BookList()
MsgBox(0, "BookList", @error)
_ArrayDisplay($aBooks)
Global $oWorkbook = $aBooks[0][0] ; _Excel_BookAttach(@ScriptDir & "\Test.xlsx")
MsgBox(0, "oWorkbook", isobj($OWorkbook))
_Excel_RangeWrite($oWorkbook, 1, "xxx", "A7")
MsgBox(0, "RangeWrite", @error)

 

[Bagel]

Nine, internally how are the messages handled when multiple messages are received? If multiple messages are sent via for example Case 1 will they be queued or will only the most recently sent message be received? For my purposes having each script send a message through a separate case is a little more complicated and requires a bit more coding.

[Nine]

No problem.  It will be stacked.  Try it.  Limit is 500 messages at single moment.  Almost impossible to achieve.

[Bagel]

Nine, I am using _WCD_Server_IsRequestAvail() as a Case in a Switch. Offhand, do you know why

Case _WCD_Server_IsRequestAvail()
        ~code

would crash the script silently and

Case _WCD_Server_IsRequestAvail()
            If _WCD_Server_IsRequestAvail() Then
                ~code
            EndIf

would NOT cause a crash? In both situations there isn't even a client running let alone a request being sent. My intention is of course to execute the Case code only when a client sends data but I'm concerned that something else is going on and don't want to add any overhead to the script if possible.

Thanks

[Nine]

Your Case statement seems highly suspicious.  It should be a constant, not a variable. The problem is absolutely not related to the UDF.  Post a full runable script as I am sure we can solve your issue in a few secs.

[Bagel]

I just had some ideas on how to test a bit further and I think you're right. There is something wrong with how I'm using the Switch/Case statement but I can't tell what it is. When I use _WCD_Server_IsRequestAvail() in ConsoleWrite it produces False as expected since I don't have a client running that has sent any requests. So none of the code inside the Case should be executing. I need to investigate a bit more.

[Bagel]

Okay, I figured it out. The Switch is using "$nMsg" as the expressions since it's in a GUI and most of the time it's returning "0" and when it does, it matches what _WCD_Server_IsRequestAvail() is returning since there aren't any requests available and hence executes the code inside the case. Now I just need to figure out how to rework the code around this.

Incidentally, I've been wondering, is there a shelf life to the requests that are queued up and haven't been received? Will they die after a time or reside on the server indefinitely?

[Nine]

They are stacked forever until they are treated.  I think my example is truly very close to what you should do.

[Bagel]

Yes, I've implemented largely your example. I just kept the code dealing with the requests in an IF statement and then moved the IF statement below the Switch I'm using. I'll get to employ and test all the new code in a live environment tomorrow. The tool seems particularly well suited for my use case. I have, at times, multiple scripts running which need to communicate with a GUI I have created to manage them. What I've done in many cases to transfer data between them, when necessary, is create a control on the GUI and have one of the other scripts store information in the control so that the GUI can access it. But that is an ugly kludge I've had to resort to. I really had no idea it was possible to transfer data between processes like this. I wish it were possible to use with all the 3rd party applications I have to interface with.

One thing I'm curious about, what would your opinion be of the performance of transferring information via your WCD-IPC vs. using something like ControlSetText and then having the GUI read from that control? Or having to use ControlSetText to populate a group of controls with information and having WCD-IPC send all the data via a single send and then having the GUI populate the controls? I've never tested performance of something so complicated. Do you have any advice on how to set up such a test?

Edited April 5, 2021 by Bagel

[Bagel]

Also, many of the "child" scripts often have to receive information from the GUI. Like when particular buttons and controls are used. What I've done to achieve this is create Do loops that continuously check the status of those controls to detect a user change using ControlGetText.

[Nine]

I think it is time for you to show the code that is problematic.  Asking theoretical questions is fine, but as always it is how it is implemented.  And since I have no idea what your child process looks like (nor you server side either), I will not be able to advice you correctly without seeing those.