Jump to content
XinYoung

Do...Until Excel sheet has no more data?

Recommended Posts

XinYoung
Posted (edited)

Why hello there! :D

I have a script reading an Excel sheet (currently only looking at A1), copying the data in that cell, and then searching for it in a Website's search tool. Then it does some stuff if it actually finds something.

After that, I need it to go back to the Excel sheet, write "yes" into column B if the search was good -- "no" if no results were found, then go down to A2 and do the whole thing all over again until column A has nothing left.

:frantics: I really suck at loops and have a hard time grasping how it works. Any help or guidance is really appreciated!

 

SearchCourse()

;~  Now that were logged in, navigate to the course search page.
Func SearchCourse()
    _IENavigate($oIE, $urlBBCourseSearch)

;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~  Open the selected Excel file
    Local $oAppl = _Excel_Open()
    Local $oWorkbook = _Excel_BookOpen($oAppl, $ChosenFileName, Default, Default, True)

;~ Copy whats in the first cell (A1)
    _Excel_RangeCopyPaste($oWorkbook.Worksheets(1), "A1")
    Global $WhatsCopied = ClipGet()

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $WhatsCopied)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    Global $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
    
;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
    EndIf
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)

;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

 

Edited by XinYoung
Fixed some comments in the code

Share this post


Link to post
Share on other sites
Davidowicza
Posted (edited)

If you do this after you make your workbook object, you will get the size of the excel workbook (know what the last line of the excel file will be).

#include <array.au3>

$array = _Excel_RangeRead($oWorkbook)
$size = Ubound($array)

Give adding in a loop a try now that you know the size. If you are still struggling with it, let us know!

Edited by Davidowicza
Grammar
  • Like 1

Share this post


Link to post
Share on other sites
JLogan3o13

@XinYoung You can do this easily with _Excel_RangeWrite. Something like this should get you started:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test1.xlsx")
Local $oRange = _Excel_RangeRead($oWorkbook, Default, Default)

For $a = 1 To UBound($oRange)
    ;Do other Stuff
    _Excel_RangeWrite($oWorkbook, Default, "This is cell B" & $a, "B" & $a)
Next

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

If your columns have headers, you'll have to adjust by one.

  • Like 1

√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
Subz
Posted (edited)

It would be faster to just grab all of A:A into an array and loop through the results and create a second array to hold the "Yes/No" here is an example untested:

#include <Array.au3>
#include <Excel.au3>
Global $sChosenFileName = "Filename.xlsx"
Global $aBBTableData
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, $sChosenFileName)
;~ Get all used cells in column A:A
Global $aSearchItems = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array 
Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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
    $aSearchResult[$i] = SearchCourse($aSearchItems[$i])
Next
_ArrayDisplay($aSearchItems, "Search Items")
_ArrayDisplay($aSearchResult, "Search Results")
;~ Paste the results back to Excel in column B1
_Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")

Func SearchCourse($_sSearch)
    _IENavigate($oIE, $urlBBCourseSearch)

;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearch)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)
    
;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
        $sResult = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)
;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

Edited by Subz
  • Like 1

Share this post


Link to post
Share on other sites
XinYoung

OK, I tried implementing it but I'm getting an error: SearchCourse() called by a previous line with 0 arg(s)

Because of the way I call upon the func...?

 

OpenExcel()

Func OpenExcel()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, Default, True)
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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
        $aSearchResult[$i] = SearchCourse($aSearchItems[$i])
    Next
    _ArrayDisplay($aSearchItems, "Search Items")
    _ArrayDisplay($aSearchResult, "Search Results")
;~ Paste the results back to Excel in column B1
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")

    SearchCourse()

EndFunc   ;==>OpenExcel


;~  Now that were logged in, navigate to the course search page.
Func SearchCourse($aSearchResult)
    _IENavigate($oIE, $urlBBCourseSearch)

    ;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $aSearchResult)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    DeleteCourseBegin()

EndFunc   ;==>SearchCourse



;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)

;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
        $sResult = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

;~ The course search was successful. Delete the course now.
Func DeleteCourseNow()
;~ Click the checkbox located beside the course that has been found.
    Local $CheckBox = _IEGetObjByName($oIE, "ckbox")
    _IEAction($CheckBox, "click")
    _IELoadWait($oIE)

;~ Click the Delete button.
    _IELinkGetCollection($oIE)
    Local $oLinks = _IELinkGetCollection($oIE)
    For $oLink In $oLinks
        If $oLink.href = "javascript:validateRemove();" Then
            _IEAction($oLink, "click")
            ExitLoop
        EndIf
    Next

;~  Click the "Delete course, including all of its files" radio button.
    Local $RadioButton = _IEGetObjById($oIE, "removeAllFiles_t")
    _IEAction($RadioButton, "click")
    _IELoadWait($oIE)

;~ Click the "Submit" button
    Local $Submit = _IEGetObjByName($oIE, "bottom_Submit")
    _IEAction($Submit, "click")
    _IELoadWait($oIE)
;~  Now go back to the Excel sheet and search for the next one....?

EndFunc   ;==>DeleteCourseNow

 

