bb01 Posted August 11, 2010 Share Posted August 11, 2010 Hi everyone, i used to be a member, but forgot my password & last time did anything in AutoIt was about a yr ago.. So i know this can be done.. just cant work out how.. Already spent 3days trying to work this out... So here goes.. expandcollapse popup; ********************************************************************************* ; Phone Tree Script by Graham (Do not alter, or else you could damage it)********************************************************************************* ;Warning - Taking to login Screen MsgBox(4096, "Phone Tree Script! ", "In a Few Seconds, You'll be taken to phonetreesite to Login, Please Make Sure That You Have Logged Out Of phonetreesite", 5) #include <IE.au3> ShellExecute ("iexplore.exe", "about:blank") WinWait ("Blank Page") $oIE = _IEAttach ("about:blank", "url") _IELoadWait ($oIE) _IENavigate ($oIE, "*******************") ; move iexplore.exeto top left of screen WinMove ("[CLASS:IEFrame]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:IEFrame]", "", 1, 1, 800, 600, 0 ) ;Login Screen WinWaitActive("stage1 - Logon - Windows Internet Explorer") ;Sleep(10000) ;MouseMove(10, 100) Send("{tab 26}") Send("Input Username") WinWaitActive("stage2 - Applications - Windows Internet Explorer") ;now to log into External ;MouseMove(10, 100) Send("{tab 22}") Send("{Enter}") Sleep(2000) WinWaitActive("Legal Notice - State of Nebraska .") Send("{Enter}") Sleep(2000) ;Another Login Screen To Be Filled Out - 2nd Main login at N-Focus Send("Input Username") Sleep(50000) WinWait ("Blank Page") ;Loading CSV File ready for copying and pasting. MsgBox(4096, "Phone Tree Script!", "We're Taking Controll Of The Keyboard & Mouse, Do Not Use Till Finished (Will Only Take A Few Minutes)", 5) #include <Excel.au3> ;Whilst Working on Script, The Phonetree.csv is local, however, once finished, will be put onto G Drive ;$sFilePath1 = @ScriptDir & "G:\Phone Tree\phonetree.csv" ;This file should already exist $sFilePath1 = @ScriptDir & "\phonetree.csv" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Please Make Sure That G:\Phone Tree\Phonetree.csv Is Correct") Exit EndIf WinWaitActive("Microsoft Excel - phonetree.csv") ; move phonetree.csv to top left of screen WinMove ("[CLASS:EXCEL]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:EXCEL]", "", 1, 1, 800, 600, 0 ) ;--------------- Step 1 ; Runing phonetree.csv ;WinSetOnTop("[CLASS:EXCEL]", "", 1) WinActivate("[CLASS:EXCEL]", "") WinWaitActive("Microsoft Excel - phonetree.csv") ;move to master case Number Send("{tab}") ;copys master case Number Send("^{c}") ;============ check to make sure its logged in & at the right part ;------------- Step 2 ;paste master case number WinActivate("[CLASS:IEFrame]", "") Send("^{v}") ;Send Narrative From CSV Send("{tab 5}") Send("{enter}") Send("{tab 9}") Send("{enter}") Send("{tab 3}") Send("{enter}") Send("{tab 3}") Send("{enter}") ;---------------- Step 3 WinActivate("[CLASS:EXCEL]", "") Send("{tab}") Send("^{c}") WinActivate("[CLASS:IEFrame]", "") Send("^{v}") ; Ok now to work out how to get it to go down to next line in excel & if its blank, then goto exit. otherwise if theres contents then it'll redo steps 1-3 with the next line. MsgBox(4096, "Logout", "In a Few Seconds, Nfocus & Excel will shutdown & then you can carry on working..", 5) ; Exit Phonetree.csv WinClose("Microsoft Excel - phonetree.csv") WinClose("iexplore.exe") So the problem is how to get it to go down to next line in excel & if its blank, then goto exit. otherwise if theres contents then it'll redo steps 1-3 with the next line. Sorry been so long since doing this & looked threw search & found lots of loops, but they unanswered. Thanks in Advance BB01 Link to comment Share on other sites More sharing options...
FlyinRiz Posted August 11, 2010 Share Posted August 11, 2010 (edited) Hi everyone, i used to be a member, but forgot my password & last time did anything in AutoIt was about a yr ago.. So i know this can be done.. just cant work out how.. Already spent 3days trying to work this out... So here goes.. expandcollapse popup; ********************************************************************************* ; Phone Tree Script by Graham (Do not alter, or else you could damage it)********************************************************************************* ;Warning - Taking to login Screen MsgBox(4096, "Phone Tree Script! ", "In a Few Seconds, You'll be taken to phonetreesite to Login, Please Make Sure That You Have Logged Out Of phonetreesite", 5) #include <IE.au3> ShellExecute ("iexplore.exe", "about:blank") WinWait ("Blank Page") $oIE = _IEAttach ("about:blank", "url") _IELoadWait ($oIE) _IENavigate ($oIE, "*******************") ; move iexplore.exeto top left of screen WinMove ("[CLASS:IEFrame]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:IEFrame]", "", 1, 1, 800, 600, 0 ) ;Login Screen WinWaitActive("stage1 - Logon - Windows Internet Explorer") ;Sleep(10000) ;MouseMove(10, 100) Send("{tab 26}") Send("Input Username") WinWaitActive("stage2 - Applications - Windows Internet Explorer") ;now to log into External ;MouseMove(10, 100) Send("{tab 22}") Send("{Enter}") Sleep(2000) WinWaitActive("Legal Notice - State of Nebraska .") Send("{Enter}") Sleep(2000) ;Another Login Screen To Be Filled Out - 2nd Main login at N-Focus Send("Input Username") Sleep(50000) WinWait ("Blank Page") ;Loading CSV File ready for copying and pasting. MsgBox(4096, "Phone Tree Script!", "We're Taking Controll Of The Keyboard & Mouse, Do Not Use Till Finished (Will Only Take A Few Minutes)", 5) #include <Excel.au3> ;Whilst Working on Script, The Phonetree.csv is local, however, once finished, will be put onto G Drive ;$sFilePath1 = @ScriptDir & "G:\Phone Tree\phonetree.csv" ;This file should already exist $sFilePath1 = @ScriptDir & "\phonetree.csv" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Please Make Sure That G:\Phone Tree\Phonetree.csv Is Correct") Exit EndIf WinWaitActive("Microsoft Excel - phonetree.csv") ; move phonetree.csv to top left of screen WinMove ("[CLASS:EXCEL]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:EXCEL]", "", 1, 1, 800, 600, 0 ) ;--------------- Step 1 ; Runing phonetree.csv ;WinSetOnTop("[CLASS:EXCEL]", "", 1) WinActivate("[CLASS:EXCEL]", "") WinWaitActive("Microsoft Excel - phonetree.csv") ;move to master case Number Send("{tab}") ;copys master case Number Send("^{c}") ;============ check to make sure its logged in & at the right part ;------------- Step 2 ;paste master case number WinActivate("[CLASS:IEFrame]", "") Send("^{v}") ;Send Narrative From CSV Send("{tab 5}") Send("{enter}") Send("{tab 9}") Send("{enter}") Send("{tab 3}") Send("{enter}") Send("{tab 3}") Send("{enter}") ;---------------- Step 3 WinActivate("[CLASS:EXCEL]", "") Send("{tab}") Send("^{c}") WinActivate("[CLASS:IEFrame]", "") Send("^{v}") ; Ok now to work out how to get it to go down to next line in excel & if its blank, then goto exit. otherwise if theres contents then it'll redo steps 1-3 with the next line. MsgBox(4096, "Logout", "In a Few Seconds, Nfocus & Excel will shutdown & then you can carry on working..", 5) ; Exit Phonetree.csv WinClose("Microsoft Excel - phonetree.csv") WinClose("iexplore.exe") So the problem is how to get it to go down to next line in excel & if its blank, then goto exit. otherwise if theres contents then it'll redo steps 1-3 with the next line. Sorry been so long since doing this & looked threw search & found lots of loops, but they unanswered. Thanks in Advance BB01 Try instead of using mouse and keyboard shortcuts to do it in the backend like this (this is just an excerpt)... ;read in array Local $tmpCellA, $tmpCellB, $i = 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) While $tmpCellA <> "" ReDim $avArray[$i+1][2] $avArray[$i][0] = $tmpCellA $avArray[$i][1] = $tmpCellB $avArray[0][0] += 1 $i += 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) WEnd I know this is an array, but it would still work for single cells...focus on the loop. -Aaron Edited August 11, 2010 by FlyinRiz Link to comment Share on other sites More sharing options...
Bert Posted August 11, 2010 Share Posted August 11, 2010 Several things that will make your script much more stable - 1. IE.au3 has many controls to allow you to not use "send" and sleep. for example: - use IEloadWait to wait for the page to load. - Use IEAction to handle clicking on what you need to do. - When using IEAttach, you don't need to make the window active. 2. Excel.au3 has abilities to capture entire rows or columns and put that data into an array. - Look at _ExcelReadArray to put the row or column into an array. - Attach to the instance of Excel by using _ExcelBookAttach. That way if focus is lost on the excel window, it won't break the script. If you just want a simple answer, try just using the arrow key to change cells. Once you change cells, then read the cell using _ExcelReadCell and check to see if it contains a value other than "". The Vollatran project  My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
bb01 Posted August 11, 2010 Author Share Posted August 11, 2010 ok, thanks for that guys.. its been to long since i've done this... the IEloadWait action wont work for all of this, as a lot of it is a pop out window (that has the same address, but using frames inside the webpage. the array looks good.. just trying to work out how to do that into this script.. Basicly this script needs to read username, case, number, title, message & then put them into the website.. doing that part i can get round.. BB01 Link to comment Share on other sites More sharing options...
bb01 Posted August 12, 2010 Author Share Posted August 12, 2010 Ok guys, got it all working except the looping, any ideas on how to do that.. Have cleaned it up a lot, using the menu items to get to the field, instead of tabs... Thanx in advance guys Link to comment Share on other sites More sharing options...
FlyinRiz Posted August 12, 2010 Share Posted August 12, 2010 What do you have? It might make it easier to see what is wrong. Or you could check out Do...While, While...WEnd, etc. Link to comment Share on other sites More sharing options...
bb01 Posted August 13, 2010 Author Share Posted August 13, 2010 (edited) Ok this is how it looks so far.. God i wish i'd been using Autoit more.. Last ver i used was v2 & still got some of the exe's that were compiled using Autoit 2 Quite surprising that theres so much more.. & never using arrays etc, is now really confusing, but getting there.. (Got asked to make this script to make life easier for some poor old dear, that has to use Nfocus.. ) Ok.. So yes i've used it before, but class me as a newbie again.. cos i really do feel like it now.. lol Anyway heres so far of the script, will be cleaning it up quite a bit.. but just having problems with the loop.. Added the pause & exit, so when it finishes it pauses & waits for the user to klick exit Oh also using the winactive, as Nfocus is a popup type window. Thanks in advance BB01 expandcollapse popup; ********************************************************************************* ; Phone Tree Script by Graham (Do not alter, or else you could damage it ; ********************************************************************************* ;Warning - Taking to login Screen #include <IE.au3> #include <Excel.au3> Global $Paused HotKeySet("{PAUSE}", "TogglePause") HotKeySet("{ESC}", "Terminate") MsgBox(4096, "Phone Tree Script! Created For PSI", "Before we start, If you need to pause press the Pause/Break Button, If You Wish To Exit Press ESC.. ", 10) MsgBox(4096, "Phone Tree Script! Created For PSI - Login", "In a Few Seconds, You'll be taken to Nfocus to Login, Please Make Sure That You Have Logged Out Of NFocus", 5) ShellExecute ("iexplore.exe", "about:blank") WinWait ("Blank Page") $oIE = _IEAttach ("about:blank", "url") _IELoadWait ($oIE) _IENavigate ($oIE, "http://***************************") ; move iexplore.exe to top left of screen WinMove ("[CLASS:IEFrame]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:IEFrame]", "", 1, 1, 800, 600, 0 ) ;Login Screen WinWaitActive("Citrix XenApp - Logon - Windows Internet Explorer") ;this next tabx26 will be replaced, gotta work out excatly how to get to the section easier.. Send("{tab 26}") Send("Input Username") Beep(500, 1000) WinWaitActive("Citrix XenApp - Applications - Windows Internet Explorer") ;now to log into N-Focus External Send("{tab 22}") Send("{Enter}") WinWaitActive("Legal Notice - State of Nebraska - \\Remote, 128-bit SSL/TLS.") Send("{Enter}") Sleep(2000) ;Another Login Screen To Be Filled Out - 2nd Main login at N-Focus Send("Input Username") Beep(500, 1000) WinWait ("N-FOCUS - Main Menu - \\Remote, 128-bit SSL/TLS.") Send("{tab}") ; ********************************************************************************* ; Loading CSV File ready for copying and pasting. ; ********************************************************************************* MsgBox(4096, "Phone Tree Script! Created For PSI", "We're Taking Controll Of The Keyboard & Mouse, Do Not Use Till Finished (Will Only Take A Few Minutes)", 5) ; ********************************************************************************* ; Whilst Working on Script, The Phonetree.csv is local, however, once finished, will be put onto G Drive ; ********************************************************************************* ;$sFilePath1 = @ScriptDir & "G:\Phone Tree\phonetree.csv" ;This file should already exist $sFilePath1 = @ScriptDir & "\phonetree.csv" ;This file should already exist $oExcel = _ExcelBookOpen($sFilePath1) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Please Make Sure That G:\Phone Tree\Phonetree.csv Is Correct") Exit EndIf WinWaitActive("Microsoft Excel - phonetree.csv") ; move phonetree.csv to top left of screen WinMove ("[CLASS:EXCEL]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:EXCEL]", "", 1, 1, 800, 600, 0 ) ; ********************************************************************************* ;Start the loop here ; ********************************************************************************* ; Runing phonetree.csv ;WinSetOnTop("[CLASS:EXCEL]", "", 1) WinActivate("[CLASS:EXCEL]", "") WinWaitActive("Microsoft Excel - phonetree.csv") ;move to master case Number Send("{tab}") ;copys master case Number Send("^{c}") ;============ check to make sure its logged into Nfocus & at the right part ;paste master case number into Nfocus WinActivate("N-FOCUS - Search for Master Case - \\Remote, 128-bit SSL/TLS.", "") Send("^{v}") Send("!s") ;Send Narrative From CSV 2 Nfocus WinActivate("N-FOCUS - Detail Master Case - \\Remote, 128-bit SSL/TLS.", "") Send("!g") Send("n") WinActivate("N-FOCUS - Search Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("{RIGHT}") Send("{tab 4}") Send("{DOWN 4}") Send("{SPACE}") Send("{tab}") Send("{DOWN 3}") Send("{SPACE}") Send("!o") WinActivate("N-FOCUS - Detail Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("{DOWN 3}") Send("{SPACE}") WinActivate("[CLASS:EXCEL]", "") Send("{LEFT}") Send("^{c}") WinActivate("N-FOCUS - Additional Narrative Detail - \\Remote, 128-bit SSL/TLS.", "") Send("!g") Send("!m") WinActivate("[CLASS:EXCEL]", "") Send("{tab 2}") Send("^{c}") WinActivate("N-FOCUS - Detail Narrative Text - \\Remote, 128-bit SSL/TLS.", "") Send("^{v}") Send("^{a}") WinActivate("N-FOCUS - Search Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("!c") Send("{ALTDOWN}") Send("f") Send("c") Send("{ALTUP}") ; ********************************************************************************* ; Next line in Excel ; ********************************************************************************* WinActivate("[CLASS:EXCEL]", "") Send("{DOWN}") Send("{LEFT 6}") ; ********************************************************************************* ; End the loop here ; ********************************************************************************* ; ********************************************************************************* ; If blank line in excel then exit ; ********************************************************************************* MsgBox(4096, "Logout", "In a Few Seconds, Nfocus & Excel will shutdown & then you can carry on working..", 5) ; Exit Phonetree.csv WinClose("Microsoft Excel - phonetree.csv") WinClose("iexplore.exe") MsgBox(0, "Exiting", "Press OK to Save File and Exit") ; Now we save it into the date directory; overwrite existing file if necessary _ExcelBookSaveAs($oExcel, "date\Temp.xls", "xls", 0, 1) _ExcelBookClose($oExcel) ; And finally we close out ; ********************************************************************************* ; Dont Touch this, this is the pause & exit script ; ********************************************************************************* Func TogglePause() $Paused = NOT $Paused While $Paused sleep(100) ToolTip('Script is "Paused"',0,0) MsgBox(0, "Paused", "The Script Is Now Paused, To Un-Pause Press The Pause/Break Button") WEnd ToolTip("") EndFunc Func Terminate() Exit 0 EndFunc Sorry for the double post, theres no edit button now (may be due to this firefox or was a forum hicup..). Anyway another Q. Dont know if it's possible or not.. & thats why i'm going to ask it.. But is there a way of it scanning all the lines in excel & then doing a progress bar, So that way when you've started the main part of the script, a progress bar pops up letting you know how far the script has done..? Thanks again Guys for the patience and help.. Graham PS (the last script i ever made was a simple PCA script (Program compatibility Assistant, the one that says did it install correctly.), u ran it, it then would sit in the system tray & run when the PCA kicked in, the script would then instantly shut it down, therefore not having those pesky popups in windows vista or windows 7 anymore.. I still use it, as it works a charm, & way it looped was when it ran & completed, it then would norm exit, therefor i had it just reopen itself again.. Would that type of loop work here? EG. Once its finished the main script, it shuts itself down & on shutting itself down it reopens another copy of itself? Edited August 13, 2010 by bb01 Link to comment Share on other sites More sharing options...
FlyinRiz Posted August 13, 2010 Share Posted August 13, 2010 (edited) But is there a way of it scanning all the lines in excel & then doing a progress bar, So that way when you've started the main part of the script, a progress bar pops up letting you know how far the script has done..? My above example of the Array read actually does (roughly) what you are asking. Let me post some more of the code so you can see what's happening. I'll include my loop as well which should help you with both questions... expandcollapse popup;****************************************************************************** ;------------------------------ PRE-SCRIPT ------------------------------------ ;****************************************************************************** ;%%%%%%% OPTIONS %%%%%%% Opt("CaretCoordMode", 1) ;1=absolute, 0=relative, 2=client Opt("ExpandEnvStrings", 0) ;0=don't expand, 1=do expand Opt("ExpandVarStrings", 0) ;0=don't expand, 1=do expand Opt("FtpBinaryMode", 1) ;1=binary, 0=ASCII Opt("GUICloseOnESC", 1) ;1=ESC closes, 0=ESC won't close Opt("GUICoordMode", 1) ;1=absolute, 0=relative, 2=cell Opt("GUIDataSeparatorChar","|") ;"|" is the default Opt("GUIOnEventMode", 0) ;0=disabled, 1=OnEvent mode enabled Opt("GUIResizeMode", 0) ;0=no resizing, <1024 special resizing Opt("GUIEventOptions",0) ;0=default, 1=just notification, 2=GuiCtrlRead tab index Opt("MouseClickDelay", 10) ;10 milliseconds Opt("MouseClickDownDelay", 10) ;10 milliseconds Opt("MouseClickDragDelay", 250) ;250 milliseconds Opt("MouseCoordMode", 1) ;1=absolute, 0=relative, 2=client Opt("MustDeclareVars", 0) ;0=no, 1=require pre-declare Opt("OnExitFunc","OnAutoItExit");"OnAutoItExit" called Opt("PixelCoordMode", 1) ;1=absolute, 0=relative, 2=client Opt("SendAttachMode", 0) ;0=don't attach, 1=do attach Opt("SendCapslockMode", 1) ;1=store and restore, 0=don't Opt("SendKeyDelay", 5) ;5 milliseconds Opt("SendKeyDownDelay", 1) ;1 millisecond Opt("TCPTimeout",100) ;100 milliseconds Opt("TrayAutoPause",1) ;0=no pause, 1=Pause Opt("TrayIconDebug", 1) ;0=no info, 1=debug line info Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Opt("TrayMenuMode",0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayOnEventMode",0) ;0=disable, 1=enable Opt("WinDetectHiddenText", 0) ;0=don't detect, 1=do detect Opt("WinSearchChildren", 1) ;0=no, 1=search children also Opt("WinTextMatchMode", 1) ;1=complete, 2=quick Opt("WinTitleMatchMode", 1) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase Opt("WinWaitDelay", 250) ;250 milliseconds ;%%%%%% INCLUDES %%%%%%% #include <Excel.au3> #include <File.au3> #include <Array.au3> #include <IE.au3> #include <ProgressConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <..\MyInclude\2DArray.au3> ;%%%%%% CONSTANTS %%%%%% Global Const $gc_prog_name = "Password Reset Tool" Global Const $gc_version = "v1.1" Global Const $gc_szVersion = $gc_prog_name & " - " & $gc_version ;%%%%%% GLOBAL VARS %%%%%%% Global $sFilePath = @ScriptDir & "\IDs.xls" Global $oExcel Global $oExcel_Out Global $avArray[1][2] Global $reset_type = 0 Global $upw = "Password1" Global $show_resets = 0 Global $must_change_pw = 0 Global $pw_errors = 0 Global $status_label1 Global $output_failed = 0 ;%%%%%% INIT %%%%%% ; Keep only one instance of this program running If WinExists($gc_szVersion) Then Exit ; It's already running AutoItWinSetTitle($gc_szVersion) ;Make sure input file exists in the correct format If MsgBox(68,"Please note!","Make sure that you have a file name 'IDs.xls' in the same directory as this script." & _ @CRLF & @CRLF & "It should have the following format:" & @CRLF & _ "Column A - ID numbers with letter (A & N supported)" & @CRLF & _ "Column B - Last Names (optional)" & @CRLF & _ "Column ? - All other columns are ignored" & @CRLF & @CRLF & _ "Would you like to continue?") = 7 Then Exit EndIf ;File init If FileExists($sFilePath) Then $oExcel = _ExcelBookOpen($sFilePath, 0) Else MsgBox(16,"Can not find input file","Make sure you have a file named 'IDs.xls' " & _ "in the same directory" & @CRLF & "as this script and the ID numbers are in column A.") Exit EndIf ;Delete old PWs.xls file If FileExists(@ScriptDir & "\PWs.xls") Then If Not FileDelete(@ScriptDir & "\PWs.xls") Then MsgBox(16,"Error","Cannot delete existing password file. Make sure it's not locked by another program.") Exit EndIf EndIf ;Check if file new could be opened $oExcel_Out = _ExcelBookNew(0) If @error Then $output_failed = 1 EndIf ;Check if file new could be saved If Not _ExcelBookSaveAs($oExcel_Out, @ScriptDir & "\PWs") Then $output_failed = 1 EndIf ;Initialize element count $avArray[0][0] = 0 ;****************************************************************************** ;--------------------------------- SCRIPT ------------------------------------- ;****************************************************************************** ;Create Option GUI Local $GUI_w = 400 Local $GUI_h = 255 Local $radio1, $radio2, $radio3, $input1, $submit1, $quit1, $checkbox1, $gui1 $gui1 = GUICreate($gc_szVersion, $GUI_w, $GUI_h, (@DesktopWidth-$GUI_w)/2, (@DesktopHeight-$GUI_h)/2) $radio1 = GUICtrlCreateRadio("Reset all passwords using 'Password1'", 20, 20, $GUI_w-40, 30) GUICtrlSetFont(-1, 9) $radio2 = GUICtrlCreateRadio("Reset password(s) using random passwords (Password###)", 20, 60, $GUI_w-40, 30) GUICtrlSetFont(-1, 9) If $output_failed Then GUICtrlSetState(-1, $GUI_DISABLE) EndIf $radio3 = GUICtrlCreateRadio("Reset all passwords using:", 20, 100, $GUI_w-230, 30) GUICtrlSetFont(-1, 9) GUICtrlSetState($radio1, $GUI_CHECKED) $input1 = GUICtrlCreateInput("", 210, 105, 170, 20) GUICtrlSetFont(-1, 9) GUICtrlSetState(-1,$GUI_DISABLE) $submit1 = GUICtrlCreateButton("Start",50,145, 125, 40, $BS_DEFPUSHBUTTON) $quit1 = GUICtrlCreateButton("Quit",225,145, 125, 40) $checkbox2 = GUICtrlCreateCheckbox("User must change password at next logon", 20, 200, $GUI_w-40, 20) $checkbox1 = GUICtrlCreateCheckbox("Show passwords being reset (not recommended)", 20, 225, $GUI_w-40, 20) GUISetState() ;Start GUI loop While 1 $msg = GUIGetMsg(1) Select Case $msg[0] = $submit1 ;Local $submit = MsgBox(36, "Start?", "Is everything correct?") ;If $submit = 6 Then If BitAND(GUICtrlRead($radio3), $GUI_CHECKED) = $GUI_CHECKED Then If AetnaPWCheck(GUICtrlRead($input1)) Then ExitLoop Else MsgBox(0,"Error","The password does not meet the minimum requirements. Please select another option or provide a new password.") EndIf Else ExitLoop EndIf ;EndIf Case $msg[0] = $radio1 And BitAND(GUICtrlRead($radio1), $GUI_CHECKED) = $GUI_CHECKED GUICtrlSetState($input1,$GUI_DISABLE) $reset_type = 0 Case $msg[0] = $radio2 And BitAND(GUICtrlRead($radio2), $GUI_CHECKED) = $GUI_CHECKED GUICtrlSetState($input1,$GUI_DISABLE) $reset_type = 1 Case $msg[0] = $radio3 And BitAND(GUICtrlRead($radio3), $GUI_CHECKED) = $GUI_CHECKED GUICtrlSetState($input1,$GUI_ENABLE) $reset_type = 2 Case $msg[0] = $checkbox1 If BitAND(GUICtrlRead($checkbox1), $GUI_CHECKED) = $GUI_CHECKED Then $show_resets = 1 Else $show_resets = 0 EndIf Case $msg[0] = $checkbox2 If BitAND(GUICtrlRead($checkbox2), $GUI_CHECKED) = $GUI_CHECKED Then $must_change_pw = 1 Else $must_change_pw = 0 EndIf Case $msg[0] = $GUI_EVENT_CLOSE Or $msg[0] = $quit1 Local $quit = MsgBox(36, "Quit?", "Are you sure you want to quit?") If $quit = 6 Then _ExcelBookClose($oExcel_Out, 0) _ExcelBookClose($oExcel, 0) Exit EndIf EndSelect WEnd $upw = GUICtrlRead($input1) GUIDelete($gui1) ;read in array Local $tmpCellA, $tmpCellB, $i = 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) While $tmpCellA <> "" ReDim $avArray[$i+1][2] $avArray[$i][0] = $tmpCellA $avArray[$i][1] = $tmpCellB $avArray[0][0] += 1 $i += 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) WEnd _ExcelBookClose($oExcel, 0) ;Get rid of any header information _2DArrayReverse($avArray) While 1 Local $user = $avArray[UBound($avArray)-1][0] If StringLen($user) <> 7 Or _ (StringLower(StringLeft($user, 1)) <> 'a' And _ StringLower(StringLeft($user, 1)) <> 'n') Or _ Not StringIsDigit(StringRight($user, 6)) Then ReDim $avArray[UBound($avArray)-1][2] Else ExitLoop EndIf WEnd _2DArrayReverse($avArray) ;Create Status GUI Local $numUsers = UBound($avArray) $gui1 = GUICreate("Reset Progress", 300, 100, (@DesktopWidth-300)/2, @DesktopHeight-150-100, BitOR($WS_POPUP,$WS_DLGFRAME), BitOR($WS_EX_CLIENTEDGE,$WS_EX_TOPMOST)) $status_label1 = GUICtrlCreateLabel("Current User (none) - ", 20, 20, 260) GUICtrlSetFont(-1,8.5,800) Local $percent = 0, $numComplete = 0 Local $gui_progress_ctrl = GUICtrlCreateProgress(20, 55, 260, 20, $PBS_SMOOTH ) Local $gui_label1_ctrl = GUICtrlCreateLabel($numComplete & " of " & $numUsers & " Complete", 30, 80, 110, 20) Local $gui_label2_ctrl = GUICtrlCreateLabel($percent & " percent", 210, 80, 110, 20) GUISetState() ;Write Header info for output excel sheet _ExcelWriteCell($oExcel_Out, "UID", 1, 1) _ExcelWriteCell($oExcel_Out, "PW", 1, 2) _ExcelWriteCell($oExcel_Out, "LastName", 1, 3) $i = 2 ;Row number (to get array number, subtract 2) ;Start resetting passwords While $i <= (UBound($avArray) + 1) GUICtrlSetData($status_label1, "Current User (" & $avArray[$i-2][0] & ") - Starting...") ;Reset Password $ePW = SetPassword($avArray[$i-2][0]) _ExcelWriteCell($oExcel_Out, StringUpper($avArray[$i-2][0]), $i, 1) _ExcelWriteCell($oExcel_Out, $ePW, $i, 2) _ExcelWriteCell($oExcel_Out, $avArray[$i-2][1], $i, 3) $i += 1 $numComplete += 1 ;Update GUI $percent = Round(100 * ($numComplete/$numUsers),0) GUICtrlSetData($gui_label1_ctrl,$numComplete & " of " & $numUsers & " Complete") GUICtrlSetData($gui_progress_ctrl, $percent) GUICtrlSetData($gui_label2_ctrl, $percent & " percent") WEnd _ExcelBookClose($oExcel_Out) GUIDelete($gui1) MsgBox(0,"Summary","The tool finished with " & $pw_errors & " password error(s).") ;End of program Exit Look for the following line for the loop ;Start resetting passwords EDIT: Obviously, you'll change the password reset to what you actually want to do with the data... Enjoy! -Aaron Edited August 13, 2010 by FlyinRiz Link to comment Share on other sites More sharing options...
bb01 Posted August 13, 2010 Author Share Posted August 13, 2010 (edited) ah so its the While $i <= (UBound($avArray) + 1) i've been doing $i <($avArray) ok, can see where i was really going wrong.. Thanx Aaron. Will let you know how it goes on monday, as thats when back in Work. Btw was looking at your script & gotta admit, that is one hell of a script.. Think i'm gonna love AutoIt 3.. seems like theres a lot more that you can do now with it.. Edited August 13, 2010 by bb01 Link to comment Share on other sites More sharing options...
FlyinRiz Posted August 13, 2010 Share Posted August 13, 2010 ah so its the While $i <= (UBound($avArray) + 1)i've been doing $i <($avArray)ok, can see where i was really going wrong.. Thanx Aaron. Will let you know how it goes on monday, as thats when back in Work.Yea the big thing with comparisons is to make sure the datatypes are the same like why 1 = "1" and True = "True" are both false. Those are just simple examples...Enjoy the weekend.-Aaron Link to comment Share on other sites More sharing options...
FlyinRiz Posted August 13, 2010 Share Posted August 13, 2010 Btw was looking at your script & gotta admit, that is one hell of a script.. Think i'm gonna love AutoIt 3.. seems like theres a lot more that you can do now with it..Thanks, but I wouldn't even call it that complicated. I was able to crank that out in a day or so, but I'm no where close to the ability of most of the MVPs and Mods. I love AutoIt (I've been using it for 5+ years now) and I'd have to agree that it's amazing what you can, and will be able to, do with it...-Aaron Link to comment Share on other sites More sharing options...
bb01 Posted August 17, 2010 Author Share Posted August 17, 2010 Hiya, got another q, how can i get the excel to save with the date in it.. eg MsgBox( 4096, "The file will be saved now as DATE-Phonetree.csv", _DateTimeFormat( _NowCalc(),2)) no matter what i do, it just does not save the date in the name.. Thanks in advance Graham Link to comment Share on other sites More sharing options...
bb01 Posted August 17, 2010 Author Share Posted August 17, 2010 Ok this is what we have so far.. Not sure if it'll work right or not (Hope it does, or else gotta go back & redo it all), spending to much time trying to get this working & with this damn sinus infection, have got headache all the time.. But Thanks do goto FlyinRiz for all there help... If you can just scan over this & see if its right.. Thanks in Advance Graham expandcollapse popup; ********************************************************************************************* ; Phone Tree Script Do not alter, or else you could damage it (created by BB01 & lots of help from ; FlyinRiz, This script was designed & made for PSI. ; *************************************************************************************************** ;Warning - Taking to login Screen #include <IE.au3> #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <ProgressConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <GuiConstantsEx.au3> #include <Date.au3> #include <WindowsConstants.au3> ;%%%%%% CONSTANTS %%%%%% Global Const $gc_prog_name = "Phone Tree Script! Created For PSI" Global Const $gc_version = "v0.1" Global Const $gc_szVersion = $gc_prog_name & " - " & $gc_version ;%%%%%% GLOBAL VARS %%%%%%% ;This file should already exist Global $sFilePath = @ScriptDir & "\phonetree.csv" Global $Paused HotKeySet("{PAUSE}", "TogglePause") HotKeySet("{ESC}", "Terminate") ; Old Message Box ;MsgBox(4096, "Phone Tree Script! Created For PSI", "Before we start, If you need to pause press the Pause/Break Button, If You Wish To Exit Press ESC.. ", 10) ;MsgBox(4096, "Phone Tree Script! Created For PSI - Login", "In a Few Seconds, You'll be taken to Nfocus to Login, Please Make Sure That You Have Logged Out Of NFocus", 5) ;%%%%%% INIT %%%%%% ; Keep only one instance of this program running If WinExists($gc_szVersion) Then Exit ; It's already running AutoItWinSetTitle($gc_szVersion) ;Make sure input file exists in the correct format MsgBox(68,"Phone Tree Script! Created For PSI - Please Note:-","Before we start, If you need to pause press the Pause/Break Button, If You Wish To Exit Press ESC.. " & _ @CRLF & @CRLF & "In a Few Seconds, You'll be taken to Nfocus to Login" & @CRLF & _ "Please Make Sure That You Have Logged Out Of NFocus" & @CRLF & _ @CRLF & @CRLF & "Make sure that you have a file name 'phonetree.csv' in the same directory as this script." & @CRLF & _ @CRLF & @CRLF & "This Script Will Continue In 30 Seconds, If Nothing Is Pressed" & @CRLF & _ "Would you like to continue?", 30) ;File init If FileExists($sFilePath) Then $oExcel = _ExcelBookOpen($sFilePath) Else MsgBox(16,"Can not find input file","Make sure you have a file named 'phonetree.csv' " & _ "in the same directory" & @CRLF & "as this script and the Name (Last, First) are in column A.") Exit EndIf ShellExecute ("iexplore.exe", "about:blank") WinWait ("Blank Page") $oIE = _IEAttach ("about:blank", "url") _IELoadWait ($oIE) _IENavigate ($oIE, "https://nfuse-dhhs.ne.gov/Citrix/XenApp/auth/login.aspx") ; move iexplore.exe to top left of screen WinMove ("[CLASS:IEFrame]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:IEFrame]", "", 1, 1, 800, 600, 0 ) ;Login Screen WinWaitActive ("Citrix XenApp - Logon - Windows Internet Explorer") Send("{tab 11}") ;was 26 Send("Input Username") Beep(500, 1000) WinWaitActive("Citrix XenApp - Applications - Windows Internet Explorer") ;now to log into N-Focus External ;MouseMove(10, 100) Send("{tab 22}") Send("{Enter}") ;Sleep(2000) WinWaitActive("Legal Notice - State of Nebraska - \\Remote, 128-bit SSL/TLS.") Send("{Enter}") ;Sleep(2000) ;Another Login Screen To Be Filled Out - 2nd Main login at N-Focus Send("Input Username") Beep(500, 1000) WinWait ("N-FOCUS - Main Menu - \\Remote, 128-bit SSL/TLS.") Send("{tab}") ; ********************************************************************************* ; Loading CSV File ready for copying and pasting. ; ********************************************************************************* MsgBox(4096, "Phone Tree Script! Created For PSI", "We're Taking Controll Of The Keyboard & Mouse, Do Not Use Till Finished (Will Only Take A Few Minutes)", 5) WinActivate("Microsoft Excel - phonetree.csv") ; move phonetree.csv to top left of screen WinMove ("[CLASS:EXCEL]", "", 2, 2, 800, 600, 0 ) WinMove ("[CLASS:EXCEL]", "", 1, 1, 800, 600, 0 ) ; If the loop fails, put ; Runing phonetree.csv here ; Thanks go out to FlyinRiz for all there help, Hopefully this is working.. ; ********************************************************************************* ; Next line in Excel ; ********************************************************************************* $avArray[0][0] = 0 WinActivate("[CLASS:EXCEL]", "") ;read in array Local $tmpCellA, $tmpCellB, $i = 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) While $tmpCellA <> "" ReDim $avArray[$i+1][2] $avArray[$i][0] = $tmpCellA $avArray[$i][1] = $tmpCellB $avArray[0][0] += 1 $i += 1 $tmpCellA = _ExcelReadCell($oExcel, $i, 1) $tmpCellB = _ExcelReadCell($oExcel, $i, 2) WEnd While $i <= (UBound($avArray) + 1) ; ********************************************************************************* ;Start the loop here ; ********************************************************************************* ; Runing phonetree.csv ;WinSetOnTop("[CLASS:EXCEL]", "", 1) WinActivate("[CLASS:EXCEL]", "") WinWaitActive("Microsoft Excel - phonetree.csv") ;move to master case Number Send("{tab}") ;copys master case Number Send("^{c}") ;============ check to make sure its logged into Nfocus & at the right part ;paste master case number into Nfocus WinActivate("N-FOCUS - Search for Master Case - \\Remote, 128-bit SSL/TLS.", "") Send("^{v}") Send("!s") ;Send Narrative From CSV 2 Nfocus WinActivate("N-FOCUS - Detail Master Case - \\Remote, 128-bit SSL/TLS.", "") Send("!g") Send("n") WinActivate("N-FOCUS - Search Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("{RIGHT}") Send("{tab 4}") Send("{DOWN 4}") Send("{SPACE}") Send("{tab}") Send("{DOWN 3}") Send("{SPACE}") Send("!o") WinActivate("N-FOCUS - Detail Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("{DOWN 3}") Send("{SPACE}") WinActivate("[CLASS:EXCEL]", "") Send("{LEFT}") Send("^{c}") WinActivate("N-FOCUS - Additional Narrative Detail - \\Remote, 128-bit SSL/TLS.", "") Send("!g") Send("!m") WinActivate("[CLASS:EXCEL]", "") Send("{tab 2}") Send("^{c}") WinActivate("N-FOCUS - Detail Narrative Text - \\Remote, 128-bit SSL/TLS.", "") Send("^{v}") Send("^{a}") WinActivate("N-FOCUS - Search Narrative - \\Remote, 128-bit SSL/TLS.", "") Send("!c") Send("{ALTDOWN}") Send("f") Send("c") Send("{ALTUP}") WEnd ; If the loop part fails, use below to go down 1 line in excel for time being & then copy & paste inside loop to below.. Yes its a mess, but if its only way to get working, then so be.. only got a few days left.. 8-16-10 ;Send("{DOWN}") ;Send("{HOME}") ; ********************************************************************************* ; End the loop here ; ********************************************************************************* ; ********************************************************************************* ; If blank line in excel then exit ; ********************************************************************************* MsgBox(4096, "Logout", "In a Few Seconds, Nfocus & Excel will shutdown & then you can carry on working..", 5) ; Exit Phonetree.csv WinClose("iexplore.exe") MsgBox(0, "Exiting", "Press OK to Save File and Exit") ; Now we save it into the directory; & create phonetree.cv ; Once worked out how to save the date into the file name, replace DATE-Phonetree.xls with the formular _ExcelBookSaveAs($oExcel, "DATE-Phonetree.xls", "xls", 0, 1) _ExcelBookClose($oExcel) ; And finally we close out WinClose("Microsoft Excel - phonetree.csv") ; ********************************************************************************* ; Dont Touch this, this is the pause & exit script ; ********************************************************************************* Func TogglePause() $Paused = NOT $Paused While $Paused sleep(100) ToolTip('Script is "Paused"',0,0) MsgBox(68, "Paused", "The Script Is Now Paused, To Un-Pause Press The Pause/Break Button") WEnd ToolTip("") EndFunc Func Terminate() Exit 0 EndFunc Link to comment Share on other sites More sharing options...
bb01 Posted August 24, 2010 Author Share Posted August 24, 2010 ok, well the script did every thing except the loop.. so not sure what i've done wrong.. trying to find where i've gone wrong with it.. Looks like its not working because theres no section at the end of the Excel (WinActivate("N-FOCUS - Search Narrative - \\Remote, 128-bit SSL/TLS.", "")) to tell it to go to beging of the loop.. Can some one have a look over it & tell me where or what i did wrong.. Thanks in advance Graham Link to comment Share on other sites More sharing options...
bb01 Posted August 24, 2010 Author Share Posted August 24, 2010 oh on a side note, how can i get it to save a backup copy once finished to 8-24-10.zip, so its always using the current date & saves it to a zip file.. that way if we need to ever go back & check the files, we can find it easily. Thanks in Advance 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