Sign in to follow this  
Followers 0
Sveach

Help with a loop

3 posts in this topic

#1 ·  Posted (edited)

Hey guys, novice programmer here needing some help with a loop. Here's the story:

Trying to write a script that will do the following:

1) Take user input for a number.

2)Loop through rows in an excel file until it finds that number, in a specified column.

3)Output the contents of that entire row.

I'm a bit stumped on what to do. I don't have a lot of experience with programming. Here's attempt #9 at it so far:

#include <excelcom.au3>
$loc_num = InputBox("WW Center Lookup Utility", "Please enter the location number","15638");
$excel_file = "c:\centers.xls";
$column = "A";
$row = 2;
$readfile = _XLread($excel_file,1,$column,$row);

if _XLread($excel_file,1,$column,$row) == $loc_num Then
    $name = _XLread($excel_file,1,"B",$row)
    $street = _XLread($excel_file,1,"D",$row)
    MsgBox(0,"",$name & @cr & $street)
Else
    $row = $row + 1
    $readline = _XLread($excel_file,1,$column,$row)
    if $readline == $loc_num Then
    MsgBox(0,"",$readline)
    EndIf
EndIf

My biggest hurdle is this: If the first line it reads isn't the correct line (by comparing $loc_num and $readfile), how do I get it to increment $row by 1 (to look at the next line) and actually get it to read the next line? I know there's some redundancy in my script, as far as using variables, etc.

Edited by Sveach

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

#include <excelcom.au3>
$loc_num = InputBox("WW Center Lookup Utility", "Please enter the location number","15638");
$excel_file = "c:\centers.xls";
$column = "A";
$row = 2;
$readfile = _XLread($excel_file,1,$column,$row);

While 1
    if _XLread($excel_file,1,$column,$row) == $loc_num Then
        $name = _XLread($excel_file,1,"B",$row)
        $street = _XLread($excel_file,1,"D",$row)
        MsgBox(0,"",$name & @cr & $street)
        ExitLoop
    Else
        $row = $row + 1
    EndIf
WEnd

or if you prefer:

#include <excelcom.au3>
$loc_num = InputBox("WW Center Lookup Utility", "Please enter the location number","15638");
$excel_file = "c:\centers.xls";
$column = "A";
$row = 2;
$readfile = _XLread($excel_file,1,$column,$row);

$found = 0

While NOT $found
    if _XLread($excel_file,1,$column,$row) == $loc_num Then
        $name = _XLread($excel_file,1,"B",$row)
        $street = _XLread($excel_file,1,"D",$row)
        MsgBox(0,"",$name & @cr & $street)
        $found = 1
    Else
        $row = $row + 1
    EndIf
WEnd

You should probably put in some catches to prevent infinite loops (like having a max row #, and if it is exceeded then kick out and display a message saying "Not Found")

Edited by Leighwyn

Share this post


Link to post
Share on other sites

#include <excelcom.au3>
$loc_num = InputBox("WW Center Lookup Utility", "Please enter the location number","15638");
$excel_file = "c:\centers.xls";
$column = "A";
$row = 2;
$readfile = _XLread($excel_file,1,$column,$row);

While 1
    if _XLread($excel_file,1,$column,$row) == $loc_num Then
        $name = _XLread($excel_file,1,"B",$row)
        $street = _XLread($excel_file,1,"D",$row)
        MsgBox(0,"",$name & @cr & $street)
        ExitLoop
    Else
        $row = $row + 1
    EndIf
WEnd

or if you prefer:

#include <excelcom.au3>
$loc_num = InputBox("WW Center Lookup Utility", "Please enter the location number","15638");
$excel_file = "c:\centers.xls";
$column = "A";
$row = 2;
$readfile = _XLread($excel_file,1,$column,$row);

$found = 0

While NOT $found
    if _XLread($excel_file,1,$column,$row) == $loc_num Then
        $name = _XLread($excel_file,1,"B",$row)
        $street = _XLread($excel_file,1,"D",$row)
        MsgBox(0,"",$name & @cr & $street)
        $found = 1
    Else
        $row = $row + 1
    EndIf
WEnd

You should probably put in some catches to prevent infinite loops (like having a max row #, and if it is exceeded then kick out and display a message saying "Not Found")

Excellent, that's what I needed to know. A while loop with an if statement nested in it. Thanks! I'll give this a try today.

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