Error handling in For loop

[prasadstudy]

I am facing an issue while fetching list of values from excel. I am not getting the value assigned to the index of the array var[3] as A3 does not exist in excel due to merge function. Hence it assigns the 3rd cell value to var[4] and keep the var[3] index as null. I want to skip the error and assign the value in the sequence.

Below is the program I have written to tackle on this circumstance but so far no success.

Global $xlup = -4162
Global $iRow = $ReferenceWorkBook.ActiveSheet.Range("F65536").End($xlup).Row
Global $var[$iRow]
Global $r = 3

For $i = ($iRow - $iRow) + 1 To $iRow - 1
    $test = _Excel_RangeRead($ReferenceWorkBook, 1, "F" & $r)
    If @error Then
        $i = $i - 1
        $r = $r + 1
        Exit
    Else
        $var[$i] = $test
        $r = $r + 1
    EndIf
Next

 

Edited September 13, 2017 by Jos
please use codebox and proper indentation. as shown.

[JLogan3o13]

Are you working by row, by column, or both? If you're just reading down the column you could do something like this (I only included column A Value and column B Reference).

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")
Local $oRange = _Excel_RangeRead($oWorkBook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("B:B"))
    _ArrayDisplay($oRange)

_Excel_BookClose($oWorkBook)
_Excel_Close($oExcel)

You could then iterate through your array and get any item that is not blank.

Or simply change the third parameter of _Excel_RangeRead to Default and you'll pull all used cells. You can then iterate through the array with something like this:

For $a = 1 To UBound($oRange) - 1
        If Not ($oRange[$a][0] = "") Then ConsoleWrite("Row " & $a & " values are: " & $oRange[$a][0] & ", " & $oRange[$a][1] & @CRLF)
    Next

 

[prasadstudy]

@JLogan3o13

I am working by row ($r) and column (F). I did modify the program (highlighted) per suggestion by still not getting the desire result.

Snapshot of original excel values are uploaded for your reference.

Global $xlup = -4162
Global $iRow = $ReferenceWorkBook.ActiveSheet.Range("F65536").End($xlup).Row
Global $var[$iRow]
Global $r=3

For $i=($iRow-$iRow)+1 To $iRow-1
$test=_Excel_RangeRead($ReferenceWorkBook,1,"F"&$r)
If Not ($test = "" or Null or @error) Then
$var[$i]=$test
$r=$r+1
Send($var[$i])
Send("{DOWN}") ; To print in excel sheet one by one
EndIf
Next

 

Outputs are as follow:-

5

10

 

 

 

[JLogan3o13]

A couple of questions, as your request is still a bit vague.

• First, you say "still not getting the desired result". From your screenshot above, what would the desired result look like?
• Second, instead of grabbing everything from 65536 up, why not use UsedRange?
• Lastly, it would be a lot easier, rather than running _Excel_RangRead multiple times, just to read the entire range into an array and then loop from there. This works just fine for me, prints out every cell that is not blank:

#include <Excel.au3>

Local $sTest
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx", Default, True)
Local $oRange = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("F:F"))

    For $a = 1 To UBound($oRange) - 1
        $sTest = $oRange[$a]
            If Not ($sTest = "") Then
                Send($sTest & "{DOWN}")
            EndIf
    Next


_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

If you're expecting something different, please show what you would expect to see.

[prasadstudy]

@JLogan3o13

What I wanted is to add all the values into an array without null in it. I have almost done it in the below program but for some unknown reasons, it is not moving out of the loop after adding all the values in the array.

Attached is the workbook contains values to be fetched from.

HotKeySet("{SPACE}", "_Exit")
Func _Exit()
Exit
EndFunc ;==> _Exit()
#include <Excel.au3>
$Referencepath=IniRead("D:\Selenium\ABABank\BankExec\Config.ini", "General","Reference","default")
Global $OpenExcel=_Excel_Open()
Global $ReferenceWorkBook=_Excel_BookOpen($OpenExcel,$Referencepath,Default, True)
Opt("WinTitleMatchMode",2)
WinSetState("Reference", "", @SW_MAXIMIZE)
Sleep(1000)
Global $xlup = -4162
;Global $iRow =_Excel_RangeRead($ReferenceWorkBook, Default, $ReferenceWorkBook.ActiveSheet.UsedRange.Columns("F:F"))
Global $iRow = $ReferenceWorkBook.ActiveSheet.Range("A65536").End($xlup).Row
sleep(1000)
Send($iRow)
Send("{DOWN}")
Global $text=""
Global $r=3
Global $var[$iRow]

Global $Flag=0
For $i=($iRow-$iRow)+1 To $iRow-1
Sleep(200)
$text=_Excel_RangeRead($ReferenceWorkBook,1,"A"&$r)
If ($text<>"") Then
$var[$i]=$text
$r=$r+1
Send($var[$i])
Send("{DOWN}")
ElseIf ($Flag<>$iRow) Then
$r=$r+1
$i=$i-1
$Flag=$Flag+1
Else
Exit
EndIf
Next
Send("I am out of the loop") ; Just to make sure program is out of the loop after printing all the fetched values.

Reference.xlsx

[BrewManNH]

Get rid of the line $i=$i-1. You're resetting the loop counter variable, so it will never get to the end.

[prasadstudy]

@BrewManNH

It works after excluding the statement '$i=$i-1'. Thank you so much!!!

[prasadstudy]

oh... I have just noticed an another issue in the program. It is coming out of the loop properly but still adding NULL into the array which I don't want.

Below is the program I have written.

Can anyone tell me how to exclude NULL from getting added into array?

$Referencepath=IniRead("XXXYYY")

Global $iRow = $ReferenceWorkBook.ActiveSheet.Range("F65536").End($xlup).Row
sleep(100)
Global $text=""
Global $r=3
Global $var[$iRow]

Global $Flag=0
For $i=($iRow-$iRow)+1 To $iRow-1
Sleep(100)
$text=_Excel_RangeRead($ReferenceWorkBook,1,"F"&$r)
If ($text<>"")Then ; I tried with NULL as well instead of double quotes but no different results.
$var[$i]=$text
$r=$r+1
ElseIf ($Flag<>$iRow) Then
$r=$r+1
$Flag=$Flag+1
Else
Exit
EndIf
Next

For $z=1 To $iRow-1
 Send($var[$z]) ; 
 Send("{DOWN}")
 Next
 

Reference.xlsx

[prasadstudy]

Issue resolved!!!