Excel - Going through an Excel file line by line

[Nilkimas]

Hi all, 

 

I was wondering if someone can help me out. 

For my ongoing project I need to automate work in a tool. I have figured most of it out. Originally the script is in AutoHotKey but for reasons AutoIt is better. 

There are several things I need to do in an Excel file. 

1. I need to check for the first empty cell - This to know up to where to process it. 

2. Check one cell with the one below it, in case they are the same, I will need to loop the process in the tool

3. I need to take column A value, paste it somewhere, run through a process, then depending on the result of 2, write column D in subsequent fields. 

Then following that I need to go to the next row. 

I have had a look at the _Excel_RangeRead function in the Excel.au3, but I am struggling to figure out how to make the range work with a variable. 

Any help would be greatly appreciated. 

[AutoBert]

 

[Nilkimas]

Thank you for the pointer, sadly it doesn't help with the main issue of going through the file one line at a time, but I can use an Array for that. It'll take a little figuring out I think. But I should manage. 

[water]

Maybe this wiki page sheds some light onto the subject.

[water]

21 hours ago, Nilkimas said:

I have had a look at the _Excel_RangeRead function in the Excel.au3, but I am struggling to figure out how to make the range work with a variable. 

Please post the code you used, the value of @error and @extended after after calling _Excel_RangeRead and the result you get (nothing, wrong result ...)

[Nilkimas]

I'm trying to find how to get the values contained within a cell... 

The wiki page has a lot of information on how to get the range, but I can't seem to find the way to get the content. 

In AutoHotKey I used the following: 
A2 := test.ActiveSheet.cells.(Line,2).FormulaR1C1 to format a variable based on the content to then write/paste elsewhere. 

Edited December 1, 2022 by Nilkimas

[water]

I do not know AutoHotKey but I think in AutoIt your statement should look like:

Global $vCellR1C1  ; Formula of the read cell
Global $oWorkbook  ; Object of the Excel workbook to process
Global $iLine      ; Line number to read
; AutoHotKey:test.ActiveSheet.cells.(Line,2).FormulaR1C1 
$vCellR1C1 = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1

 

[Nilkimas]

That did it, thank you water. Really appreciate it.