Jump to content

Recommended Posts

Posted (edited)

Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.

#Include "Chrome.au3"
#Include "wd_core.au3"
#Include "wd_helper.au3"
#Include "WinHttp.au3"
#include <MsgBoxConstants.au3>
#include <File.au3>
#include <IE.au3>
#include <Array.au3>
#include <INet.au3>
#include <AutoItConstants.au3>
#include <WinAPIFiles.au3>
#include <GDIPlus.au3>
#include <Excel.au3>
#Include "WinHttp.au3"
#Include "_HtmlTable2Array.au3"

Local $sDesiredCapabilities, $sSession
SetupChrome()
_WD_Startup()
$sSession = _WD_CreateSession($sDesiredCapabilities)
_WD_LoadWait($sSession)
_WD_Navigate($sSession, "table1.html")

_WD_LoadWait($sSession)

_WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']")
Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']")
;ConsoleWrite ("mat-table " & $sElement & @CRLF)
Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True)

sleep(1000)

For $i = 0 to UBound($aArray1) - 1
  $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text')
Next
;_ArrayDisplay($aArray1)

;Email variables
$SmtpServer = "" ; address for the smtp-server to use - REQUIRED
$FromName = "Hermes" ; name from who the email was sent
$FromAddress = "sender@gmail.com" ; address from where the mail should come
$ToAddress = "recipient@gmail.com"   ; destination address of the email - REQUIRED, use commas (,) to add more email addresses
$Subject = "File not found" ; subject from the email - can be anything you want it to be
$Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail
$AttachFiles = ""                       ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed
$CcAddress = "" ; address for cc - leave blank if not needed
$BccAddress = "" ; address for bcc - leave blank if not needed
$Importance = "High" ; Send message priority: "High", "Normal", "Low"
$Username = "" ; username for the account used from where the mail gets sent - REQUIRED
$Password = "" ; password for the account used from where the mail gets sent - REQUIRED
$IPPort = 25 ; port used for sending the mail
$ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS
$tls = 0 ; enables/disables TLS when required

Local $oAppl = _Excel_Open()
Local $sWorkbook = "c:\test.xlsx"

Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters


If FileExists($sWorkbook) Then ;Check if the file exist.

    Local $oAppl = _Excel_Open()
    Local $sWorkbook = "c:\test.xlsx"

    Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters

    Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))

    Local $iIdx
    Local $Skipline = 0 ;0==> first line
    Do
    Local $temprf
    
    For $i = 0 To UBound($aArray2) - 1

        $temprf &= $aArray2[$i]

        _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]")
        Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True)

        $iIdx = _ArraySearch($aArray1, $aArray2[$i])
        
        If @error Then ContinueLoop
        _WD_ElementAction($sSession, $aElement[$iIdx], 'click')

        If $i < $Skipline Then ContinueLoop
        $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1)
        _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange)

        ;Paste
        Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton")
        _WD_ElementAction($sSession, $oTest4, 'click')

        Sleep(1000)

        ;Save Button
        Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button")
        _WD_ElementAction($sSession, $save3, 'click')


        _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1)
        sleep(1000)
     Next
Until (Not @error) 
     _Excel_Close($oWorkbook)

Else
    _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls)
    Exit
EndIf

_WD_LoadWait($sSession)

;Attaching files to emails
Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0)

    Local $objEmail = ObjCreate("CDO.Message")
    $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>'
    $objEmail.To = $s_ToAddress
    Local $i_Error = 0
    Local $i_Error_desciption = ""
    If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress
    If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress
    $objEmail.Subject = $s_Subject
    If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then
        $objEmail.HTMLBody = $as_Body
    Else
        $objEmail.Textbody = $as_Body & @CRLF
    EndIf

    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer
    If Number($IPPort) = 0 then $IPPort = 25
    $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort
    ;Authenticated SMTP
    If $s_Username <> "" Then
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username
        $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password
    EndIf
    ; Set security params
    If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
    ;Update settings
    $objEmail.Configuration.Fields.Update
    ; Set Email Importance
    Switch $s_Importance
        Case "High"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High"
        Case "Normal"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal"
        Case "Low"
            $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low"
    EndSwitch
    $objEmail.Fields.Update
    ; Sent the Message
    $objEmail.Send
    $objEmail=""
EndFunc   ;==>_INetSmtpMailCom


Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
Sleep(2000)
For $i = 1 To $aDir[0]
  DirRemove($aDir[$i], $DIR_REMOVE)
Next

_WD_LoadWait($sSession)

_WD_Shutdown()

Func SetupChrome()

    _WD_Option('Driver', 'chromedriver.exe')

    _WD_Option('Port', 9515)

    _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"')

    $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}'

EndFunc   ;==>SetupChrome

If the excel file doesn't exists in the folder, it will send an email to a specific recipient.

What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row

Appreciate any help that I can get to achieve this.

table1.html test.xlsx

Edited by Hermes
Link to post
Share on other sites

