Jump to content

Copy Range of cells from Excel between two values


Recommended Posts

Dear all,

first of all hi.

I`m in trouble, again 😕

I can`t find right solution, tried all possible combinations generated by my mind and nothing.

I need to copy range of values depending by values from two different cells in excel sheet.

I put an example in attachment.

image.png.a50b062f5de2466c2fdcbdf3e44ed462.png

I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).

It is possible to do this using Autoit? 🤔

Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).

Thank you very much, in advance, for your support, solution really will make my life much easier 🙃

Have a nice day all of you!

Best Regards,

Cristin

Book1.xlsx something.au3

Link to post
Share on other sites

Quick and dirty solution to solve the copy/paste problem (and a starter for the rest of the script).
Excel starts searching at the row following the current row. So when you start at the top of a worksheet and the first hit is in row 1 you will get this hit at the end of the returned array. I fix this by moving the last row of the array to the top of the array (but a check needs to be added if "value 1" can't always be found in row 1).
After I have found the start rows ("value 1") I search for the end rows ("value 2"). Then I copy all ranges to the same worksheet  with an offset to column "M".

#include <Array.au3>
#include <Excel.au3>

Local $aDiskInfo[0][80], $aWorkbook
Local $sWorkbook = @ScriptDir & "\Book1.xlsx"
Local $sSheetInput = "Sheet1"
Local $bSheetDelete = False ;~ Change to True to create new sheet.
Local $sSheetOutput = "Output"

Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Excel_Close($oExcel)

; Find all occurrences of value "input" (partial match)
Local $aResult1 = _Excel_RangeFind($oWorkbook, "VALUE 1", $oWorkbook.Sheets($sSheetInput).Usedrange)
If @error Then Exit
_ArrayInsert($aResult1, 0) ; Insert an empty row
_ArraySwap($aResult1, 0, Ubound($aResult1, 1) - 1) ; Move the last row to row 0
_ArrayDelete($aResult1, Ubound($aResult1, 1) - 1) ; Delete the last row
Local $aResult2 = _Excel_RangeFind($oWorkbook, "VALUE 2", $oWorkbook.Sheets($sSheetInput).Usedrange)

For $i = 0 To UBound($aResult1) - 1
    $iRowStart = $oWorkbook.Sheets($sSheetInput).Range($aResult1[$i][2]).Row
    $iRowEnd = $oWorkbook.Sheets($sSheetInput).Range($aResult2[$i][2]).Row
    _Excel_RangeCopyPaste($oWorkbook.Sheets($sSheetInput), "A" & $iRowStart & ":K" & $iRowEnd, "M" & $iRowStart)
Next

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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
12 hours ago, water said:

Quick and dirty solution to solve the copy/paste problem (and a starter for the rest of the script).

Dear @water, GREAT solution, it works perfect, I`m very happy 🤩

Now I should found a solution to create new sheets for each copied range. It shouldn`t be a hard work for me.

Thank you a lot, again I see that this forum is an excellent place!

Link to post
Share on other sites

:)
If you encounter problems don't hesitate to post here again ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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
3 hours ago, water said:

:)
If you encounter problems don't hesitate to post here again ;)

Sorry but, I`m stuck again 😳

Can`t make it to work right 😖

 

Func _ExcelSheetAddAfter($oXl, $sAfterSheet = "", $sNewSheetName = "")
    If Not IsObj($oXl) Then Return SetError(1, 0, 0)
    Local $iSheetCount = $oXl.ActiveWorkbook.Sheets.Count
    If $sAfterSheet = "" Then
        $oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($iSheetCount)).Activate
    Else
        For $i = 1 To $iSheetCount
            If $oXl.ActiveWorkbook.Sheets($i).Name = $sAfterSheet Then
                $oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($i)).Activate
                ExitLoop
            EndIf
        Next
        If $i > $iSheetCount Then Return SetError(2, 0, 0)
    EndIf
    If $sNewSheetName <> "" Then $oXl.ActiveSheet.Name = $sNewSheetName
    Return 1
EndFunc

🤔

Link to post
Share on other sites

