Excel

[veerendra]

Hi i want to pass the values (Pass/Fail) into excel sheet from autoit script using if conditions. Please Help me out..

[Bert]

Please help us - what code have you tried? Please post your code.

[veerendra]

expandcollapse popup

#include <Excel.au3>
#include <debug.au3>
_DebugSetup(Default, False, 4, @ScriptDir & '\Log.txt') 
_DebugOut('TEST')


Local $sFilePath = @ScriptDir & "\Test.xls" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath, 1, True); * $fVisible = 1;  Can be 0 after testing is done & code works as expected.......

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 - Shame on you!")
    Exit
EndIf


Opt("MouseCoordMode",0)
Global $first, $input



#region ---Au3Recorder generated code Start (v3.3.7.0)  ---

#region --- Internal functions Au3Recorder Start ---

Func _Au3RecordSetup()

Opt('WinWaitDelay',100)
Opt('WinDetectHiddenText',1)
Opt('MouseCoordMode',0)
EndFunc

Func _WinWaitActivate($title, $text, $timeout = 0)
    WinWait($title, $text, $timeout)
    If Not WinActive($title, $text) Then WinActivate($title, $text)
    WinWaitActive($title, $text, $timeout)
EndFunc

#endregion --- Internal functions Au3Recorder End ---



Run('"C:\Program Files (x86)\Fortinet\FortiClient\FortiClient.exe"')
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
$input=MsgBox(4,"sucess","Testing Started You want to Continue")
  If $input = 6 Then
MouseClick("left",91,249,1)
Sleep(1000)
MouseClick("left",377,470,1)
Sleep(1000)
MouseClick("left",490,465,1)
Sleep(1000)
MouseClick("left",363,241,1)
Sleep(1000)
MouseClick("left",345,342,1)
Sleep(1000)
_WinWaitActivate("Add Parental Control Exclusion","")
Sleep(1000)
MouseClick("left",256,71,1)
Sleep(1000)
Send("www.yahoo.com")
Sleep(1000)
MouseClick("left",226,134,1)
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",274,473,1)
Else
    MsgBox(0, "quiting", "you choose to quit")
    MouseClick("left",610,-1,1)
    Exit;
EndIf




Run('"C:\Program Files (x86)\Mozilla Firefox\firefox.exe"')
sleep(1000)
_WinWaitActivate("Google - Mozilla Firefox","")
sleep(1000)
MouseClick("left",269,90,1)
sleep(1000)
Send("{BACKSPACE}www.yahoo.com{DEL}{ENTER}")
sleep(1000)
If WinWait("Yahoo Canada - Mozilla Firefox","",3) Then
    MsgBox(0,"Error", "Check the Settings in FortiClient")
    Exit;
EndIf
_WinWaitActivate("Web Page Blocked!  - Mozilla Firefox","")
MouseClick("left",273,62,1)
sleep(1000)
_WinWaitActivate("New Tab - Mozilla Firefox","")
sleep(1000)
MouseClick("left",306,92,1)
sleep(1000)
Send("www.google.ca{ENTER}")
sleep(1000)
MouseClick("left",1256,15,1)
Sleep(1000)
_WinWaitActivate("Confirm close","")
Sleep(1000)
MouseClick("left",178,115,1)






_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",445,789,1)
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",489,465,1)
Sleep(1000)
MouseClick("left",351,169,1)
Sleep(1000)
MouseClick("left",385,347,1)
Sleep(1000)
_WinWaitActivate("Edit Parental Control Exclusion","")
Sleep(1000)
MouseClick("left",290,89,1)
Sleep(1000)
MouseClick("left",211,121,1)
Sleep(1000)
MouseClick("left",237,143,1)
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",275,472,1)



Run('"C:\Program Files (x86)\Mozilla Firefox\firefox.exe"')
Sleep(1000)
_WinWaitActivate("Google - Mozilla Firefox","")
Sleep(1000)
MouseClick("left",236,95,1)
Sleep(1000)
Send("{BACKSPACE}www.yahoo.com{DEL}{ENTER}")
Sleep(1000)
_WinWaitActivate("Yahoo Canada - Mozilla Firefox","")
Sleep(1000)
MouseClick("left",275,59,1)
Sleep(1000)
_WinWaitActivate("New Tab - Mozilla Firefox","")
Sleep(1000)
MouseClick("left",297,90,1)
Sleep(1000)
Send("www.yahoo.com{DEL}{ENTER}")
Sleep(1000)
MouseClick("left",1256,15,1)
Sleep(1000)
_WinWaitActivate("Confirm close","")
Sleep(1000)
MouseClick("left",178,115,1)



