how to "attach" to the currently active Excel Worksheet?

[rudi]

Hi.

I want to do some special calculations depending on a selection I made in an already opened Excel Workbook/Worksheet.

All the examples I find seem to open a new Excel instance with a given XLS file, later on these write to the sheet. The snippets found start like this:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("Drive_Path_to_Your_Excel_WorkBook.XLS")
; ....

1.) How to tell autoit "Connect to the currently open Excel Worksheet" ?

2.) And how to read the selection to an array? (continous selection, 1-15 rows, always three collumns)

Thanks for any suggestions, Rudi.

[amokoura]

There was some kind of "ActiveWorkbook/sheet" property

[big_daddy]

The latest version of ExcelCOM_UDF.au3 includes an _ExcelAttach() function.

[rudi]

The latest version of ExcelCOM_UDF.au3 includes an _ExcelAttach() function.

Thanks for pointing me into the right direction!

I had a quick look at that UDF and had to recognize, that I'm currently not able to use it -- will have to read some COM docs first.

As I need to read a range of Cells (e.g. "a1:C15") I'll need to first get the selection, then read the 3 columns to an 3 rows 5 lines array.

Well, currently I'm working with an ugly duckly script using Send() and ClipGet() / ClipPut(), but I'll readup the stuff to make it better. (As I use the German Excel Version I have to juggle with "." and "," to be able to calcualte in Autoit, 2.33 ist 2,33 in local notation here)

I'll come back with further questions, I think after I tried with that _ExcelAttach() and the other _Excel*() functions in that UDF....

[edit: "german" excel notation for decimal separator]

Thanks, Rudi.

Edited May 10, 2008 by rudi

[PsaltyDS]

As I need to read a range of Cells (e.g. "a1:C15") I'll need to first get the selection, then read the 3 columns to an 3 rows 5 lines array.

It is not hard with the ExcelCOM_UDF.au3 UDF:

#include <Array.au3>; Only for _ArrayDisplay()
#include <ExcelCOM_UDF.au3>

$oExcel = _ExcelAttach("C:\Temp\MySpreadsheet.xls", "FilePath")
$avArray = _ExcelReadSheetToArray($oExcel, 1, 1, 3, 15)
_ArrayDisplay($avArray, "A1 Thru C15")

[rudi]

It is not hard with the ExcelCOM_UDF.au3 UDF:

#include <Array.au3>; Only for _ArrayDisplay()
#include <ExcelCOM_UDF.au3>

$oExcel = _ExcelAttach("C:\Temp\MySpreadsheet.xls", "FilePath")
$avArray = _ExcelReadSheetToArray($oExcel, 1, 1, 3, 15)
_ArrayDisplay($avArray, "A1 Thru C15")

Very nice, thanks a lot!

Is it possible to _ExcelAttach() just to the currently open Exel.exe / Workbook / Worksheet?

How to find out what the current selection range is?

Have a nice Pentecost Holiday.

Regards, Rudi

[rudi]

Hi.

I tried to understand that UDF, well, COM isn't what I know a lot about so far ...

If I got the functions correctly, there isn't one included to set the PrintArea, correct? So I tried this one, which doesn't work:

#include <ExcelCOM_UDF.au3>

$oExcel = _ExcelAttach("c:\daten\MySheet.xls", "FilePath")
ConsoleWrite(@error & @CRLF)
$oExcel.ActiveSheet.PageSetup.PrintArea("$A$1:$F$43")

Output:

>Running:(3.2.10.0):C:\Programme\AutoIt3\autoit3.exe "c:\TEMP\ExcelPrintAreaSet.au3"    
0
C:\TEMP\ExcelPrintAreaSet.au3 (5) : ==> The requested action with this object has failed.: 
$oExcel.ActiveSheet.PageSetup.PrintArea("$A$1:$F$43") 
$oExcel.ActiveSheet.PageSetup.PrintArea("$A$1:$F$43")^ ERROR
->13:17:02 AutoIT3.exe ended.rc:1
>Exit code: 1   Time: 2.052

The VBA macro code doing the same smoothly is this one:

ActiveSheet.PageSetup.PrintArea = "$A$1:$F$43"

I suspect that calling this by using COM the syntax is different? Where could I lookup this?

Regards, Rudi.

[big_daddy]

Have you tried the following?

$oExcel.ActiveSheet.PageSetup.PrintArea = "$A$1:$F$43"