Hey,

Haven't used Excel UDF really but, how about if you change

$oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($iSheetCount)).Activate

into 

$oXl.ActiveWorkBook.WorkSheets.Add(Default, $oXl.ActiveWorkbook.Sheets($iSheetCount))

I think any new sheets added should be activated without any intervention.

Link to post
Share on other sites

You should use function _Excel_SheetAdd. All functions starting with _Excel* without a second underscore belong to a very, very old version of AutoIt (< 3.3.12.0).
You can't mix the old and new versions. Details about this script breaking change can be found here.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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
3 hours ago, water said:

You should use function _Excel_SheetAdd. All functions starting with _Excel* without a second underscore belong to a very, very old version of AutoIt (< 3.3.12.0).
You can't mix the old and new versions. Details about this script breaking change can be found here.

Yeah, I found a solution ... but, I can copy only text without formatting. Hope you can help me with this last one trouble.

 

#include <Array.au3>
#include <Excel.au3>

Local $aDiskInfo[0][80], $aWorkbook
Local $sWorkbook = @ScriptDir & "\book1.xlsx"
Local $sSheetInput = "Sheet1"
Local $bSheetDelete = True ;~ Change to True to create new sheet.
Local $sSheetOutput = "Output"
Local $s=0

Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Excel_Close($oExcel)

; Find all occurrences of value "input" (partial match)
Local $aResult1 = _Excel_RangeFind($oWorkbook, "VALUE 1", $oWorkbook.Sheets($sSheetInput).Usedrange)
If @error Then Exit
_ArrayInsert($aResult1, 0) ; Insert an empty row
_ArraySwap($aResult1, 0, Ubound($aResult1, 1) - 1) ; Move the last row to row 0
_ArrayDelete($aResult1, Ubound($aResult1, 1) - 1) ; Delete the last row
Local $aResult2 = _Excel_RangeFind($oWorkbook, "VALUE 2", $oWorkbook.Sheets($sSheetInput).Usedrange)

For $i = 0 To UBound($aResult1) - 1
    $iRowStart = $oWorkbook.Sheets($sSheetInput).Range($aResult1[$i][2]).Row
    $iRowEnd = $oWorkbook.Sheets($sSheetInput).Range($aResult2[$i][2]).Row
    $s=$i+1
    _Excel_SheetAdd($oWorkbook,$s)
    $read=_Excel_RangeRead($oWorkbook,$sSheetInput,"A" & $iRowStart & ":K" & $iRowEnd)
    _Excel_RangeWrite($oWorkbook,$s,$read,"A1")
Next

Thank you in advance for your support!

Link to post
Share on other sites

_Excel_RangeRead just reads the "value, formula or displayed text" of a range. Means: Just the content of cells.
You need to use _Excel_RangeCopyPaste.
 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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
23 hours ago, water said:

_Excel_RangeRead just reads the "value, formula or displayed text" of a range. Means: Just the content of cells.
You need to use _Excel_RangeCopyPaste.
 

DONE 🤩

#include <Array.au3>
#include <Excel.au3>

Local $aDiskInfo[0][80], $aWorkbook
Local $sWorkbook = FileOpenDialog("Choose Excel file", @ScriptDir, "Excel (*.xls;*.xlsx)")
Local $sSheetInput = "Sheet"
Local $bSheetDelete = True ;~ Change to True to create new sheet.
Local $sSheetOutput = "Output"
Local $s=0

Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Excel_Close($oExcel)

; Find all occurrences of value "input" (partial match)
Local $aResult1 = _Excel_RangeFind($oWorkbook, "VALUE 1", $oWorkbook.Sheets($sSheetInput).Usedrange)
If @error Then Exit
_ArrayInsert($aResult1, 0) ; Insert an empty row
_ArraySwap($aResult1, 0, Ubound($aResult1, 1) - 1) ; Move the last row to row 0
_ArrayDelete($aResult1, Ubound($aResult1, 1) - 1) ; Delete the last row
Local $aResult2 = _Excel_RangeFind($oWorkbook, "VALUE 2", $oWorkbook.Sheets($sSheetInput).Usedrange)

