Sign in to follow this  
Followers 0
SkysLastChance

[Noob] Help Looping a code.

19 posts in this topic

 
$FilePath = FileOpenDialog("Excel File", @ScriptDir, "Excel Files (*.xls)")
If @error Then
    Exit
EndIf

$ObjExcel = ObjCreate("Excel.Application")
$ObjExcel.Visible = True
$ObjWorkbook = $ObjExcel.Workbooks.Open($FilePath, 0, "false")

While 1 
    $ObjExcel.ActiveWorkbook.Save()
    $CellString = $ObjExcel.Cells(1,"A").Value
    If $CellString = "No" Then
    If $CellString = "Yes" Then
    If $CellString = "Maybe" Then
      
    ExitLoop
    EndIf
 
    Sleep(1000); check the cell every 1 seconds
Wend 

I was wondering exactly how I would go about looping this code to go down a line when it checks for the yes no or maybe ? any advice would be much appreciated.


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

 
$FilePath = FileOpenDialog("Excel File", @ScriptDir, "Excel Files (*.xls)")
If @error Then
    Exit
EndIf

$ObjExcel = ObjCreate("Excel.Application")
$ObjExcel.Visible = True
$ObjWorkbook = $ObjExcel.Workbooks.Open($FilePath, 0, "false")

While 1 
    $ObjExcel.ActiveWorkbook.Save()
    $CellString = $ObjExcel.Cells(1,"A").Value
    If $CellString = "No" Then
       ExitLoop
    Elseif $CellString = "Yes" Then
       ExitLoop
    Else $CellString = "Maybe" Then
       ExitLoop
    EndIf   ; I edited this If statement for correct syntax demonstration, although it would always exit the loop if it was maybe.
 
    Sleep(1000); check the cell every 1 seconds
Wend 

I was wondering exactly how I would go about looping this code to go down a line when it checks for the yes no or maybe ? any advice would be much appreciated.

#include <Excel.au3>

$oExcel = _ExcelBookOpen($sFilePath , 1, true)


For $i = 1 to 100 Step 1 ;this would do 100 rows at a time, change it to whatever you need
    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "No"
                ;Do something
            Case $CellString = "Yes"
                ;Do something
            Case Else $CellString = "Maybe"
                ;Do something
        EndSelect
Next

Check out the excel UDFs, makes life a bit easier!

http://www.autoitscript.com/autoit3/docs/libfunctions/Excel%20Management.htm

Edit: Forgot an Else after the last case.

Edited by Prime03
1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Thank you this is awesome. It is working great... however I am running into one big problem... I need that code to work with this code as well. I cant seem to find a way to make them both work together. is there a way I can have both these codes work together or do I need to start back at the drawing board? 

$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
$rows = 1
Opt("WinTitleMatchMode", 1)
Local $loop = 0



Do
For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
$sR1 = $aArray[$i][3] ; Account
$sR2 = $aArray[$i][4] ; Order #
$sR3 = $aArray[$i][12] ; SCAC
$sR3 = $aArray[$i][13] ; Port of arival

send($sR1)
send("{ENTER}")
send($sR2)
send("{ENTER}")
send($sR3)
send("{ENTER}")
Next

    $loop = $loop + 1
Until $loop = $rows

I need both codes to work from the same line. So in other words after it checks yes,no or maybe. in cell 1 I need it to check and take information from that line and than move to the next line check yes, no or maybe, take information.move to the next line on and on for X amount of rows. I will look into the link you sent me as well I have just been working on this to much today I think LOL. I just changed the 100 too a input box variable and that works but I can't do it for both. so I am lost.

#include <Excel.au3>

$oExcel = _ExcelBookOpen($sFilePath , 1, true)


For $i = 1 to 100 Step 1 ;this would do 100 rows at a time, change it to whatever you need
    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "No"
                ;Do something
            Case $CellString = "Yes"
                ;Do something
            Case $CellString = "Maybe"
                ;Do something
        EndSelect
Next 

So what I was trying to do is where you put the Do somthing I was placing the code with the array witch I don't think will work because it will be stuck in a loop on first run through. correct me if I am wrong.

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Thank you this is awesome. It is working great... however I am running into one big problem... I need that code to work with this code as well. I cant seem to find a way to make them both work together. is there a way I can have both these codes work together or do I need to start back at the drawing board? 

$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
$rows = 1
Opt("WinTitleMatchMode", 1)
Local $loop = 0



Do
For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
$sR1 = $aArray[$i][3] ; Account
$sR2 = $aArray[$i][4] ; Order #
$sR3 = $aArray[$i][12] ; SCAC
$sR3 = $aArray[$i][13] ; Port of arival

