Jump to content
Sign in to follow this  
XinYoung

Trouble With my Loop Using Excel & IE

Recommended Posts

Hello all,

Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.

I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.

For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.

  • The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B.
  • The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine.
  • SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto...
  • EnterCourse(). This simply gets us to the place where column B's content comes into play.
  • UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1 :frantics:
Func OpenExcel()
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        $oExcel = _Excel_Open()
        Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True)
        $oExcel.Sheets("CopyCourses").Activate
;~ Get all used cells in columns A and B
        $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B"))
;~ Create the $aSearchResult array
        ReDim $aSearchResult[UBound($aSearchItems)]
;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1)
        For $i = 0 To UBound($aSearchItems) - 1
;~ Column 0
            ConsoleWrite($aSearchItems[$i][0] & @CRLF)
;~ Column 1
            ConsoleWrite($aSearchItems[$i][1] & @CRLF)
            $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1])
        Next
        _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
;~ When the loop is complete, run the Finished function.
        Finished()
    EndIf
EndFunc   ;==>OpenExcel

Func SearchCourse($_sSearchResult, $_userName)
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        _IENavigate($oIE, $urlBBCourseSearch)
        _IELoadWait($oIE)
        Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
        Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
        _IEFormElementSetValue($oSearchString, $_sSearchResult)
        _IEFormSubmit($oForm)
        _IELoadWait($oIE)
        Local $oBBTable = _IETableGetCollection($oIE, 2)
        $aBBTableData = _IETableWriteToArray($oBBTable)
        Return SearchResult()
    EndIf
EndFunc   ;==>SearchCourse

Func SearchResult()
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        Local $sResult
        $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
        If $iSearchIndex = -1 Then
            $sResult = "Course Not Found"
            _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
        Else
            EnterCourse()
            $sResult = "UnEnrolled!"
            _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
        EndIf
        Return $sResult
    EndIf
EndFunc   ;==>SearchResult

Func EnterCourse()
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        Local $clickFail = "DATE CREATED"
        _IELinkClickByIndex($oIE, 34)
        _IELoadWait($oIE)
        $sourceCode = _IEBodyReadHTML($oIE)
        If StringInStr($sourceCode, $clickFail) <> 0 Then
            _IELinkClickByIndex($oIE, 35)
            _IELoadWait($oIE)
        EndIf
        $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink")
        _IEAction($oLink, "click")
        Sleep(500)
        _IELinkClickByText($oIE, "Users")
        _IELoadWait($oIE)
        $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1])
    EndIf
EndFunc   ;==>EnterCourse

Func UnenrollNow($_sourceCourseId, $_userName)
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        Local $UserError = "No users found"
        Local $sResult
        $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString")
        _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex')
        $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString")
        _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex')

        ;Paste whats copied from column B into the Username text box.
        Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm")
        Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText")
        
        ;PROBLEM HERE...
        
        _IEFormElementSetValue($oSearchString, $_userName)
        
        ;^^^^^^ WHY IS $_userName ALWAYS B1 ???
        
        Sleep(1000)
        _IEFormSubmit($oForm)
        _IELoadWait($oIE)

        $sourceCode = _IEBodyReadHTML($oIE)
        If StringInStr($sourceCode, $UserError) <> 0 Then
            $sResult = "User Not Found"
            _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
            Return $sResult
        Else
            MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal")
            $sResult = "DUN"
            _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1")
            Return $sResult
        EndIf
    EndIf
EndFunc   ;==>UnenrollNow

Please let me know if any further information is needed.

If you see other problems or redundancies in my code, please let me know.

Thank you!

 

Edited by XinYoung

Share this post


Link to post
Share on other sites

Hard to say just looking at this code, since we don't have access to your web site.  Do I understand right, that the problem is in IE when you set the value ?  Could you show a screen of the problem with the associated DOM ?

Share this post


Link to post
Share on other sites

Sure. Here's the DOM for the inputbox that requires the values from column B in my Excel sheet.

I don't think there's a problem with how I'm manipulating IE though. I think I'm storing the Excel content wrong or looping through Excel wrong.

image.thumb.png.4fcfe0bb900c59665f237ef8804cc251.png

Share this post


Link to post
Share on other sites
$aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1])

$i is undefined in Func EnterCourse(), I don't even understand how you don't get an error

Share this post


Link to post
Share on other sites
1 hour ago, XinYoung said:

i declare it globally at the beginning of the script...

Remove that, it doesn't make any sense, make your script robust, Any variables inside of a func should come local or from param.  I understand that sometimes, you need to have global vars, but if you want to solve your problems, you should start reducing the bad programming habits.  Sorry from being rude...