[rudi]

Have you tried the following?

$oExcel.ActiveSheet.PageSetup.PrintArea = "$A$1:$F$43"oÝ÷ Ûú®¢× "Ø^#P1Z»r¢éÝ¢Ø^®«¨µÆ¥Ê®¢ÚZ²È§Ø^¥ªÚë^®È§¥ªÞ׬ÂØ­)¶¢iÙb觶¬r^nZºÚ"µÍÚ[ÛYH Ñ^Ù[ÓÓWÕQ]LÉÝÂÌÍÛÑ^Ù[HÑ^Ù[]XÚ
    ][ÝØÎÌLÙ][ÌLÓ^TÚY]É][ÝË  ][ÝÑ[T]   ][ÝÊBÛÛÛÛUÜ]JÜ  [ÈÔBÈ    ÌÍÛÑ^Ù[XÝ]TÚY]YÙTÙ][XJ ][ÝÉÌÍÐIÌÍÌNÌÍÑÌÍÍÉ][ÝÊBÌÍÛÑ^Ù[XÝ]TÚY]YÙTÙ][XHH   ][ÝÉÌÍÐIÌÍÌNÌÍÑÌÍÍÉ][Ý

What you mentioned is working perfectly, thanks!

With Excel's Macro Recorder I can find out a lot of things: Is there some reference (MSDN e.g.?) to lookup what COM calls do the same like VBA?

How to find out what's the

1.) current selection

2.) current active cell?

The fog is clearing just that much that I can recognize: I almost can't see anything

Regards, Rudi.

[rudi]

Hi.

Well, I can find the ActiveCell this way now:

$oExcel = _ExcelAttach("c:\daten\MySheet.xls", "FilePath")
dim $active[2]

$active[0]=$oExcel.activecell.column
$active[0]=MakeAlpha($active[0])
$active[1]=$oExcel.activecell.row
MsgBox(0,"","Active Cell: " & $active[0] & $active[1],10)


Func MakeAlpha($Column)
    ConsoleWrite($column & @CRLF)
$Pos2=floor($column/26)
$Pos1=mod($column,26)
if $Pos2 Then 
    Return Chr(64+$pos2) & Chr(64+$Pos1)
Else
    Return Chr(64+$Pos1)
EndIf

EndFunc

BTW: Does this always return fixed address values? ($A$1 instead of A1)

How can I get all the valid $STRING allowed to be used for $oExcel.selection.<$STRING> ??

Regards, Rudi.

Edited May 12, 2008 by rudi

[big_daddy]

This should have everything you need.

Excel Object Model Reference

[rudi]

This should have everything you need.

Excel Object Model Reference

Thanks.

e.g. this $oExcel.ActiveCell.* I cannot find at all in these pages.

Do you know if there is a "all-in-one.pdf" for the "Excel Object Model Reference" somewhere?

Regards, Rudi.

[big_daddy]

Thanks.

e.g. this $oExcel.ActiveCell.* I cannot find at all in these pages.

Do you know if there is a "all-in-one.pdf" for the "Excel Object Model Reference" somewhere?

Regards, Rudi.

Application.ActiveCell Property

Not one that I know of.

[PsaltyDS]

Thanks.

e.g. this $oExcel.ActiveCell.* I cannot find at all in these pages.

Do you know if there is a "all-in-one.pdf" for the "Excel Object Model Reference" somewhere?

Regards, Rudi.

.PrintArea is a property of PageSetup. You are probably more interested in ActiveSheet.PageSetup than ActiveCell.

[rudi]

.PrintArea is a property of PageSetup. You are probably more interested in ActiveSheet.PageSetup than ActiveCell.

No. I'm interested in a brief list of all the $oExcel.foo.bar.something possibilities. "BigDaddy" pointed me towards the MSDN Excel COM Reference, but, propably due to my poor COM skills , I can't pull out of these pages what I'm looking for...

A CHM file or PDF for full text search would be very nice

Regards, Rudi.

[ahha]

Application.ActiveCell Property

Not one that I know of.

This appears to be the right forum. The original question was "I want to do some special calculations depending on a selection I made in an already opened Excel Workbook/Worksheet."

I never did see an answer. _ExcelAttach() requires knowledge of the opened sheet or a sheet to open.

Is there any way to find out what sheets are currently open? It would then be able to attach to an already open sheet which is what I also want to do.