Jump to content

Recommended Posts

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

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 Kanashius
      This UDF can be used to draw at the Desktop Wallpaper (Windows 8+) with GDI+ without using files.
      The example runs with 60-120fps at my machine, so the speed is acceptable.
      When the script exits, the original wallpaper is restored.
      Have fun :).
      PS: The UDF uses the window between the background and the icons, so it could be used to add an own child window at the desktop. Just look at the begin of __Wallpaper_Startup ($hWorkerW).
       
      Example:
       
      WallpaperUDF.au3 WallpaperUDF Example.au3
    • By TTE26
      For the past two days I've been reading all Wiki pages, installed all the demos etc. but I am still unable to do this simple workflow
      1. I want to use WebDriver UDF to open Chrome (Set chrome option arguments - Window Size, Pixel ratio, device type and platform name, user-agent.)
      2. On new Tab I want to read the tab Title and save it into a TXT file.
      3. I need to save the Session ID with my previosly set Capabilities into a file.
      4. I want to close the chrome window.
      Then
      5. I want to open Chrome again, but with my previosly saved Session ID and Capabilities list from the file.
      6. And minimaze the window state.
      7. After 30 seconds, close the chrome.
      I would really appreciate for the WebDriver UDF Helper file, to have also basic code examples, for non coders. For example how to open a windwow etc., save sessions, etc.
      Anyway, if someone could help me get started and help me with this workflow, that would be much appreaciated. 😔
    • By lapoelkan12
      hi i try to use webdriver  i download it and  download json and  winhttp i try to run it  but  return error
       error: __WinHttpVer(): undefined function.
              Local $sWinHttpVer = __WinHttpVer()
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
       
       

    • By sandgre
      Wondering if you can help a teacher out.  For the last few years of remote, hybrid, and in-person teaching I've been using an AutoHotKey script that creates an inking toolbar to be used during a PowerPoint presentation.  (https://www.autohotkey.com/boards/viewtopic.php?t=79163) The annotation tools available through Ppt are woefully inadequate, and, on my machine at least, jump around all over the place - sometimes on the bottom, sometimes on the left, sometimes on the right.  It's a scavenger hunt every single time.  The inking tool is always available, and recent updates allowed for the addition of lots more colors, which I used for modeling phenomena with students. One tragic day I arrived to school to find that my district IT dep't took away access to AutoHotKey.  No more tool bar. 
      I've been trying to resurrect some programming skills from a past life. I'm working through various tutorials and help files, with the goal of recreating the toolbar in AutoIt.  I'm really needing some help to move things along though.  I've been going through your PowerPoint UDF and all of your functions seem to just relate to editing the slides. Can you point me in the direction of how to create a toolbar that accesses the annotation tools that are available during the slideshow itself? 
       
    • By levila
      Hi Guys, i just do some codding to automate login to some of the web.
      I manage to do the script but somehow after script done, some weird box popup. and keep looping until i close the main chrome.exe

      here is my code. kinldy pls assist, im very new to webdriver udf.
      #NoTrayIcon #include "wd_core.au3" #include "wd_helper.au3" $_WD_DEBUG = $_WD_DEBUG_None ; You could also use $_WD_DEBUG_Error Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://someoftheweb.com") _ChromeSetInputValueByName($sSession, "inputName", "Username") _ChromeSetInputValueById($sSession, "inputPassword", "Password") _ChromeSetMouseClick($sSession, "submit", "click") _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":["--no-sandbox"]}}}}' EndFunc Func _ChromeSetInputValueByName($sSession,$name,$Value) $sButton = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='"&$name&"']") _WD_ElementAction($sSession,$sButton,'value', $Value) EndFunc Func _ChromeSetInputValueById($sSession,$Id,$Value) $sButton = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//input[@id='"&$Id&"']") _WD_ElementAction($sSession,$sButton,'value', $Value) EndFunc Func _ChromeSetMouseClick($sSession,$Id,$Value) $sButton = _WD_FindElement($sSession,$_WD_LOCATOR_ByXPath,"//button[@type='"&$Id&"']") _WD_ElementAction($sSession, $sButton, 'click', $Value) EndFunc  
×
×
  • Create New...