_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",446,793,1)
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",106,249,1)
Sleep(1000)
MouseClick("left",386,470,1)
Sleep(1000)
MouseClick("left",450,780,1)
Sleep(1000)
_WinWaitActivate("FortiClient","")
Sleep(1000)
MouseClick("left",463,466,1)
Sleep(1000)
MouseClick("left",350,169,1)
Sleep(1000)
MouseClick("left",366,344,1)
Sleep(1000)
MouseClick("left",262,471,1)
Sleep(1000)
MouseClick("left",610,-1,1)


#endregion --- Au3Recorder generated code End ---

This is my code, here if the condition is passed i want to send the Pass value in to Excel sheet.

[Bert]

ok, I see you are doing the following in your script:
1. Open an excel file
2. run an install to configure FortiClient.exe
3. Run Firefox to test

It looks like you repeat steps 2 and 3.

For the excel part look at _ExcelWriteCell. The example given in the help file show show you what to do.

For the other part of your script - look at using controlsend instead of send. MUCH more stable and if your window loses focus, your script won't break.

[veerendra]

I am testing FortiClient Parental Controls. I am enabling the Feature and adding an website to block and testing in firefox. Now i want if firefox tab was correct or not, if correct i want pass the value in to excel.

[Bert]

Few suggestions:

You can do this to test in Firefox to make things simpler:

Run('"C:\Program Files (x86)\Mozilla Firefox\firefox.exe http://www.yahoo.com"')

This would go straight to the page and no clicks are required.

As far as FortiCLient, I would use controlsend to interact with the controls. Also, you can use command line with FortiClient.exe and you could make your script MUCH simpler.

Edited October 22, 2013 by YogiBear

[Automationuser]

Hi veerendra,

From your post I see you are trying to automate a testcase.

this is what I follow to write testcase result.

If $ExpResult="Correct" then
    _WriteResult("Pass","TC_No_01")
Else
    _WriteResult("Fail","TC_No_01")
EndIf

;==========================================================================================================
;Function Name : _WriteResult
;Functionality : This function will write the result to Excel
;Input         : TestCase Status, TestCase No.
;Output        : -
;==========================================================================================================

Func _WriteResult($TCStatus,$TCNo)
    
    ; Write the test result to Excel Sheet
    if FileExists($excelFile) Then
        $oExcel.Application.ActiveSheet.Columns($TCIDIndex).Select          ; Select the Testcase ID column
        $TCAddress=$oExcel.Application.Selection.Find($TCNo).Address        ; Find the address of the Testcase ID
        ConsoleWrite("$TCAddress:"&$TCAddress&@CRLF)                    ; Addess will be in $Column$Row format (Ex : $B$14)
        $TCRow=StringTrimLeft($TCAddress,3)                         ; Remove the first 3 characters from left hand side (Ex : $B$)
        ConsoleWrite("$TCRow="&$TCRow)
        _ExcelWriteCell($oExcel, $TCStatus, $TCRow, $TCResIndex)            ; Write the result
    EndIf
EndFunc

Edited October 23, 2013 by Automationuser

[Automationuser]

In the above code, if you refer to

$oExcel.Application.ActiveSheet.Columns($TCIDIndex).Select
_ExcelWriteCell($oExcel, $TCStatus, $TCRow, $TCResIndex)

Here $TCIDIndex="A"(TestCase no. column.Ex : A,B,C), $TCResIndex=2(If result column no. is 2)

Edited October 23, 2013 by Automationuser

[water]

I'd suggest to add some error checking to function _WriteResult. What happens if the testcase isn't found in Excel?

And I would pass all needed global variables ($excelFile, $TCIDIndex, $oExcel) as parameters (good coding practice).

[Automationuser]

@Water,

I truely agree.

_WriteResult function should be modified to do error checks