Jump to content

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
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 ?

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
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

 

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.
 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Sachs
      I have a few questions:
      BACKGROUND
      I am using Oracle Application Testing Suite's OpenScript 13 (Eclipse IDE), Oracle Java 6, and AutoIt.
      My goal is to access a given Internet Explorer browser window (`$ieTitle`), send the keystroke of "shift-control-s" for "Save As" functionality to be invoked, so that I can download a (PDF) file to a given location ($fileName).
      The code within the script `DownloadPdfFile.au3` is
      $ieTitle = $CmdLine[1] $ieControl = "AVL_AVView31" $fileName = $CmdLine[2] ControlFocus($ieTitle, "", $ieControl) ControlSend($ieTitle, "", $ieControl, "+^s") ; Save as dialog $winTitle = "Save As" ; wait for Save As window WinWait($winTitle) ; activate Save As window If Not WinActive($winTitle) Then WinActivate($winTitle) ControlFocus($winTitle,"","Edit1") ControlSetText($winTitle,"","Edit1",$fileName) Sleep(2000) ControlClick($winTitle,"","Button3") Exit 0 So I compiled it with SciTE-Lite (32-bit Version 4.4.6 , creating `DownloadPdfFile.exe`, and so within my Java code, I have
      String command = autoItExePath + " " + scriptPath + " \"" + winTitle + "\" " + directoryPath.toFile().toString() + "\\Form9Report" + sdf_ddmmmyyyy.format(new Date()) + ".pdf"; try { Process process = Runtime.getRuntime().exec(command); process.waitFor(); } catch (Exception e) { logger.error("Exception " + e.getMessage(), e); } The output would be like `C:\Program Files (x86)\AutoIt3\AutoIt3.exe C:\...\AutoItScripts\DownloadPdfFile.au3 "https://****.com/****.exe?temp_id=**** - Internet Explorer" C:\...\Report05Apr2022.pdf"` which does run without the $cmdLine successfully. 
      When executed by Java, I see in the taskbar an icon, which I right-click has "[Check] Script Paused" and "Exit".
      Questions:
      (1) How do I unpause the script?
      (2) How do I avoid having the script paused?
      Any help is appreciated.
    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By Steviep
      Hi all,
      I've been using the following code for many years for the sole purpose of tracking my app usage via Google Analytics:
      ;GOOGLE ANALYTICS $AppStatsName = @ScriptName $GA = _IECreate("https://mywebserver/apps/stats/" & $AppStatsName & ".html", 0, 0, 0, 0) The app simply calls a blank .html page on my webserver which only contains the GA tracking code.
      The $iVisible parameter is set to "0" in my case, which means the IE browser is invisible to the user.
      I noticed in Windows 11, the URL is called in the Edge browser and is not invisible.
      So, I wonder if anyone out there has any suggestions on calling a URL invisibly on any operating system?
      Thanks!
×
×
  • Create New...