Help with processing an excel sheet & looping

bb01 · 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..

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

FlyinRiz · 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..

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

Bert · 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 "".

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

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

FlyinRiz · 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.

bb01 · August 13, 2010

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

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

FlyinRiz · August 13, 2010

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

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

bb01 · 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.

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

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

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

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

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

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

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

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

Help with processing an excel sheet & looping

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members