Sign in to follow this  
Followers 0
Sam137

How to write a value in excelsheet

25 posts in this topic

#1 ·  Posted (edited)

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)

post-65245-0-98542800-1316710632_thumb.j

Edited by Sam137

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Any other method without using the UDF?

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Can it be used with other people without downloading the UDF

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

You download ExcelCOM.au3, right?

Can you post the whole script?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

post-65245-0-69870600-1316714812_thumb.j

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

You can not have both excel udfs in your script.

Remove "#include <excel.au3>"


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Yes, solution is in my last post.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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