So, is this being caused by how I have everything residing within a Func? Because at the end of each func, I call upon another one. Is that not a good way to go about it? :huh2:

I can't do something like Func SearchCourse($aSearchResult) this way?

 

Share this post


Link to post
Share on other sites
Subz

Can you try changing:

SearchCourse($aSearchResult)

To

SearchCourse($_sSearchResult)

And change:

_IEFormElementSetValue($oSearchString, $aSearchResult)

To

_IEFormElementSetValue($oSearchString, $_sSearchResult)

 

  • Like 1

Share this post


Link to post
Share on other sites
XinYoung

OK, but still getting the same error (SearchCourse() called by a previous line with 0 arg(s)) because of...

$aSearchResult[$i] = SearchCourse($aSearchItems[$i])

^located in the OpenExcel() func :think:

Share this post


Link to post
Share on other sites
Subz

No The error means you've called SearchCourse without any parameter, so look for the following and remove it since its been called within the loop.

SearchCourse()

 

  • Like 1

Share this post


Link to post
Share on other sites
XinYoung

OhHhHHhHHHh my bad! I get it. It was already there and I put it in again with 0 arg. amirite?

Alright that error is gone! :lol:

Now I'm getting Statement cannot be just an expression regarding $aSearchResult in the Else...

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)

;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
        $sResult = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

 

Share this post


Link to post
Share on other sites
Subz

Just add Local $sResult to the top of the function

Func DeleteCourseBegin()
Local $sResult
...
EndFunc

 

  • Like 1

Share this post


Link to post
Share on other sites
XinYoung

Hmmm, still getting the same error

;~ OK, we logged in and we searched for a course. Lets delete it!
Func DeleteCourseBegin()
    Local $sResult
    $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0)

;~  If the course was not found, do this.
    If $iSearchIndex = -1 Then
        MsgBox(4096, "Search Error", "Item not found")
        $sResult = "No"
;~  Now go back to the Excel sheet and search for the next one....?

;~ If the course was found, begin the deletion process.
    Else
        $aSearchResult
        MsgBox(4096, "Search Success", $aBBTableData[$iSearchIndex][0] & " = " & $aBBTableData[$iSearchIndex][1])
        DeleteCourseNow()
        $sResult = "Yes"
    EndIf
    Return $sResult
EndFunc   ;==>DeleteCourseBegin

image.thumb.png.524dd4e53ca41f00296096e748043913.png

Share this post


Link to post
Share on other sites
Subz

Sorry my bad, just delete that variable its not required.

  • Haha 1

Share this post


Link to post
Share on other sites
XinYoung

its working now :dance:

This is so cool. It doing the thing. :yes:

And when there's no more it puts a "0" into column B.

THANK YOU!!!!!!1

 

Share this post


Link to post
Share on other sites
Subz

"And when there's no more it puts a "0" into column B."

Do you mean it does have Yes or No in column B?  What does _ArrayDisplay($aSearchResult, "Search Results") return?

Share this post


Link to post
Share on other sites
XinYoung
Posted (edited)

This

image.png.2c285ca37891a34bc051f37522921fea.png

It doesn't enter "Yes" or "No" at any point. It only throws a bunch of zero's into column B at the very end.

 

Edited by XinYoung
Elaboration

Share this post


Link to post
Share on other sites
Subz

Sorry a bit difficult to write code without testing, but you should be able to fix this by changing the SearchCourse(..) function, adding Return DeleteCourseBegin

;~  Now that were logged in, navigate to the course search page.
Func SearchCourse($_sSearchItem)
    _IENavigate($oIE, $urlBBCourseSearch)

    ;~  Change the search criteria to "Course ID"
    _bbCourseSearchCategoryChange("Course ID")

;~ Paste whats copied into the search text box and click submit
    Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch")
    Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText")
    _IEFormElementSetValue($oSearchString, $_sSearchItem)
    _IEFormSubmit($oForm)

;~ Lets see what we got from the search
    Local $oBBTable = _IETableGetCollection($oIE, 2)
    $aBBTableData = _IETableWriteToArray($oBBTable)
    _ArrayDisplay($aBBTableData)

    Return DeleteCourseBegin()
EndFunc   ;==>SearchCourse

 

  • Like 1

Share this post


Link to post
Share on other sites
XinYoung

Yuuup that did it. Thank you thank you! :)

Share this post


Link to post
Share on other sites
XinYoung

Hey, um, what do I change here to make it always target a sheet named "DeleteCourses" in my Excel workbook? Sometimes the file was saved on a different sheet, which causes the wrong sheet to be Active.

Func OpenExcel()
    Global $aBBTableData
    Global $oExcel = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, Default, True)
;~ Get all used cells in column A:A
    Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
;~ Duplicate the $aSearchItems Array
    Global $aSearchResult = $aSearchItems
;~ Loop through $aSearchItems
;~ Since Excel_RangeRead returns a 0-based Array and since we only captured one column it is a 1 dimension array
;~ 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
        $aSearchResult[$i] = SearchCourse($aSearchItems[$i])
    Next
    _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "B1")
    Finished()
EndFunc   ;==>OpenExcel

