rudi Posted May 10, 2008 Share Posted May 10, 2008 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! Link to comment Share on other sites More sharing options...
amokoura Posted May 10, 2008 Share Posted May 10, 2008 There was some kind of "ActiveWorkbook/sheet" property Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted May 10, 2008 Moderators Share Posted May 10, 2008 The latest version of ExcelCOM_UDF.au3 includes an _ExcelAttach() function. Link to comment Share on other sites More sharing options...
rudi Posted May 10, 2008 Author Share Posted May 10, 2008 (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 May 10, 2008 by rudi Earth is flat, pigs can fly, and Nuclear Power is SAFE! Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 10, 2008 Share Posted May 10, 2008 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 Link to comment Share on other sites More sharing options...
rudi Posted May 11, 2008 Author Share Posted May 11, 2008 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! Link to comment Share on other sites More sharing options...
rudi Posted May 12, 2008 Author Share Posted May 12, 2008 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! Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted May 12, 2008 Moderators Share Posted May 12, 2008 Have you tried the following? $oExcel.ActiveSheet.PageSetup.PrintArea = "$A$1:$F$43" Link to comment Share on other sites More sharing options...
rudi Posted May 12, 2008 Author Share Posted May 12, 2008 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. Earth is flat, pigs can fly, and Nuclear Power is SAFE! Link to comment Share on other sites More sharing options...
rudi Posted May 12, 2008 Author Share Posted May 12, 2008 (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 May 12, 2008 by rudi Earth is flat, pigs can fly, and Nuclear Power is SAFE! Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted May 12, 2008 Moderators Share Posted May 12, 2008 This should have everything you need.Excel Object Model Reference Link to comment Share on other sites More sharing options...
rudi Posted May 12, 2008 Author Share Posted May 12, 2008 This should have everything you need.Excel Object Model ReferenceThanks.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! Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted May 12, 2008 Moderators Share Posted May 12, 2008 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 PropertyNot one that I know of. Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 12, 2008 Share Posted May 12, 2008 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 Link to comment Share on other sites More sharing options...
rudi Posted May 12, 2008 Author Share Posted May 12, 2008 .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! Link to comment Share on other sites More sharing options...
ahha Posted September 30, 2008 Share Posted September 30, 2008 Application.ActiveCell PropertyNot 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now