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 water
      I have started to rewrite the PowerPoint UDF similar to the Word, Excel and Outlook UDF. The new UDF will not be compatible with the existing one.
      I will add examples, documentation, a section in the wiki etc.
      So far the following functions have been coded - more are on the ToDo list.
      _PPT_Open: Open a connection to a running instance of PowerPoint or start a new one _PPT_Close: Close a PowerPoint instance _PPT_CommentAdd: Add a Comment anywhere on a Slide or connected to a Shape (version 1.5.0.0 2021-08-31) _PPT_CommentDelete: Delete a single Comment from a Slide or all Comments from a SlideRange (version 1.5.0.0 2021-08-31) _PPT_CommentList: Get a list of all Comments in a Presentation (version 1.5.0.0 2021-08-31) _PPT_ConvertUnits: Convert units (like centimeters) to other units (like points) (version 1.3.0.0 2021-08-17) _PPT_HeaderFooterList: Returns a list of Headers and Footers for a Presentation (version 1.1.0.0 2021-07-25, removed with version 1.4.0.0 2021-08-25) _PPT_HeaderFooterSet: Sets the Header/Footer for a Slide/Slides or the Slide-/Notes-/Handout-/Title-Master of the Presentation (version 1.2.0.0 2021-08-09) _PPT_PresentationAttach: Attach to an already opened Presentation _PPT_PresentationClose: Close a Presentation _PPT_PresentationExport: Export one/multiple/all Slides as PDF or XPS. _PPT_PresentationExportGraphic: Export one/multiple/all Slides in a graphic format. _PPT_PresentationList: Returns a list of currently open Presentations (version 1.0.0.0 2021-07-20) _PPT_PresentationNew: Create a new Presentation _PPT_PresentationOpen: Open an existing Presentation _PPT_Print: Print one/multiple/all Slides of a Presentation (version 1.0.0.0 2021-07-20) _PPT_PresentationSave: Save a Presentation _PPT_PresentationSaveAs: Save a Presentation to another location or with another type _PPT_ShapeAdd: Add a Shape to a single or multiple Slides (version 1.1.0.0 2021-07-25) _PPT_ShapeAlignDistribute: Aligns and distributes the Shapes in the specified ShapeRange (version 1.6.0.0 - to be released) _PPT_ShapeCopyMove: Copy/move a Shape(s) of a single Slide to a Slide(s) in the same or a different Presentation (version 1.1.0.0 2021-07-25) _PPT_ShapeDelete: Delete a Shape or Shapes from a single or multiple Slides (version 1.1.0.0 2021-07-25) _PPT_SlideAdd: Add slide(s) to a Presentation _PPT_SlideCopyMove: Copy, move, duplicate Slide(s) _PPT_SlideDelete: Delete Slide(s) _PPT_SlideShow: Show a Presentation _PPT_TableGet: Extract data from a PowerPoint Table Shape to an array (version 1.3.0.0 2021-08-17) _PPT_TableSet: Write data to a PowerPoint Table Shape (version 1.3.0.0 2021-08-17) _PPT_TextFindReplace: Find & replace text throughout entire PowerPoint presentation (version 1.0.0.0 2021-07-20) _PPT_TextSet: Sets/modifies the Text of a Shape or Shapes of a single or multiple Slides (version 1.2.0.0 2021-08-09 Which functions do you want me to add to the UDF?
      ToDo list:
      create slide (JLogan3o13) - Done: _PPT_SlideAdd Apply template to slide (JLogan3o13) - Done: _PPT_SlideAdd Apply theme to slide (JLogan3o13) Cut/Copy/Duplicate/Move/Delete slide(s) (JLogan3o13) - Done: _PPT_SlideCopyMove, _PPT_SlideDelete Export slides (JLogan3o13) - Done: _PPT_PresentationExport Cut/Copy/Duplicate/Move/Delete shape(s) (JLogan3o13): Done : _PPT_ShapeCopyMove, _PPT_ShapeDelete (version 1.1.0.0 2021-07-25) ApplyAnimation, Select, SetDefaultProperties, and zOrder for shape(s) (JLogan3o13) Searching and replacing text (pcjunki) - Done: _PPT_TextFindReplace (version 1.0.0.0 2021-07-20) Export slides in a graphic format (JPG, GIF, PNG ...) (UEZ) - Done: _PPT_PresentationExportGraphic The UDF can be downloaded from the download forum.
       
    • By p4sCh
      Hello everyone,
      I've created a UDF for basic communication with SSH servers. I know there is already such a UDF, but I wasn't satisfied with it for my purpose, so I created a new one.
      This UDF also acts as a wrapper for the plink executable. Its essential functions are _SSHConnect, _SSHSend, _SSHRecv and _SSHCloseSocket.
      It does support multiple simultaneous connections and aims to be pretty robust. Feel free to share your opinions
      Two of the included examples use a slightly modified version of Vintage Terminal by @Chimp
      Download
      The download includes ssh.au3 (UDF), plink.exe (necessary), vintage terminal and code examples:
      SSH UDF.zip
    • By mLipok
      Recently I start wd_capabilities.au3 project on my side.
      I think this is time to share to AutoIt community.
      Check attached UDF: wd_capabilities.au3

      Here is a scratch example showing how to use them:
      #include "wd_helper.au3" #include "wd_capabilities.au3" _Example() Func _Example() ........ ........ ........ _WD_Capabilities_Build_Arguments(Null) ; CLEANUP ARGS _WD_Capabilities_Build_Arguments('--headless') _WD_Capabilities_Build_Arguments('start-maximized') _WD_Capabilities_Build_Arguments('disable-infobars') _WD_Capabilities_Build_Arguments('user-data-dir', 'C:\Users\' & @UserName & '\AppData\Local\Google\Chrome\User Data\Default') Local $s_WD_Capabilities_Arguments = _WD_Capabilities_Build_Arguments(Default) ; GET ARGS _WD_Capabilities_Build_Preferences(Null) ; CLEANUP PREFS If $s_Download_dir Then _WD_Capabilities_Build_Preferences('download.default_directory', $s_Download_dir) Local $s_WD_Capabilities_Preferences = _WD_Capabilities_Build_Preferences(Default) ; GET PREFS Local $s_Desired_Capabilities = _WD_Capabilities_Build_JSON('chrome', $s_WD_Capabilities_Arguments, $s_WD_Capabilities_Preferences) Local $WD_SESSION = _WD_CreateSession($s_Desired_Capabilities) ........ ........ ........ EndFunc  
      I plan to publish the code on GitHub.
      In the indefinite future, when the project becomes more functional, if @Danp2 accepts it, I hope it may be included in the main WebDriver UDF.

      I am waiting for yours feedback and help in the further development of this project.

      REMARK 1:
      As so far this UDF only supports GoogleChrome.
      Adding support for other browsers shouldn't be difficult, I just didn't need to.

      REMARK 2:
      For now, this project is working but still as a early BETA, so please do not discuss, ask questions, or post examples or snippets of code that pertain to any function of this UDF in other part of this forum, until it is found acceptable. Simply: discusion and tests only here in this topic.


      Regards,
      @mLipok
       
    • By seadoggie01
      This UDF is because I'm tired of trying to use UI Automation and Send to automate Adobe Acrobat. I often need to read the contents of PDFs and Acrobat is not easy to work with as a window.  The functions are based on the API Reference from Adobe located here.
      Acrobat Pro is required for all functions.
      It's very beta right now, but it still seems to work. Currently, the functions are based around page level manipulation of PDF documents: re-arranging, swapping, deleting, and moving pages as this is what I use the most.
      Please feel free to request/suggest features!
       
       
    • By Danp2
      Introduction
      This UDF will allow you to interact with any browser that supports the W3C WebDriver specifications. Supporting multiple browsers via the same code base is now possible with just a few configuration settings.
      Requirements
      JSON UDF https://www.autoitscript.com/forum/topic/148114-a-non-strict-json-udf-jsmn
      WinHTTP UDF https://www.autoitscript.com/forum/topic/84133-winhttp-functions/
      HtmlTable2Array UDF (optional) https://www.autoitscript.com/forum/topic/167679-read-data-from-html-tables-from-raw-html-source/
      WebDriver for desired browser
      Chrome WebDriver https://sites.google.com/a/chromium.org/chromedriver/downloads FireFox WebDriver https://github.com/mozilla/geckodriver/releases Edge WebDriver https://developer.microsoft.com/en-us/microsoft-edge/tools/webdriver/ Source Code
      You will always be able to find the latest version in the GitHub Repo 
      Help / Support
      See the wiki for details on the UDF --
      https://www.autoitscript.com/wiki/WebDriver
      Please post any questions, suggestions or errors in the GH&S thread.
      https://www.autoitscript.com/forum/topic/205553-webdriver-udf-help-support-iii/
      Previous support threads (Closed)
      https://www.autoitscript.com/forum/topic/192730-webdriver-udf-help-support/
      https://www.autoitscript.com/forum/topic/201106-webdriver-udf-help-support-ii/
       
×
×
  • Create New...