Handle to existing Excel document

[lcluff2000]

Is there a way to get a handle to an open Excel document so I can use the standard ExcelSheet functions?

For example, a macro may open an excel spreadsheet with a button. I then need a handle so I can define ExcelSheet es1=???

I've tried to use ExcelSheet.Init with no luck. The only way I can get this to work so far is to create commands to do a file..save, and then open the file with ExcelSheet.Init

Is there a way to do this without saving the already open Excel file?

Thank you!

[Gintaras]

Try to add " /exe" at the beginning.

Macro Macro1561

Code:

Copy      Help

;/exe
ExcelSheet es1.Init

[lcluff2000]

I'm trying to get a handle to an already open Excel document by name or by it being the active document (there may be more than one Excel document already open).

With this code:

Code:

Copy      Help

;/exe
ExcelSheet es1.Init
es1.SetCell("OK" 1 22)

I get the following error:
Error (RT) in Macro: invalid interface pointer. Use _create or other function to create or get object

[Gintaras]

sorry, must be "/exe 1"

Macro Macro1565

Code:

Copy      Help

;/exe 1
ExcelSheet es1.Init
es1.SetCell("OK" 1 22)

But in your case, the reason probably is other.
In which line is error? es1.Init or es1.SetCell?
This error is when no workbook is open in Excel (dark gray background with no sheets).
What is your Quick Macros version? What Office version?

[lcluff2000]

Error occurs on es1.Init
There are currently 2 Excel documents open: "Book1" & "DMS Report"
I'm trying to get the handle to "Book1", and it is visible
QM version 2.3.2.8
Excel 2007

[Gintaras]

Try this. In which line error?

Macro Macro1571

Code:

Copy      Help

;/exe 1

ExcelSheet es1

;this code is simplified Init version
Excel.Application xlApp._getactive
Excel.Workbook wb=xlApp.ActiveWorkbook
es1=wb.ActiveSheet


es1.SetCell("OK" 1 22)

[lcluff2000]

Code:

Copy      Help

es1=wb.ActiveSheet

[Gintaras]

I don't know why fails to get active workbook. Try this:
Macro Macro1571

Code:

Copy      Help

;/exe 1

ExcelSheet es1

;this code is simplified Init version
Excel.Application xlApp._getactive
es1=xlApp.ActiveSheet
out es1 ;;is it 0?

es1.SetCell("OK" 1 22)

[Gintaras]

From ActiveWorkbook help:

Returns 0 if there are no windows open or if either the Info window or the Clipboard window is the active window.

[lcluff2000]

Yes. It is 0.
I even threw in an act("Book1") at the beginning.
Excel is open, and the clipboard window is not active. See attached screenshot.
Any other ideas?

[Gintaras]

Try this
Macro Macro1571

Code:

Copy      Help

;/exe 1

ExcelSheet es1

;this code is simplified Init version
IDispatch xlApp._getactive("Excel.Application")
es1=xlApp.ActiveSheet


es1.SetCell("OK" 1 22)

And try this. It uses VBScript and runs in wscript as .vbs file, not in QM. Should be True. If fails, will be False.
Macro Macro1572

Code:

Copy      Help

str vb=
;Set xlApp=GetObject(,"Excel.Application")
;Set es1=xlApp.ActiveSheet
;MsgBox not Nothing is es1
VbsExec2 vb

[lcluff2000]

The first example fails on the SetCell with the same error.
The second example returns False.

[Gintaras]

I don't have more ideas. Google also did not help. Try to use book name.

Macro Macro1575

Code:

Copy      Help

;/exe 1

ExcelSheet es1

;this code is simplified Init version
Excel.Application xlApp._getactive
Excel.Workbook wb=xlApp.Workbooks.Item("Book1")
es1=wb.ActiveSheet


es1.SetCell("OK" 1 22)

[lcluff2000]

Thanks for trying Gintaras.

To work around this, I created a function that works around this issue:

Code:

Copy      Help

/
function str'name ExcelSheet&es1

int w1 w2
str s
double t

;;wait for up to 30 sec to activate
w1=wait(30 WC name)
act(w1)

;;del the temp file
s.expandpath("$temp$\temp33333.xlsx")
del- s
err

;;save as to temp file
Acc a2=acc("Save As" "SPLITBUTTON" w1 "NetUIHWND" "" 0x1001)
a2.Mouse(1)
wait(5 WC win("Save As"))
s.setwintext(id(54 win("Save As" "bosa_sdm_XL9")))
Acc a3=acc("Save" "PUSHBUTTON" win("Save As" "bosa_sdm_XL9") "bosa_sdm_XL9" "" 0x1001)
a3.DoDefaultAction

;;close
act(w1)
key Af c

;;open ExcelSheet
out s
es1.Init("Sheet1" 4 s)

You can call it with this code:

Code:

Copy      Help

ExcelSheet es1
str s
GetOpenExcel("Book1" es1)

It may only work with Excel 2007. I'm sure it could be optimized.