Sign in to follow this  
Followers 0
prasadstudy

Error handling in For loop

9 posts in this topic

#1 ·  Posted (edited)

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

 

test.PNG

Edited by Jos
please use codebox and proper indentation. as shown.

Share this post


Link to post
Share on other sites



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

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

@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

 

 

 

Capture.PNG

Share this post


Link to post
Share on other sites

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

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

1 person likes this

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

@BrewManNH

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Issue resolved!!!

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