NewtonScripter Posted July 21, 2008 Share Posted July 21, 2008 I am currently using the ExcelCOM_UDF.au3 and I was looking through the list and didn't see an array formula function. I just learned about array formula function and I think I need it. So just to make sure, is there a function to add in array formulas? Or is it possible to create one? and if so, how. Link to comment Share on other sites More sharing options...
junkew Posted July 21, 2008 Share Posted July 21, 2008 I am currently using the ExcelCOM_UDF.au3 and I was looking through the list and didn't see an array formula function. I just learned about array formula function and I think I need it. So just to make sure, is there a function to add in array formulas? Or is it possible to create one? and if so, how.What do you mean with the array formula function? Sample?If you check the ExcelCom_UDF.AU3 file you can see the logic and most likely then are able to add functionality yourself FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
NewtonScripter Posted July 21, 2008 Author Share Posted July 21, 2008 (edited) What do you mean with the array formula function? Sample?array formulas have to be entered with CTRL+SHIFT+ENTERso it's a lil weirdA thing I want to do is add the number of 8 numbers in a row that are greater than a specified number in an another cellso it looks like this {=SUM(1*($C2:$C9>$A$8)}You don't enter it in with the {} though, or it doesn't work and you can't enter it like a reg formula also you type in =SUM(1*($C2:$C9>$A$8) and then CTRL+SHIFT+ENTERIf you check the ExcelCom_UDF.AU3 file you can see the logic and most likely then are able to add functionality yourselfI took a look at the file. I don't understand where you find all the variables and functions. Like this line:$oExcel.Activesheet.cells($RangeorRow).Formula = $sFormulais there a website that lists the properties of excel? i am having trouble finding it. Edited July 21, 2008 by NewtonScripter Link to comment Share on other sites More sharing options...
junkew Posted July 22, 2008 Share Posted July 22, 2008 (edited) You should use property "formula" You should enter a proper formula (you missed closing brace) Below sample should work $oExcel = ObjCreate("Excel.Application") If NOT IsObj($oExcel) Then consolewrite("Something wrong creating excel instance") $oExcel.Visible=1 $wb = $oExcel.workbooks.add $wb.worksheets(1).cells(1,1).formula="=SUM(1*($C2:$C9>$A$8))" Edit: Fixed formula to be english instead of dutch (SOM instead of SUM) Edited July 22, 2008 by junkew FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
NewtonScripter Posted July 23, 2008 Author Share Posted July 23, 2008 Where do you find information about the COM for excel? (Is it COM?) Like how did you know to fill out a cell you use something like this $o_Excel.Activesheet.Range("B1").Value = "hello" Link to comment Share on other sites More sharing options...
NewtonScripter Posted July 23, 2008 Author Share Posted July 23, 2008 (edited) You should use property "formula" You should enter a proper formula (you missed closing brace) Below sample should work $oExcel = ObjCreate("Excel.Application") If NOT IsObj($oExcel) Then consolewrite("Something wrong creating excel instance") $oExcel.Visible=1 $wb = $oExcel.workbooks.add $wb.worksheets(1).cells(1,1).formula="=SUM(1*($C2:$C9>$A$8))" Edit: Fixed formula to be english instead of dutch (SOM instead of SUM) I just tried your code... well, kinda I created a spreadsheet with column A goes from 1 to 100 And then i used this line of code: $o_Excel.Activesheet.Range("B3").Formula = "=SUM(1*(A1:A9>5))" which is basically the same format what it does is for the numbers 1 to 9, it prints out how many are greater than 5 when i just use that code it outputs 0 but when i do it manually and CTRL+SHIFT+ENTER it gives the correct answer of 4 Okay I just read about the OLE/COM Object Viewer Now the next step for me is to find the one with Excel.Application right? I don't know how to find the one with the VersionIndependentProgID = Excel.Applciation can someone point me in the right direction? Edited July 23, 2008 by NewtonScripter 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