Sign in to follow this  
Followers 0
lilruly

Cell addresses in excel

5 posts in this topic

Hello all, I am trying to read through a known column in excel and compare each row to the row above it. If it matches I want to compare other columns in that row to the row above it to look for other matches. Some other stuff will happen after that but that is where I am sticking. I am using the ExcelCom.au3 udf to read the column. I can loop through the column and show the contents of each cell but cannot figure out how to show the address of each cell and thereby create a variable of the row to use in other columns. I am close because I can get the address of the last active cell. I have been trying to use the _XPGetActive function but no luck.

This is what I have so far in an attempt to get the process right. (The commented sections are failed attempts:) )If anyone can help me over this hump (either with or without the ExcelCom udf) I would appreciate it.

CODE

include <ExcelCom.au3>

$path = ("c:\worksheet.xls")

$last = _XLLastRow ($path, "1")

$oExcel = ObjGet ("", "Excel.Application")

For $count = 1 To ($last -1)

$read = _XLRead ($path, "1", "D", $count)

; $row = _XLGetActive($path, $read, $Address)

; $Address = $oExcel.Application.activecell.address

; If $read = "Fred" Then

; MsgBox(0, "", "Contents " & $row)

; MsgBox(0, "", "Address " & $Address)

MsgBox(0, "", "Contents " & $read)

; EndIf

Next

_XLClose ($path)

Share this post


Link to post
Share on other sites



Hello all, I am trying to read through a known column in excel and compare each row to the row above it. If it matches I want to compare other columns in that row to the row above it to look for other matches. Some other stuff will happen after that but that is where I am sticking. I am using the ExcelCom.au3 udf to read the column. I can loop through the column and show the contents of each cell but cannot figure out how to show the address of each cell and thereby create a variable of the row to use in other columns. I am close because I can get the address of the last active cell. I have been trying to use the _XPGetActive function but no luck.

This is what I have so far in an attempt to get the process right. (The commented sections are failed attempts:) )If anyone can help me over this hump (either with or without the ExcelCom udf) I would appreciate it.

CODE

include <ExcelCom.au3>

$path = ("c:\worksheet.xls")

$last = _XLLastRow ($path, "1")

$oExcel = ObjGet ("", "Excel.Application")

For $count = 1 To ($last -1)

$read = _XLRead ($path, "1", "D", $count)

; $row = _XLGetActive($path, $read, $Address)

; $Address = $oExcel.Application.activecell.address

; If $read = "Fred" Then

; MsgBox(0, "", "Contents " & $row)

; MsgBox(0, "", "Address " & $Address)

MsgBox(0, "", "Contents " & $read)

; EndIf

Next

_XLClose ($path)

Where did you get your ExcelCom.au3? The Excel UDF I have is LocoDarwin's ExcelCOM_UDF.au3 dated 2-12-07. Those functions are not there. Where did they come from?

:whistle:


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

Where did you get your ExcelCom.au3? The Excel UDF I have is LocoDarwin's ExcelCOM_UDF.au3 dated 2-12-07. Those functions are not there. Where did they come from?

:whistle:

This is the version header:

;ExcelCom.au3 2_99 June17th 06 ;parameter for suppress macro (? does not work OfficeXP) @cameronsdad

;ExcelCom.au3 2_95 June17th 06 ; FontSize @blitzkrg; suppress macro security warning; Checkbox read tristate @cameronsdad

;ExcelCom.au3 2_93 May14th 06 ; SimuCal DeleteRow, DeleteColumn [****change parms "_XLSetColumnWidth"]

; Version: 2_90; May02 06; More Borders, etc DickB / corrected single row

; Version: 2_89: May02 06; More Fonts, etc DickB / Password sheet open proxy; fixed right justify

; Version: 2_84; ; Apr30th06; Simucal's Cell Font, width, colour, etc additions

; Version: 2_82; Feb06 consolidated

; Version: 2_81; Jan 31st ; fixed "CreatBlank" again!

; Version: 2_71; Jan 11th change ame csvFile to csvFileView.cvs ; also chart type XLScatter now trendlines

so it looks like my version is a bit out of date although that confuses me a bit since I have only been actively using AutoIt for about 3 months now. I did get it from a link in this forum.

Are you suggesting that my problem may be solved by updating my ExcelCom UDF? I can only hope it is that simple :lmao:

Share this post


Link to post
Share on other sites

This is the version header:

;ExcelCom.au3 2_99 June17th 06 ;parameter for suppress macro (? does not work OfficeXP) @cameronsdad

;ExcelCom.au3 2_95 June17th 06 ; FontSize @blitzkrg; suppress macro security warning; Checkbox read tristate @cameronsdad

;ExcelCom.au3 2_93 May14th 06 ; SimuCal DeleteRow, DeleteColumn [****change parms "_XLSetColumnWidth"]

; Version: 2_90; May02 06; More Borders, etc DickB / corrected single row

; Version: 2_89: May02 06; More Fonts, etc DickB / Password sheet open proxy; fixed right justify

; Version: 2_84; ; Apr30th06; Simucal's Cell Font, width, colour, etc additions

; Version: 2_82; Feb06 consolidated

; Version: 2_81; Jan 31st ; fixed "CreatBlank" again!

; Version: 2_71; Jan 11th change ame csvFile to csvFileView.cvs ; also chart type XLScatter now trendlines

so it looks like my version is a bit out of date although that confuses me a bit since I have only been actively using AutoIt for about 3 months now. I did get it from a link in this forum.

Are you suggesting that my problem may be solved by updating my ExcelCom UDF? I can only hope it is that simple :whistle:

Well... it's not always that simple. Multiple people have made multiple versions, and sometimes you just have to try them out and see what works for you. It's not an official part of AutoIt, and not maintained by the AutoIt developers.

It is significant though, that in the thread for randallc's ExcelCOM.au3 he recommends you use LocoDarwin's ExcelCOM_UDF.au3 instead:

** I am recommending people consider LocoDarwin's

(Yet Another) ExcelCOM UDF

becuse i think it will ultimately do all these functions and get to standard UDF;

Not all there yet, but some features are better already.

The randallc version hasn't been updated since October of last year, LocoDarwin's is from February of this year and is recommended by randallc...

:lmao:


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

Well... it's not always that simple. Multiple people have made multiple versions, and sometimes you just have to try them out and see what works for you. It's not an official part of AutoIt, and not maintained by the AutoIt developers.

It is significant though, that in the thread for randallc's ExcelCOM.au3 he recommends you use LocoDarwin's ExcelCOM_UDF.au3 instead:

The randallc version hasn't been updated since October of last year, LocoDarwin's is from February of this year and is recommended by randallc...

;)

You know I really hadn't noticed that there are completly different versions by different people out there. I guess I assumed that if someone was creating a totally different tool they wouldn't give it the same name :whistle: . I have LocoDarwins version now and can see that is is very well commented and that's bound to help some. Of course I may be back :lmao:

Thanks much

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