Sign in to follow this  
Followers 0
rudi

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

16 posts in this topic

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.


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites



There was some kind of "ActiveWorkbook/sheet" property

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by rudi

Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

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

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

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.


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

Have you tried the following?

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

Share this post


Link to post
Share on other sites

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ÉÝÂÌÍÛÑ^Ù[^Ù[]XÚ
    ][ÝØÎÌLÙ][ÌLÓ^TÚY]É][ÝË  ][ÝÑ[T]   ][ÝÊBÛÛÛÛUÜ]JÜ  [ÈÔBÈ    ÌÍÛÑ^Ù[]TÚY]YÙTÙ][XJ ][ÝÉÌÍÐIÌÍÌNÌÍÑÌÍÍÉ][ÝÊBÌÍÛÑ^Ù[]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 :D

Regards, Rudi.


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by rudi

Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

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.


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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


Earth is flat, pigs can fly, and Nuclear Power is SAFE!

Share this post


Link to post
Share on other sites

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.

Share this post


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
Sign in to follow this  
Followers 0