Thanks again

Share this post


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

what do I change here to make it always target a sheet named "DeleteCourses"

 

Assuming that the sheet actually exists, the following line will make sure that the sheet is active:

$oExcel.Sheets("DeleteCourses").Activate

 

  • Like 1

Share this post


Link to post
Share on other sites
Subz
Posted (edited)

Or

_Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("DeleteCourses").Usedrange.Columns("A:A"))

 

Edited by Subz
  • Like 1

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

  • Similar Content

    • XinYoung
      By XinYoung
      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  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!
       
    • SlackerAl
      By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
    • Morphice
      By Morphice
      Hello , 
      I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. 
      * workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. 
      ; = comments and reminders 
      #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include<excel.au3> #include<Array.au3> Global Const $PatientLookupX = 320 Global Const $PatientLookupY = 64 ; down 1 and enter Global Const $PatientTextBoxX = 410 Global Const $PatientTextBoxY = 217 ; click , Ctrl + V , Enter Global Const $PHMhubX = 512 Global Const $PHMhubY = 613 ;click down 1 enter button Global Const $HealthRiskAssesmetX = 40 Global Const $HealthRiskAssesmetY = 162 Global Const $AddnewAssesmentX = 168 Global Const $AddnewAssesmentY = 98 Global Const $SelectAssesmentX = 342 Global Const $SelectAssesmentY = 98 ; Down 7 and enter Risk score new Global Const $EmptyAnswerBarX = 465 Global Const $EmptyAnswerBarY = 145 Global Const $LowriskpreventionX = 716 Global Const $LowriskpreventionY = 324 Global Const $MediumriskPreventionX = 716 Global Const $MediumriskPreventionY = 352 Global Const $HighriskPreventionX = 716 Global Const $HighriskPreventionY = 377 Global Const $CatatrosphicPreventionX = 714 Global Const $CatatrosphicPreventionY = 399 Global Const $ClosebuttonX = 1167 Global Const $ClosebuttonY = 666 Global Const $SaveRiskButtonX = 1161 Global Const $SaveRiskButtonY = 692 Global Const $ExitCPScreenX = 1339 Global Const $ExitCPScreenY = 8 Global Const $ExitpatientHubX = 1000 Global Const $ExitpatientHubY = 79 Global Const $sleepMod = 2 Global Const $sleepVal = 5000*$sleepMod Global Const $sleepLow = 200*$sleepMod Global Const $sleepMed = 1000*$sleepMod Global Const $sleepHigh = 3500*$sleepMod ;Function Open excel , read account number in column A ;------------------------------------------------------------------------------------------------------------------------------------------------------- HotKeySet("{ESC}","stopbaby") Func _WinWaitActivate($title,$text,$timeout=0) $hWnd = WinWait($title,$text,$timeout) If Not WinActive($title,$text) Then WinActivate($title,$text) WinWaitActive($title,$text,$timeout) EndFunc $i=0 While $i <=2 $i = $i+1 Local $Open_excel = _Excel_Open() Local $File_path = "D:\AutoIT\Risk_Test.xlsx" Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path) WinActivate($Open_workbook) Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i) _Excel_Close($Open_excel,False) WEnd ;--------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoSearch() WinActivate(eClinicalWorks (Garcia,Erick) Sleep($sleepMed) MouseClick("",693,77) Send("!p") ; shortcut for patient menu Send("{DOWN}") ; down 1 send ("{Enter}") ; patient lookup Sleep($sleepMed) ;paste account number How would I do this??? Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub Sleep($sleepMed) Next NavtoPHMHub() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoPHMHub() MouseClick("",$PHMhubX,$PHMhubY) Sleep($sleepLow) Send("{DOWN}") Send("{ENTER}") ;takes you to Care Plan HUB Next NavtoRiskScore() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoRiskScore() MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment Sleep($sleepLow) MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment Sleep($sleepLow) MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab Sleep($sleepLow) Send("{DOWN 7}") Send("{ENTER}") Sleep($sleepLow) MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar Sleep($sleepLow) MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6 Next NavtoNextPatient() Func NavtoNextPatient MouseClick("",$ClosebuttonX,$ClosebuttonY) MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY) MouseClick("",$ExitCPScreenX,$ExitCPScreenY) MouseClick("",$ExitpatientHubX,$ExitpatientHubY) EndFunc ;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps ;--------------------------------------------------------------------------------------------------------------------------------------------------------- Func stopbaby() exit EndFunc Best Regards,
      Morphice
      steps for program.docx
    • SlackerAl
      By SlackerAl
      Running the first example of _Excel_RangeFind from the help file (note I have added the version MsgBox and changed the path to _Excel1.xls)
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> MsgBox(0, "Version", @AutoItVersion) ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Find all occurrences of value "37000" (partial match) ; ***************************************************************************** Local $aResult = _Excel_RangeFind($oWorkbook, "37000") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I have also created a simple new Excel file "_Excel1.xls" in my script area and added "37000" to one cell.
      I generate the error:

      I arrived at this after generating the same error within my code. I'm using AutoIt version 3.3.14.2
      Any thoughts?
       
       
       
×