send($sR1)
send("{ENTER}")
send($sR2)
send("{ENTER}")
send($sR3)
send("{ENTER}")
Next

    $loop = $loop + 1
Until $loop = $rows

I need both codes to work from the same line. So in other words after it checks yes,no or maybe. in cell 1 I need it to check and take information from that line and than move to the next line check yes, no or maybe, take information.move to the next line on and on for X amount of rows. I will look into the link you sent me as well I have just been working on this to much today I think LOL. I just changed the 100 too a input box variable and that works but I can't do it for both. so I am lost.

#include <Excel.au3>

$oExcel = _ExcelBookOpen($sFilePath , 1, true)


For $i = 1 to 100 Step 1 ;this would do 100 rows at a time, change it to whatever you need
    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "No"
                ;Do something
            Case $CellString = "Yes"
                ;Do something
            Case $CellString = "Maybe"
                ;Do something
        EndSelect
Next 

So what I was trying to do is where you put the Do somthing I was placing the code with the array witch I don't think will work because it will be stuck in a loop on first run through. correct me if I am wrong.

$oExcel = _ExcelBookOpen($sFilePath , 1, true)
$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
Opt("WinTitleMatchMode", 1)
$rows = 1


For $i = 1 to $rows Step 1 ;this would do 100 rows at a time, change it to whatever you need
    
    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "No"
                ;Do something
                Do
                    For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
                        $sR1 = $aArray[$i][3] ; Account
                        $sR2 = $aArray[$i][4] ; Order #
                        $sR3 = $aArray[$i][12] ; SCAC
                        $sR3 = $aArray[$i][13] ; Port of arival

                        send($sR1)
                        send("{ENTER}")
                        send($sR2)
                        send("{ENTER}")
                        send($sR3)
                        send("{ENTER}")
                    Next

                        $loop = $loop + 1
                Until $loop = $rows
            Case $CellString = "Yes"
                ;Do something
            Case $CellString = "Maybe"
                ;Do something
        EndSelect
Next











It seems it might be easier to start over from the drawing board, but here's how I think it should work. I'm unable to test this code so there might be issues, stitching code together can be a challenge sometimes. If it still doesn't work, try and give a more detailed explanation of what needs to happen and it might be easier to help.

Edited by Prime03

Share this post


Link to post
Share on other sites

If you are using the excel dom object, don't use Send().  Look up your relevant _excel function, and use that instead.


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

The send part of the code is not what I am worried about. I am not even in puting anything in excel I need information from excel and put into a 3 party program, but thank you.


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Just a tip I give to people: Send is not reliable, use Control* functions, or _ie* functions...such as controlsettext, or _IEFormElementSetValue

Else, I would first verify that the window is ative, and your control has focus.

Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Solid advice, I am using fields on IE with the code now I don't use send anymore. I found out the hard way it sucks. I was just using as example from old code I had. I am having trouble with the loop portion. Basically need to run a loop inside a loop.


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

Holy crap prime I think this is exactly what I need I am going to work on it and I will defiantly get back to you good sir. 


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

#include <File.au3>
#include <Excel.au3>
#include <Array.Au3>
#include <Clipboard.au3>
#include <crypt.au3>
#include <IE.au3>

Func Terminate()
    Exit 0
EndFunc



HotKeySet("{ESC}", "Terminate")

Opt("SendKeyDelay", 80)
Dim $oExcel, $sExcelFile, $ms
Dim $fExcelVisible = 1
While ProcessExists("EXCEL.EXE")
$ms = MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.") ;Retry=4 and Cancel=2
    If $ms=2 Then Exit
Sleep(250)
WEnd
$sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xls)") ;Changed $oExcel to $sExcelFile
If FileExists($sExcelFile) Then

    $oExcel = _ExcelBookOpen($sExcelFile,$fExcelVisible, False);this will open the chosen xls file.
If IsObj($oExcel) Then ConsoleWrite("Excel book " & $sExcelFile & " has been opened." & @CRLF)
EndIf

$number = inputbox( "Richardson Bot", "How many trucks?")



$oIE = _IECreate("XXX")
$hIE = _IEPropertyGet($oIE, "hwnd") ; Get Handle of the IE window

    _IELoadWait($oIE)


Local $username = _IEGetObjByName ($oIE, "username")
Local $password = _IEGetObjByName ($oIE, "password")
Local $button = _IEGetObjByName ($oIE, "submit")

    _IEFormElementSetValue ($username, "XXX")
    _IEFormElementSetValue ($password, "XXX")
    _IEAction ($button, "click")
    _IELoadWait($oIE)
    Sleep (3000)
    Send (50527601)
    Sleep (3000)
    Send ("{Enter}")
    _IELoadWait ($oIE)