Are you running it via Scite? This is what I get --

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\Users\DanPollak\Dropbox\webdriver\test5.au3" /UserParams    
+>10:31:52 Starting AutoIt3Wrapper (21.316.1639.1) from:SciTE.exe (4.4.6.0)  Keyboard:00000409  OS:WIN_10/2009  CPU:X64 OS:X64  Environment(Language:0409)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\DanPollak\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\DanPollak\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.5)  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\DanPollak\Dropbox\webdriver\test5.au3
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(70,146) : warning: $sElement: possibly used before declaration.
        Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(72,38) : warning: $aArray1: possibly used before declaration.
        $iIdx = _ArraySearch($aArray1,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(82,102) : warning: $oTest2: possibly used before declaration.
        Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton", $oTest2)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(88,102) : warning: $save2: possibly used before declaration.
        Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button", $save2)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(70,146) : error: $sElement: undeclared global variable.
        Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(72,38) : error: $aArray1: undeclared global variable.
        $iIdx = _ArraySearch($aArray1,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(82,102) : error: $oTest2: undeclared global variable.
        Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton", $oTest2)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Users\DanPollak\Dropbox\webdriver\test5.au3"(88,102) : error: $save2: undeclared global variable.
        Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button", $save2)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Users\DanPollak\Dropbox\webdriver\test5.au3 - 4 error(s), 4 warning(s)
!>10:31:52 AU3Check ended. Press F4 to jump to next error.rc:2
+>10:31:52 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 1.001

 

Link to post
Share on other sites
Posted (edited)

Thanks again Danp! I just modified the AutoIt code, and attached the corresponding html, and excel file.

What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row

Edited by Hermes
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Hermes
      Hi, sometimes when I run my script in GUI (SciTe Editor) or as an executable file, the chrome driver launches but the chrome browser does not but when I re-run the script again - chrome driver launches and so does the browser. Here is the error that I'm getting:
      __WD_Post: StatusCode=0; ResponseText=WinHTTP request timed out before Webdriver... __WD_Post ==> Send / Recv error: WinHTTP request timed out before Webdriver _WD_CreateSession: WinHTTP request timed out before Webdriver _WD_CreateSession ==> Webdriver Exception: HTTP status = 0 Just wondering if there's a way to re-launch both the driver and browser without having to rerun the script/executable.
       
       
    • By SkysLastChance
      I am using web driver and xpaths to automate chrome. 
      I am able to edit everything I need to until I get these pop ups. Then when I try to find the xpath it act like it does not exist. 
      This code below is working to find input fields on the main page of the site. But, if I try the same thing for ones in the popover it will not find it. 😕
      $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//*[substring(@id, string-length(@id) - string-length('_3') + 1 ) = '_3']") _WD_ElementAction($sSession, $sElement, 'value', "TEST") Here is the html

      Any idea why it is not finding the element inside these pop overs?
       
       
      HTML:
       
    • By DJ143
      I have a autoit exe file which is used in upload/browse file functionality.  This has been integrated with selenium framework and I am invoking the autoit exe using Java process and runtime. 
      Now the issue is when I run the scripts and invoke the autoit exe in local it works perfectly.  But when I use selenium grid or jenkins to run the scripts in another windows server it is not working.
      Can anyone please suggest any solution for this?
    • By adityaparakh
      Hello ,
      I am trying to use Websockets in AutoIt.
      It is to fetch live stock market prices , API is provided and documentation available for python language.
      The link for the code snippet is :
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Introduction
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Instruments/paths/~1instruments~1subscription/post
       
      https://github.com/symphonyfintech/xts-pythonclient-api-sdk
       
      Second Link is to subscribe to a list of ExchangeInstruments.
      Now I would like to get live stock ltp (LastTradedPrice) for a few stocks whose "ExchangeInstrumentID" I know.
      I am able to use the WinHttp object to perform actions using simple codes like below :
      I have the secretKey and appkey and can generate the needed token. And get the unique ExchangeInstrumentID.

      Below code is just for example of how I am using WinHttp. Unrelated to socket part.
      Global $InteractiveAPItoken = IniRead(@ScriptDir & "\Config.ini", "token", "InteractiveAPItoken", "NA") $baseurl = "https://brokerlink.com/interactive/" $functionurl = "orders" $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") $oHTTP.Open("POST", $baseurl & $functionurl, False) $oHTTP.SetRequestHeader("Content-Type", "application/json;charset=UTF-8") $oHTTP.SetRequestHeader("authorization", $InteractiveAPItoken) $pD = '{ "exchangeSegment": "NSEFO", "exchangeInstrumentID": ' & $exchangeInstrumentID & ', "productType": "' & $producttype & '", "orderType": "MARKET", "orderSide": "' & $orderside & '", "timeInForce": "DAY", "disclosedQuantity": 0, "orderQuantity": ' & $qty & ', "limitPrice": 0, "stopPrice": 0, "orderUniqueIdentifier": "' & $orderidentifier & '"}' $oHTTP.Send($pD) $oReceived = $oHTTP.ResponseText $oStatusCode = $oHTTP.Status
          
          
      But am struggling to understand and use socket.
      Would be of great help if you can have a look at the link mentioned above and help with the code sample for AutoIt.
      To connect and listen to a socket.
      Thanks a lot
       
    • By Hermes
      Hi, I have a site that has the following elements below: 
      <div>More element here</div> <div>More element here</div> <div>More element here</div> When I do this in Auto It:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div") _WD_HighlightElement($sSession, $oSelectDiv, 1) I also tried to add [3], but it doesnt seems to work:
      Local $oSelectDiv = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "div[3]") _WD_HighlightElement($sSession, $oSelectDiv, 1) It always highlight the first one, but I am trying to highlight the 3rd in the list. Is there anyway to select the 3rd div without having to add any class/id in the divs, and without using XPATH? The structure of the elements in that site were built that way.
×
×
  • Create New...