how to reference existing excel in ComControl?

Started by domvalle@comcast.net, January 18, 2020, 09:03:11 PM

Previous topic - Next topic

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

Quote from: domvalle@comcast.net on January 19, 2020, 06:08:14 PM
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
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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.   
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade