Sign in to follow this  
Followers 0
veerendra

Excel

10 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@Water,

I truely agree.

_WriteResult function should be modified to do error checks

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
Sign in to follow this  
Followers 0