Jump to content

Conditional Formatting (Excel) (Solved)


Recommended Posts

Hi Guys,

Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 

.Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A")
    .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets

I also tried it like this:

.Range("=$A3:$A4000").FormatConditions _
            .Add(xlCellValue, xlGreater, "=$A:$A")
    .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets

 

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites

@Skeletor
Could you please post the entire script or a reproducer, so we can see how we can help you? :)
And, since your script is using an external file ( the Excel one ), attach it or a reproducer of it.

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to post
Share on other sites
#include-once
#include <ExcelChart.au3>
.#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>



Global $aExcel = "C:\temp\test.xls"
Global $oWorkbook = _Excel_BookNew($aExcel)

With $oWorkbook.ActiveWorkbook.Sheets(1)
    .Range("A:M").Font.Color = 0x111111
    .Range("A:M").Font.Size = 9
    .Range("A:M").Font.Family = "Calibri"
    .Range("K1:M1").Interior.Color = 0xffCC99
    .Range("K2:M2").Interior.Color = 0xffCC99
    .Range("K1:M1").Borders.LineStyle = 1
    .Range("K2:M2").Borders.LineStyle = 1
    .Range("K2:M2").FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSets($xl3Arrows)

EndWith

 

Edited by Skeletor

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites

Untested:

.Range("$A3:$A4000").FormatConditions.Add($xlCellValue, $xlGreater, "$A:$A")
.IconSet($xl3Arrows) ; ActiveWorkbook.iconsets

Details find here:
https://docs.microsoft.com/en-us/office/vba/api/excel.formatconditions.add

If the $xl* variables are undefined then you need to define them based on the MSDN enumerations.
https://docs.microsoft.com/en-us/office/vba/api/excel.xlformatconditiontype

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
Local $xlCellValue = 1
Local $xl3Arrows = 1

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.Add($xlCellValue, $xlGreater, "$A:$A")
    .IconSet($xl3Arrows) ;ActiveWorkbook.iconsets
EndWith

Although, this does work, its just abit of tweaking to get the icons to appear. 
But this is a start. 

@water Thanks for the assistance. Thanks for the Excel UDF's... really making the most out of these UDF's.

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites

:) 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Hmmm, I got the icons to appear, but I cannot change them... even if I use Criteria(1) is still does not change to a different icon set. 

Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.AddIconSetCondition
    .IconSets($xl5Arrows)
EndWith

 

 

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites
  • 4 weeks later...

Not seen any changes with the iconset... 

Autoit script

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A3:A4000").FormatConditions.AddIconSetCondition
    .Range("A3:A4000").FormatConditions.IconSet.IconSets = $xl3Arrows
    .Range("A3:A4000").FormatConditions.IconCriteria.Type = $xlConditionValueNumber
    .Range("A3:A4000").FormatConditions.IconCriteria.Value = 1

EndWith
	.Range("A3:A453").Select
	$Selection.FormatConditions.AddIconSetCondition
	$Selection.FormatConditions($Selection.FormatConditions.Count).SetFirstPriority
	With Selection.FormatConditions(1)
	.ReverseOrder = False
	.ShowIconOnly = False
	.IconSet = ActiveWorkbook.IconSets(xl3Arrows)
	EndWith
	With .Selection.FormatConditions(1).IconCriteria(2)
	.Type = $xlConditionValuePercent
	.Value = 33
	.Operator = 7
	EndWith
	With Selection.FormatConditions(1).IconCriteria(3)
	.Type = $xlConditionValuePercent
	.Value = 67
	.Operator = 7
	EndWith

VB Script

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites

I have never played with Iconset etc.
Can you please provide an example Excel workbook and a stripped down but fully functional script we can play with?
In addition a screenshot of the result you get and the result you expect would be great.

Plus information about the Excel version you run.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Here is the sample code:

#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include-once
#include <ExcelChart.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)


_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "ID", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name", "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Company", "C1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Laptop", "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "SerialNumber", "E1")

For $i = 1 to 10 Step 1
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "00" & $i, "A" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name0" & $i, "B" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Scania", "C" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Dell", "D" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "W2314" & $i, "E" & $i)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=SUM(20 / " & $i & ")", "F" & $i)
Next


With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A:E").Font.Color = 0x111111
    .Range("A:E").Font.Size = 9
    .Range("A:M").Font.Family = "Arial"
    .Range("A1:F11").Borders.LineStyle = -4199

EndWith


; ######################################################################################################################

;                               ADD CONDITIONAL FORMATTING

; ######################################################################################################################

Local $xl3Arrows = 1
Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("F1:F11").FormatConditions.AddIconSetCondition
    .Range("F1:F11").FormatConditions.IconSet.IconSets = $xl3Arrows
    .Range("F1:F11").FormatConditions.IconCriteria.Type = $xlConditionValueNumber
    .Range("F1:F11").FormatConditions.IconCriteria.Value = 1

EndWith

and attached (Book5.xlsx) is the result including the expected result column.

 

Excel version - 365 (2016)

Book5.xlsx

Kind Regards
Skeletor

