SkysLastChance Posted January 3, 2014 Share Posted January 3, 2014 $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. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Prime03 Posted January 3, 2014 Share Posted January 3, 2014 (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 January 3, 2014 by Prime03 SkysLastChance 1 Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 3, 2014 Author Share Posted January 3, 2014 (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 January 3, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Prime03 Posted January 3, 2014 Share Posted January 3, 2014 (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 January 3, 2014 by Prime03 Link to comment Share on other sites More sharing options...
jdelaney Posted January 3, 2014 Share Posted January 3, 2014 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. Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 3, 2014 Author Share Posted January 3, 2014 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. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
jdelaney Posted January 3, 2014 Share Posted January 3, 2014 (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 January 3, 2014 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. Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 3, 2014 Author Share Posted January 3, 2014 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. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 3, 2014 Author Share Posted January 3, 2014 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. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 3, 2014 Author Share Posted January 3, 2014 (edited) expandcollapse popup#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 January 3, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 4, 2014 Author Share Posted January 4, 2014 http://i915.photobucket.com/albums/ac355/Vaughn_Mitchell/excelpicture_zpsb34df141.png?t=1388792853 here is the excel file You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 4, 2014 Author Share Posted January 4, 2014 (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 January 4, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 6, 2014 Author Share Posted January 6, 2014 (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 January 6, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 6, 2014 Author Share Posted January 6, 2014 (edited) Has not changed much but this is what I have expandcollapse popup#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 January 6, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
water Posted January 9, 2014 Share Posted January 9, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 10, 2014 Author Share Posted January 10, 2014 Thank you I will do that You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 10, 2014 Author Share Posted January 10, 2014 (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 January 10, 2014 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 10, 2014 Author Share Posted January 10, 2014 If I am not making since please someone tell me I can try to clear it up and even throw in some pictures... You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
SkysLastChance Posted January 16, 2014 Author Share Posted January 16, 2014 can anyone lead me to a place where I can find these answers... the help scripts have been helping just not with this problem You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now