_IELinkClickByText ($oIE, "Saved Invoices")


    _IELoadWait ($oIE)




$aArray= _ExcelReadSheetToArray($oExcel,2,1,$number,3,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
Opt("WinTitleMatchMode", 1)
$rows = 1
Local $loop = 0


For $i = 1 to $rows Step 1 ;this would do 100 rows at a time, change it to whatever you need

    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "MOLLARD"
                _IELinkClickByText ($oIE, "Mollard - CGI Crystal - Ctr# 2013196")
            Case $CellString = "RED RIVER SOUTH"
                _IELinkClickByText ($oIE, "Red River South- CGI Crystal - Ctr#2013196")
            Case $CellString = "STARBUCK"
                _IELinkClickByText ($oIE, "Starbuck - CGI Crystal - Ctr# 2013196")
            Case $CellString = "SOUTH LAKES"
                _IELinkClickByText ($oIE, "South Lakes - CGI Crystal - Ctr# 2013196")

                Do
                    For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
                        $sR1 = $aArray[$i][3] ; Account
                        $sR2 = $aArray[$i][4] ; Order #
                        $sR3 = $aArray[$i][12] ; SCAC
                        $sR3 = $aArray[$i][13] ; Port of arival

                        Local $BOL = _IEGetObjById ($oIE, "OPT_IDX_billoflading") ;BOL
                        Local $SCN = _IEGetObjById ($oIE, "OPT_IDX_scn") ;SCN
                        Local $SCAC = _IEGetObjById ($oIE, "GEN_scacCode") ;SCAC
                        Local $PORT = _IEGetObjById ($oIE, "OPT_IDE_portcity") ;Port

                                _IEFormElementSetValue ($BOL, "vmitchell")
                                _IEFormElementSetValue ($SCN, "vmitchell")
                                _IEFormElementSetValue ($SCAC, "vmitchell")
                                _IEFormElementSetValue ($PORT, "vmitchell")
                    Next

                        $loop = $loop + 1
                Until $loop = $rows

        EndSelect
Next

Okay so this is what I got I got the code to run however... I am trying to just test and have it do one. no such luck so far... it gets as far as typing in the Number but wont click the first link (Mollard - CGI Crystal - Ctr# 2013196). I will link the sheet if possible. I have a feeling this will give you a better idea of what I am trying to accomplish though, I will keep tinkering.

I need it to start reading from 2A not sure If I have it setup like that or not.

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Update: I got it to click the link. It is starting the read from cell a,1 which is no big deal, however It does not add the vmitchell into the fields I wanted it to. I figured it was my bad coding but I tryed to just make it creat a web page with no luck. It does everything I need it too tell I get to the code shown in box. 

Do
                    For $i =  1 to $aArray[0][0]  ; if you change 1 it will start reading from that.
                        $sR1 = $aArray[$i][3] ; Account
                        $sR2 = $aArray[$i][4] ; Order #
                        $sR3 = $aArray[$i][12] ; SCAC
                        $sR3 = $aArray[$i][13] ; Port of arival

                        Local $BOL = _IEGetObjById ($oIE, "OPT_IDX_billoflading") ;BOL
                        Local $SCN = _IEGetObjById ($oIE, "OPT_IDX_scn") ;SCN
                        Local $SCAC = _IEGetObjById ($oIE, "GEN_scacCode") ;SCAC
                        Local $PORT = _IEGetObjById ($oIE, "OPT_IDE_portcity") ;Port

                                _IEFormElementSetValue ($BOL, "vmitchell")
                                _IEFormElementSetValue ($SCN, "vmitchell")
                                _IEFormElementSetValue ($SCAC, "vmitchell")

It does nothing when it gets here =/

 

Edit: it now starts on row 2

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Okay I am stuck again... I have no idea how to get this to start working...

 

Edit: It enters in the fields now I got that figured out but still can't get this loop to work.

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

Has not changed much but this is what I have

#include <File.au3>
#include <Excel.au3>
#include <Array.Au3>
#include <Clipboard.au3>
#include <crypt.au3>
#include <IE.au3>

Func Terminate()
    Exit 0
EndFunc



HotKeySet("{ESC}", "Terminate")

Opt("SendKeyDelay", 80)
Dim $oExcel, $sExcelFile, $ms
Dim $fExcelVisible = 1
While ProcessExists("EXCEL.EXE")
$ms = MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.") ;Retry=4 and Cancel=2
    If $ms=2 Then Exit
Sleep(250)
WEnd
$sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xls)") ;Changed $oExcel to $sExcelFile
If FileExists($sExcelFile) Then

    $oExcel = _ExcelBookOpen($sExcelFile,$fExcelVisible, False);this will open the chosen xls file.
If IsObj($oExcel) Then ConsoleWrite("Excel book " & $sExcelFile & " has been opened." & @CRLF)
EndIf

$number = inputbox( "Richardson Bot", "How many trucks?")



$oIE = _IECreate("xxx")
$hIE = _IEPropertyGet($oIE, "hwnd") ; Get Handle of the IE window

    _IELoadWait($oIE)


Local $username = _IEGetObjByName ($oIE, "username")
Local $password = _IEGetObjByName ($oIE, "password")
Local $button = _IEGetObjByName ($oIE, "submit")

    _IEFormElementSetValue ($username, "xxx")
    _IEFormElementSetValue ($password, "xxx")
    _IEAction ($button, "click")
    _IELoadWait($oIE)
    Sleep (3000)
    Send (50527601)
    Sleep (3000)
    Send ("{Enter}")
    _IELoadWait ($oIE)


_IELinkClickByText ($oIE, "Saved Invoices")


    _IELoadWait ($oIE)




$aArray = _ExcelReadSheetToArray($oExcel,2,1,$number,16,True) ; $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 10, $iColCnt = 3, $iColShift = True as excel
Opt("WinTitleMatchMode", 1)
$rows = 2
Local $loop = 0



For $i = 1 to $rows Step 1 ;this would do 100 rows at a time, change it to whatever you need

    _ExcelBookSave($oExcel)
    $CellString = _ExcelReadCell($oExcel, $i, 1) ;Row =$i, column = 1)
        Select
            Case $CellString = "MOLLARD"
                _IELinkClickByText ($oIE, "Mollard - CGI Crystal - Ctr# 2013196")
                Sleep (3000)
            Case $CellString = "RED RIVER SOUTH"
                _IELinkClickByText ($oIE, "Red River South- CGI Crystal - Ctr#2013196")
            Case $CellString = "STARBUCK"
                _IELinkClickByText ($oIE, "Starbuck - CGI Crystal - Ctr# 2013196")
            Case $CellString = "SOUTH LAKES"
                _IELinkClickByText ($oIE, "South Lakes - CGI Crystal - Ctr# 2013196")
                Sleep (3000)

                Do

                        For $i =  2 to $aArray[0][0]  ; if you change 1 it will start reading from that.
                        $sR1 = $aArray[$i][3] ; Account
                        $sR2 = $aArray[$i][4] ; Order #
                        $sR3 = $aArray[$i][12] ; SCAC
                        $sR3 = $aArray[$i][13] ; Port of arival

                        Local $BOL = _IEGetObjByName ($oIE, "OPT_IDX_billoflading") ;BOL
                        Local $SCN = _IEGetObjByName ($oIE, "OPT_IDX_scn") ;SCN
                        Local $SCAC = _IEGetObjByName ($oIE, "GEN_scacCode") ;SCAC
                        Local $PORT = _IEGetObjByName ($oIE, "OPT_IDE_portcity") ;Port3

                                _IEFormElementSetValue ($BOL, "vmitchell")
                                _IEFormElementSetValue ($SCN, "vmitchell")
                                _IEFormElementSetValue ($SCAC, "vmitchell")
                                _IEFormElementSetValue ($PORT, "vmitchell")

                                _IEAction ($oIE, "home")

                                _IELoadWait ($oIE)
                    Next

                        $loop = $loop + 1
                Until $loop = $rows

        EndSelect
Next

Edit: do I need to add else to this???? 

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

I suggest to add some error checking to your script so you know where the problem is.

Each function sets @error. So I suggest to insert this line after each function call:

If @error Then MsgBox(0, "Error", "Error " & @error & " when calling function xxxx") ; replace xxxx with the name of the function

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thank you I will do that :)


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

I think I have found the problem.... The code is just looping the second code... this is what I thought was going to happen so instead of clicking the link running the loop once and then searching for the link again it is picking the link once and then running a loop inside the same link over and over. 

I need the code to 

Pick a link depending on what is in cell "A1" I need to read run the second code placing the info in the cells from excel in to the webpage from the same row (A row) and I know how to make it read cells just was testing the bot.

Than I need it to pick a link  again but from cell "B1" read run the second code from the same row B row

However what is happening  is that the bot is picking the link once and than looping the second code over and over never to pick the link again...

 

Edit: this is why no errors are popping up as well..

Edited by SkysLastChance

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

If I am not making since please someone tell me I can try to clear it up and even throw in some pictures...


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites

can anyone lead me to a place where I can find these answers... the help scripts have been helping just not with this problem 


Life's simple. You make choices and you don't look back.

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