"I need coffee to turn me back to a human"

Microsoft Office Splash Screen | Basic Notepad Program (Beginner) | Transparent Splash Screen

Link to post
Share on other sites

Something like this (I know it is not 100% what you expect - but it runs without errors):

#include <Array.au3>
#include <INet.au3>
#include <File.au3>
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstants.au3>
#include <string.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include-once
; #include <ExcelChart.au3>

Local $oError = ObjEvent("AutoIt.Error", "_ErrFunc")
Global Const $xlDouble = -4119 ; Double line.

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "ID", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name", "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Company", "C1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Laptop", "D1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "SerialNumber", "E1")

For $i = 1 To 10 Step 1
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "00" & $i, "A" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Name0" & $i, "B" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Scania", "C" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Dell", "D" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "W2314" & $i, "E" & $i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=SUMME(20 / " & $i & ")", "F" & $i)
Next

With $oExcel.ActiveWorkbook.Sheets(1)
    .Range("A:E").Font.Color = 0x111111
    .Range("A:E").Font.Size = 9
    .Range("A:M").Font.Name = "Arial"
    .Range("A1:F10").Borders.LineStyle = $xlDouble
EndWith

; ######################################################################################################################
;                               ADD CONDITIONAL FORMATTING
; ######################################################################################################################

Local $xl3Arrows = 1
Local $xlCellValue = 1
Local $xl5Arrows = 13
Local $xlConditionValueNumber = 0

$oIconSet = $oExcel.ActiveWorkbook.Sheets(1).Range("F1:F10").FormatConditions.AddIconSetCondition
$oIconSet.IconSet = $oExcel.ActiveWorkbook.IconSets($xl3Arrows)
$oIconCriterion = $oIconSet.IconCriteria(2)
$oIconCriterion.Type = $xlConditionValueNumber
$oIconCriterion.Value = 15
$oIconCriterion.Operator = $xlGreaterEqual

; Retrieve information about each IconCriteria
For $oIconCriterion In $oIconSet.IconCriteria
    ConsoleWrite($oIconCriterion.Type & "-" & $oIconCriterion.Value & "-" & $oIconCriterion.Operator & @CRLF)
Next

Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Skeletor changed the title to Conditional Formatting (Excel) (Solved)