ps : Also use : Opt ("MustDeclareVars, 1) at the very beginning of your script.

Edited by Nine

Share this post


Link to post
Share on other sites

OK... You're not rude lol

So, for best practice. how/where should I implement the $i within the EnterCourse() func?

Func EnterCourse()
    If Not WinExists($hWnd) Then
        MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.")
        _Exit()
    Else
        Local $clickFail = "DATE CREATED"
        _IELinkClickByIndex($oIE, 34)
        _IELoadWait($oIE)
        $sourceCode = _IEBodyReadHTML($oIE)
        If StringInStr($sourceCode, $clickFail) <> 0 Then
            _IELinkClickByIndex($oIE, 35)
            _IELoadWait($oIE)
        EndIf
        $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink")
        _IEAction($oLink, "click")
        Sleep(500)
        _IELinkClickByText($oIE, "Users")
        _IELoadWait($oIE)
        $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1])
        
        ;WARNING: $i: possibly used before declaration.
        
    EndIf
EndFunc   ;==>EnterCourse

 

Share this post


Link to post
Share on other sites
45 minutes ago, XinYoung said:

Do i just throw it in as a local declaration at the beginning?

No, $i comes from another func.  But it is not your only problem.  Lets just take EnterCourse func. 

$hWnd, $oIE, $sourceCode, $aSearchResult, $aSearchItems and $i have all been declared as global vars.  That is really not good. 

$i for example comes from a For loop.  If you read carefully help file, it says that "The variable will be created automatically with Local scope, even when MustDeclareVars is on."  So $i has been declare local in another func.  You cannot access it even if you declare local or global or whatever. Those few functions that you have posted at the beginning needs to be rethink with the best practices :  All vars should be declared locally or passed as function parameters.  If you do that you probably won't have the same number of functions, probably less.
 

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  

  • Similar Content

    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By ThePoro
      Hi everyone.
      I want to ask about this : 
      I want it runs from 1 to 100 and It opens 10 firefox profiles then access youtube. After I close a firefox window, the loop runs and wait for another window close until loop ends
      I have a loop like this.
      Func launch() Local $from = Int(GUICtrlRead($input1)) Local $to = Int(GUICtrlRead($input2)) If $to <> "" Then While $from <= $to Local $profile = $to _RunDos("start firefox.exe -p " & $profile & " -no-remote youtube.com") $to=$to+1 WEnd Else Local $profile = $to _RunDos("start firefox.exe -p " & $profile & " -no-remote youtube.com") EndIf EndFunc Is there any solution?
      Thank you!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By SkysLastChance
      The problem that I am having is when my Msgbox pops up it come up blank.
      I would like it to show Nonprocedure.  
      Why is my msgbox coming up blank?
      How do I grab NonProcedure code and display it  in msgbox. 
      Do $oInputs = _IETagNameGetCollection($oIE,"class") For $oInput in $oInputs If StringStripWS($oInput.innertext,1) = "medical__name" Then $otarget = $oInput $vTxt = $otarget. ExitLoop Else $target = "1" EndIf Next Until $otarget <> "1" MsgBox(0,"",$vTxt) <div class="medical__summary"> <a class="medical__name" onclick="trackmedicalEvent('medicalClick', 'click', '4990000', '82546', 'tri', 1, '');" href="/field/origin/a/xid=i5dbe04e5d2ac4ad58ea5bdb7049b1af7">NonProcedure</a> <a class="medical__group" href="/field/origin">Origin <span class="medical__category-name">(medical)</span></a> <div class="medical__extended-fields"> <span class="medical__extended-field">Number 319390</span> <span class="medical__extended-field">Type</span> </div> </div>  
    • By Rhidlor
      First off, the project I'm working on revolves around AS/400 "Client Access software", it's foundation is directly influenced by the thread linked at the bottom of this post. Moving on, to explain the problem I'm facing; my project utilizes an infinite While loop and automatically performs semi-hard coded monotonous tasks to save users time and effort, the problem is, occasionally and unexpectedly "Display Messages" will popup and the core script will continue executing instead of dismissing said message and the script "breaks". As a countermeasure I've added some code to the While loop in an effort to intervene and dismiss these display messages before the core script has a chance to do anything... however it doesn't work. I hope I didn't do too bad of a job explaining that. 
      So I think my question is: How can I temporarily "pause" the core script when these messages spontaneously appear?
      The only other solution I thought of would be to check if a display message has appeared before executing every line of code but that obviously isn't very practical.
      Any and all help is greatly appreciated!
      Thanks
       
      ;Loop to keep script running and handle display messages While 1 dismiss_display_messages() Sleep(50) WEnd ;Function to dismiss display messages Func dismiss_display_messages() If $ps.SearchText("Display Messages") Then $ps.SendKeys("[enter]", 8, 1) ;This while loop waits until the display message has disappeared to resume the core script While $ps.SearchText("Display Messages") Sleep(100) WEnd EndIf EndFunc  
       
×
×
  • Create New...