For $i = 0 To UBound($aResult1) - 1
    $iRowStart = $oWorkbook.Sheets($sSheetInput).Range($aResult1[$i][2]).Row
    $iRowEnd = $oWorkbook.Sheets($sSheetInput).Range($aResult2[$i][2]).Row
    $s=$i+1
    $read=_Excel_SheetAdd($oWorkbook,$s)
    _Excel_RangeCopyPaste($oWorkbook.Sheets($sSheetInput),"A" & $iRowStart & ":K" & $iRowEnd, $oWorkbook.Sheets($s).Range("A1"))
Next

If you have some ideas how to improve this code, please, I`ll be glad.

Thank you for support ☺️

Have a great day!

BR

Link to post
Share on other sites

I suggest to use variable names you can easily understand what they are used for:

For $i = 0 To UBound($aResult1) - 1
    $iRowStart = $oWorkbook.Sheets($sSheetInput).Range($aResult1[$i][2]).Row
    $iRowEnd = $oWorkbook.Sheets($sSheetInput).Range($aResult2[$i][2]).Row
    $iSheetIndex = $i + 1
    $read = _Excel_SheetAdd($oWorkbook, $iSheetIndex)
    _Excel_RangeCopyPaste($oWorkbook.Sheets($sSheetInput),"A" & $iRowStart & ":K" & $iRowEnd, $oWorkbook.Sheets($iSheetIndex).Range("A1"))
Next

If you need to enhance speed you could create all worksheets with a single call to _Excel-SheetAdd before the loop and specify the number of sheets to create.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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

Dear @water

when i use this 

_Excel_BookOpen 

@erorr = 3 come out 

this error begin after i changed to excel 13

any ideas please


 
#include <Array.au3>
#include <Excel.au3>

Local $aDiskInfo[0][80], $aWorkbook
Local $sWorkbook = FileOpenDialog("Choose Excel file", @ScriptDir, "Excel (*.xls;*.xlsx)")
Local $sSheetInput = "Sheet"
Local $bSheetDelete = True ;~ Change to True to create new sheet.
Local $sSheetOutput = "Output"
Local $s=0

Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Excel_Close($oExcel)

; Find all occurrences of value "input" (partial match)
Local $aResult1 = _Excel_RangeFind($oWorkbook, "VALUE 1", $oWorkbook.Sheets($sSheetInput).Usedrange)
If @error Then Exit
_ArrayInsert($aResult1, 0) ; Insert an empty row
_ArraySwap($aResult1, 0, Ubound($aResult1, 1) - 1) ; Move the last row to row 0
_ArrayDelete($aResult1, Ubound($aResult1, 1) - 1) ; Delete the last row
Local $aResult2 = _Excel_RangeFind($oWorkbook, "VALUE 2", $oWorkbook.Sheets($sSheetInput).Usedrange)

For $i = 0 To UBound($aResult1) - 1
    $iRowStart = $oWorkbook.Sheets($sSheetInput).Range($aResult1[$i][2]).Row
    $iRowEnd = $oWorkbook.Sheets($sSheetInput).Range($aResult2[$i][2]).Row
    $s=$i+1
    $read=_Excel_SheetAdd($oWorkbook,$s)
    _Excel_RangeCopyPaste($oWorkbook.Sheets($sSheetInput),"A" & $iRowStart & ":K" & $iRowEnd, $oWorkbook.Sheets($s).Range("A1"))
Next

 

Link to post
Share on other sites

Can you please check the content of $sWorkbook before _Excel_BookOpen to make sure it contains a valid path?
 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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

In addition I suggest to use

#include <FileConstants.au3>
Local $sWorkbook = FileOpenDialog("Choose Excel file", @ScriptDir, "Excel (*.xls;*.xlsx)", BitOR($FD_FILEMUSTEXIST, $FD_PATHMUSTEXIST))
If @error Then Exit ; User cancelled the file selection dialog or there is a problem with the file filter

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-11-16 - Version 1.7.0.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 Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
    • 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
×
×
  • Create New...