Just by checking MSDN (https://docs.microsoft.com/en-us/office/vba/api/excel.iconsetcondition) and following the examples I found there :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
      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
    • By robertocm
      change linked image paths in excel 2007 Open XML Files with AutoIt and 7-zip:
      #include <File.au3> #include <WinAPIFiles.au3> ;Required 7-zip Global $Path7z = @ProgramFilesDir & "\7-Zip" If Not FileExists($Path7z & "\7z.exe") Then MsgBox(16, "", "7z.exe not found in path " & $Path7z) Exit EndIf Global $bFileOpen Global $sFileRead ;Global $sOldImg = "C:\Users\MyUserName\Documents\MyImageFolder" ;Global $sNewImg = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder" Global $sOldImg = "C:\Users\MyUserName\Documents\MyImageFolder\My%20Image1.png" Global $sNewImg = "C:\Users\ANOTHERUSERNAME\Documents\AnotherImageFolder\My%20Image1.png" Global $sFileSelectFolder = FileSelectFolder("Directory to change excel image paths", "") Global $sTempDir = @ScriptDir & "\TempDir" Global $sFileCoreXml = $sTempDir & "\docProps\core.xml" If FileExists($sTempDir) Then DirRemove($sTempDir, $DIR_REMOVE) ;Look for excel files in selected directory and subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xls*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then For $i = 1 To $aFileList[0] ;Discard some kind of temp files (locked files from antivirus?) If StringLeft($aFileList[$i], 1) = "~" Then ContinueLoop $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file RunWait('"' & $Path7z & '\7z.exe" x -aoa -r -y "' & $aFileList[$i] & '" -o"' & $sTempDir & '"', $Path7z, @SW_HIDE) If FileExists($sFileCoreXml) Then $sFileRead = FileRead($sFileCoreXml) If __ReplaceImagePaths($sTempDir, $sOldImg, $sNewImg) = 1 Then Consolewrite("--> Image path replaced in file: " & $aFileList[$i] & @CRLF) EndIf ;Help File 7-zip.chm 'Switch can be used in any place in command line' RunWait('"' & $Path7z & '\7z.exe" a -r -tzip -y "' & $aFileList[$i] & '" "' & $sTempDir & '\*"', $Path7z, @SW_HIDE) Else If FileExists($sTempDir & "\EncryptedPackage") Then Consolewrite("Error password protected file: " & $aFileList[$i] & @CRLF) EndIf DirRemove($sTempDir, $DIR_REMOVE) Else Consolewrite("Error Locked file: " & $aFileList[$i] & @CRLF) EndIf Next Else MsgBox(16, "Error", "No excel files were found in the folder") EndIf Func __ReplaceImagePaths($sTempDir, $sFind, $sReplace) ;List all files with .xml.rels extension in the directory \xl\drawings\_rels Local $aFileListDrw = _FileListToArray($sTempDir & "\xl\drawings\_rels", "*.xml.rels", 1, True) If @error = 1 Then ;MsgBox (0, "", "Path was invalid") SplashTextOn("Title", "Path was invalid", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf If @error = 4 Then ;MsgBox (0, "No files", "No files were found") SplashTextOn("Title", "No files were found", -1, -1, -1, -1, 1, "", 24) Sleep(2000) SplashOff() Exit EndIf Local $iRetval ;Loop through the array For $i = 1 To $aFileListDrw[0] $iRetval = _ReplaceStringInFile($aFileListDrw[$i], $sFind, $sReplace) Next If Not $iRetval = -1 Then Return 1 EndFunc  
      Some references:
      https://stackoverflow.com/questions/37145369/change-path-to-picture-links-in-excel http://www.jkp-ads.com/Articles/Excel2007FileFormat.asp EDITED:
      Note: it seems that if User Account Control (UAC) is enabled then 7zip is unable to overwrite the destination file (using the same name).
      In this case, a possible solution would be to rename the original excel file before (see _PathSplit in help file).
      In my case i prefer just to disable UAC
    • By Hermes
      My _Excel_RangeCopyPaste is not working as intended. What I am trying to accomplish is copy the range B:E using _Excel_RangeCopyPaste in the first row and repeat the same for row 2 and so on.
      ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next Here's the full code:
      #Include <Chrome.au3> #Include <wd_core.au3> #Include <wd_helper.au3> #Include <WinHttp.au3> #include <MsgBoxConstants.au3> #include <IE.au3> #include <Array.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "test.html") Local $oAppl = _Excel_Open() Local $sWorkbook = "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 $oRange = $oWorkbook.ActiveSheet.Range("B:E") ;Skip from reading header columns Local $Skipline = 0 ;0==> first line Local $temprf For $i = 0 To UBound($aArray2) - 1 If $Skipline = $i Then ContinueLoop $temprf &= $aArray2[$i] _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) Local $oTest = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "PasteButton") ;This button will paste values from the Clipboard once clicked _WD_ElementAction($sSession, $oTest, 'click')) Next _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') Return '{"capabilities":{"alwaysMatch":{"goog:chromeOptions":{"w3c":true,' & _ '"excludeSwitches":["enable-automation"],"useAutomationExtension":false}}}}' EndFunc ;==>SetupChrome For the first row I am trying to copy just B:E with the following info
      Apple Banana Orange Mango and then repeat for row 2, row 3, etc. I've attached the spreadhseet.
      test.xlsxI have also attached the excel file for reference.
    • By Rskm
      Hi, My autoit program generates excel output file.  How do i set the author name for this excel file. thanks
    • By Hermes
      I have an html table that displays data along with an excel spreadsheet that has the same data as the html table. I am wanting to only match the Title column in my html table with the Title column in my Excel spreadsheet. If the titles match, click on the Edit hyperlink and continue to loop to next row. The issue I'm experience is its not matching correctly. So far  i've written the codes below:
      <table border="1" class="test"> <tr> <th> UniqueID</th> <th> Title</th> <th> UserID</th> <th> Address</th> <th> Gender </th> </tr> <tr> <td> 1 </td> <td> Title1 </td> <td> 12345 </td> <td> Manila </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5"> <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 2 </td> <td> Title2 </td> <td> 67891 </td> <td> Valenzuela </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 3 </td> <td> Title3 </td> <td> 88888 </td> <td> Ohio </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 4 </td> <td> Title4 </td> <td> 77777 </td> <td> California </td> <td> <span> Female </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> <tr> <td> 5 </td> <td> Title5 </td> <td> 33333 </td> <td> Arizona </td> <td> <span> Male </span> </td> </tr> <tr> <td align="center" colspan="5" > <a href="#" class="testlink">Edit</a> </td> </tr> </table> #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "Excel.au3" #Include "_HtmlTable2Array.au3" #Include "Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "index.html") Sleep(6000) Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "test.xlsx") ; Get the table element $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='test']") ; Retrieve HTML $sHTML = _WD_ElementAction($sSession, $sElement, "Property", "outerHTML") ;Local $aTable = _HtmlTableGetWriteToArray($sHTML) Local $aArray1 = _Excel_RangeRead($oWorkbook,1,$oWorkbook.ActiveSheet.Usedrange.Columns("B:B")) Local $aArray2 = _HtmlTableGetWriteToArray($sHTML) ;_ArrayDisplay($aArray1) ;_ArrayDisplay($aArray2) For $i = UBound($aArray1) - 1 To 0 step - 1 For $j = UBound($aArray2) - 1 to 0 step - 1 If $aArray1[$i][1] == $aArray2[$j][1] Then _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") $test1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//a[contains(@class,'testlink') or contains(text(),'Edit')]") _WD_ElementAction($sSession, $test1, 'click') ;_ArrayDisplay($aArray1) ;_ArrayDelete($aArray1 , $i) ;exitloop EndIf Next Next _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 Would appreciate if anyone can provide tips, or point me in the right direction in doing it.
       
      test.xlsx
×
×
  • Create New...