how to reference existing excel in ComControl?

[domvalle@comcast.net]

We can load an excel sheet in a comcontrol just fine.
MyDialog002=`001,001,486,318,COMCONTROL,"ComControl_1",DEFAULT,"excel file",DEFAULT,30,DEFAULT,DEFAULT,DEFAULT,DEFAULT`

But to get to control it we have to use the objXL = ObjectGet(,"Excel.Application") to get the reference
if there are other excel docs open it can be confusing...

could you use DialogControlGet in some way to return this specific doc ?

[JTaylor]

Can't say this will let you accomplish what you want as I don't know what you need to do but this is how I do it for a browser control embedded in a dialog.

    obrowser = DialogObject(DnD_Handle,"co_DnD_obrowser",@doGetObject)



Jim

[stanl]

Not really sure from your post, but are you saying multiple instances of Excel opened, or one instance with multiple workbooks/worksheets. Never used Excel in Comctrl only OWS. But I think using Jim's suggestion of DialogObject() you could run events like activeworksheet/activeworkbook - then refresh.

[domvalle@comcast.net]

Yes, things like save, saveAS and export to pdf are what we usually do.
I tried objXL = DialogObject(MyDialog_Handle,"ComControl_1",@doGetObject) and it returns something but not Excel.
objActiveWorkbook = objXL.ActiveWorkbook and everything else will fail.
So DialogObject does not seem to get a handle to the Excel document.
objXL = ObjectGet(,"Excel.Application") does return the correct handle though...
hmmm...may have to stick with that...

[stanl]

maybe try  objXL = DialogObject(MyDialog_Handle,"ComControl_1",1, @doGetObject) as the Will Help:

In order to have your dialog procedure called by the control, you need to call DialogObject with a request code of one (1) and specify the name of the event in the fourth parameter

EDIT: no, that won't work

[td]

Yes, things like save, saveAS and export to pdf are what we usually do.
I tried objXL = DialogObject(MyDialog_Handle,"ComControl_1",@doGetObject) and it returns something but not Excel.
objActiveWorkbook = objXL.ActiveWorkbook and everything else will fail.
So DialogObject does not seem to get a handle to the Excel document.
objXL = ObjectGet(,"Excel.Application") does return the correct handle though...
hmmm...may have to stick with that...

You need to understand a bit about the Excel programming model to embed Excel in a WIL Dialog.  The object reference (the return value is a reference to an object and not a handle) returned by DialogObject is a Worksheet object and not the Excel application object reference.   It returns a Worksheet object reference because that is how Microsoft programmed Excel to work when it is being hosted in another process.  To obtain a reference to an Excel application object, you need to call the "Application" property of the WorkSheet object.

Code (winbatch) Select Expand

objWorksheet = DialogObject(MyDialog_Handle,"ComControl_1",@doGetObject)
objExcel = objWorkSheet.Application  ; May have restrictions when used in a hosting environment.


You can use the WIL Type Viewer or go to MSFT's on-line documentation to explore the Excel programming mode.  Be forewarned that it is very complex and can be very exasperating.

The application object is described here:
https://docs.microsoft.com/en-us/office/vba/api/excel.application(object)

While hosting Excel in a dialog is doable, it requires a lot of patience and effort to learn the ins and outs.  Depending on your end goal you might want to consider alternatives.

[domvalle@comcast.net]

OK, so here is a more accurate account of this scenario...
We use Excel to complete Office Forms...
When the form is opened in a dialog via comcontrol:
MyDialog005=`003,021,370,270,COMCONTROL,"ComControl_1",DEFAULT,"Excel Doc",DEFAULT,50,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
it seems to operate fine.
We have a SaveXL gosub that gets the obj from objXL = ObjectGet(,"Excel.Application").
if the user does NOT press ENTER or move to the next cell after entering data the .save() crashes, otherwise the .save() works fine
...Interesting
So I thought we were not pointing to the correct Excel doc/instance?
I read that Excel will create a temporary file to hold these interim changes so I'm thinking that this temporary file is the issue...
For now we prompt the use to "Be sure to press ENTER before saving this form..."

[td]

<rant> You are misusing the term "crash". </rant> 

Excel's application object's "Save" method is known to have issues.  It has been complained about by VBA programmers for multiple years.  Since WinBatch is using the same COM Automation object WinBatch users encounter the same problems.   

[domvalle@comcast.net]

OK, thanks for confirming the issue, we will just work around it...

[td]

Some have more success using the worksheet object's "SaveAs" method but your mileage may vary.