How to write a value in excelsheet

[Sam137]

In a excel sheet I want to search for the occurence of $currRepTitle in the range and select the cell which at the right(3 positions) of that particular occurence and mark it as "Y". See attached. How to accomplish this...

$currRepTitle = "aaa"
_ExcelSheetActivate($oExcel, "Report List")
   $oExcel.Application.ActiveSheet.Range ("A1:C200").Find ($currRepTitle).Select  
   $oExcel.Application.ActiveCell.Offset ($aa).Select   
   $oExcel.Application.ActiveCell.Offset (0, 3).Select  
   _ExcelWriteCell($oExcel, "Yes", "D1:D200",4)
   _ExcelBookSave($oExcel)

Edited September 22, 2011 by Sam137

[water]

There is an available. Function _ExcelFindInRange "Finds all instances of a string in a range and returns their addresses as a two dimensional array". Move 3 cells to the right and use _ExcelWriteCell to write "Yes".

[Sam137]

Any other method without using the UDF?

[water]

No, because the UDF is just a wrapper around Excel COM interface (what you have already posted in your example)

[Sam137]

Can it be used with other people without downloading the UDF

[water]

Sure. Compile the script and pass the exe to the users. Then everything is contained in one single file.

Use obfuscator to reduce the size and remove all unused functions etc.

[Sam137]

Ok, how to use the excel com UDF? I dont know...

[water]

Download the UDF to the AutoIt\Include directory or the directory where your script resides.

The ExcelCOM UDF is just an extended Excel UDF (which is part of AutoIt), so you can use the examples there.

Call _ExcelFindInRange as described in the UDF - every function has a header that describes syntax, returned values and error codes.

[Sam137]

Thanks, when i try to download Excel.au3, EXCELCOM_UDF into the autoit/include folder....

And i executed

$aa = _ExcelFindInRange($oExcel, $ConcatVal, "D1:D200")

MsgBox(0,"",$aa)

Its saying "not a valid function"

Edited September 22, 2011 by Sam137

[water]

You download ExcelCOM.au3, right?

Can you post the whole script?

[Sam137]

I downloaded EXCELCOM_UDF.au3, when i go and search the file is not there in the autoit/include. when i re download the same, it asking for would you want to replace ?

[water]

Than it has to be somewhere else. I don't know where your browser saves downloaded files. Search your disk for the UDF?

Edited September 22, 2011 by water

[Sam137]

I sought out the prob and its in the autoit/include dir. Still i get the same error

[Sam137]

Now i have included the header #include <ExcelCOM_UDF.au3>.

When i run the script, its showing the error as attached screen shot.

[water]

I have a c:\temp\test.xls that looks like

line 1
line 2
line 3
aaa

when I use

#include <excelcom_udf.au3>
#include <array.au3>
$ConcatVal = "aaa"
$oExcel = _ExcelBookOpen("C:\temp\test.xls")
$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200")
_ArrayDisplay($aFound)

I get the following display

[0]|1
[1]|A4|Z4S1|1|4

[water]

You can not have both excel udfs in your script.

Remove "#include <excel.au3>"

[water]

Yes, solution is in my last post.

[water]

If you use my example excel this script should write "Yes" into column 4 of the line with "aaa"

#include <excelcom_udf.au3>
#include <array.au3>
$ConcatVal = "aaa"
$oExcel = _ExcelBookOpen("C:\temp\test.xls")
$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200")
_ArrayDisplay($aFound)
_ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 3)

[Sam137]

when i used the same code, i am not getting the display... instead i am getting row and column as 0

[water]

You are sure you have a file C:\temp\test.xls with the content I posted?

Then run this extended script (error checking)

#include <excelcom_udf.au3>
#include <array.au3>
$ConcatVal = "aaa"
$oExcel = _ExcelBookOpen("C:\temp\test.xls")
if @error then exit msgbox(0,"","error opening excel file. @error = " & @error)
$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200")
if @error then exit msgbox(0,"","error finding string. @error = " & @error)
_ArrayDisplay($aFound)
_ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 3)
if @error then exit msgbox(0,"","error writing text. @error = " & @error)