Jump to content

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


Recommended Posts

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.

Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites
Posted (edited)

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 by Bagel
Link to post
Share on other sites

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 :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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

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

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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) :

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

Link to post
Share on other sites

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)

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

Link to post
Share on other sites

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

Link to post
Share on other sites
Posted (edited)

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 by Bagel
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.  

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...