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
