kemo1987

Hello i am trying to make script that export data from erp then load it to array (done) then insert array values inside another sheet but after find cell that has yesterday date and insert the 4 values next to that date i have attached the code and screens "i have tried to use chatgpt but no luck always get error to find date and even i tried to map the month and days by use letter as month and day number as u see in column C " Thanks and appreciate your time #region --- Internal functions Au3Recorder Start --- Func _Au3RecordSetup() Opt('WinWaitDelay',100) Opt('WinDetectHiddenText',1) Opt('MouseCoordMode',0) EndFunc #AutoIt3Wrapper_Version=Beta #AutoIt3Wrapper_Res_Fileversion=1.0.0.0 #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #Include<file.au3> #include <INet.au3> ; needed for get source (hmtl) #include <String.au3> ; needed for stringbetween #include<Array.au3> ; #include <Date.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #include <src/OutlookEX 1.7.0.1/OutlookEx.au3> ;==================================== _movetosheet() Func _movetosheet() Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\temp\Agouza.xls") sleep(1000) $datawb.worksheets("ffgg").select sleep(1000) $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata = _Excel_RangeRead($datawb, Default, Default ) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 2) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 4) _ArrayColDelete($mydata, 5) _ArrayColDelete($mydata, 5) _ArrayColDelete($mydata, 5) _ArrayColDelete($mydata, 5) _ArraySwap($mydata, 0, 4, True) _ArraySwap($mydata, 1, 3, True) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 6) ;_ArrayDelete($mydata, 7) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayDelete($mydata, UBound($mydata) - 1) $mydata[6][4] = $mydata[6][2] - $mydata[6][3] $mydata[7][0] ="متوسط نسبة الربح إلى رأس المــال" sleep(1000) If IsArray($mydata) Then _ArrayDisplay($mydata) _Excel_BookClose($datawb) ;-------------------------------- ; Initialize Excel application Local $oExcel = _Excel_Open() ; Open the existing workbook (Replace 'YourExcelFile.xlsx' with the actual path) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Data\Data.xlsx") ; Use the already populated $mydata array to get values Local $Credit = $mydata[1][2] Local $Delivery = $mydata[4][2] Local $Cash = $mydata[2][2] + $mydata[3][2] + $mydata[5][2] Local $Total = $mydata[6][2] ; Write data into the sheet for Tuesday row (row 2) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Credit, "D4") ; Credit for Tuesday 1/10 _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Delivery, "E4") ; Delivery for Tuesday _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Cash, "F4") ; Cash for Tuesday _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $Total, "G4") ; Total for Tuesday ; Save the workbook _Excel_BookSave($oWorkbook) ; Close the workbook and Excel application _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel) ;MsgBox(0, "Excel", "Array data written to Excel successfully!") $oExcel.Quit() $oExcel.Quit() EndFunc

after some help of chatgpt i get this answer using dictionary method and now it takes >Exit code: 0 Time: 109.1 thanks everyone The nested loops comparing each element from two arrays can indeed cause performance issues, especially with large datasets. One way to optimize this section is to use a more efficient approach like a hash table or dictionary to store and access the data. #include <INet.au3> ; needed for get source (hmtl) #include <String.au3> ; needed for stringbetween #include<Array.au3> ; #include <Date.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> ;==================================== Global $brname="agouza" Global $htfilename="agouzaold.htm" Global $exfilename= "agouzaold.xls" Global $path = "D:\Desktop\Autoit project\" ;==================================== _movetosheet() Func _movetosheet() ;-----------------------------mydata current stock--------------- Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\itemcard.xlsx") $datawb.worksheets("Sheet1").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata = _Excel_RangeRead($datawb, Default, Default ) _Excel_BookClose($datawb) sleep(1000) $oExcel.Quit() sleep(1000) ; ---------------------------------------------------------- Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Agouza.xlsx") $datawb.worksheets("Sheet1").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata2 = _Excel_RangeRead($datawb, Default, Default ) ;_ArrayColDelete($mydata2, 3) ;_ArrayDelete($mydata2, 0) _Excel_BookClose($datawb) sleep(1000) $oExcel.Quit() ;;--------------------Add quantity & price to mydata ------------------ ;~ Loop through $mydata2 ; Your existing code... ; The optimization block using dictionary Local $data2Dict = ObjCreate("Scripting.Dictionary") For $i = 1 To UBound($mydata2) - 1 $data2Dict(StringStripWS($mydata2[$i][0], 7)) = $mydata2[$i][1] & "|" & $mydata2[$i][2] Next For $j = 1 To UBound($mydata) - 1 Local $key = StringStripWS($mydata[$j][0], 7) If $data2Dict.Exists($key) Then Local $values = StringSplit($data2Dict($key), "|") $mydata[$j][9] = $values[1] $mydata[$j][8] = $values[2] EndIf Next ; End of optimization block ; The rest of your existing code... ;~ Loop through $mydata2 ;~ For $i = 1 To UBound($mydata2)-1 ;~ If StringStripWS($mydata2[$i][0],7) = "" Then ContinueLoop ;~ ;~ Loop through $mydata ;~ For $j = 1 To UBound($mydata)-1 ;~ ;~ Check if $mydata[x][column 0] = $mydata2[x][column 0] and set $mydata[x][column 4] to $mydata2[x][column 1] ;~ If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2] ;; Quantity 1>0 ;~ If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1] ;price ;~ Next ;~ Next ;;=================================== ;---------------------mydata Set on------------------DOne For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][9])="" Then $mydata[$i][9] =0 Next For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][9])>1 Then $mydata[$i][9] =1 Next ;~ ;--------------mydata set unwanted items to zero Local $filea = @ScriptDir & "\inc\snagouza.txt" Local $filesn _FileReadToArray($filea, $filesn) For $i = 1 To UBound($filesn) - 1 For $j = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$j][0]) = $filesn[$i] Then $mydata[$j][9] =0 ;_ArrayDelete($mydata, $j) Next Next ;'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ DOne ; Load the unwanted items into a dictionary ;~ Local $unwantedItemsDict = ObjCreate("Scripting.Dictionary") ;~ Local $filea = @ScriptDir & "\inc\snagouza.txt" ;~ Local $filesn ;~ _FileReadToArray($filea, $filesn) ;~ For $i = 1 To UBound($filesn) - 1 ;~ $unwantedItemsDict($filesn[$i]) = True ;~ Next ;~ ; Loop through the data and set unwanted items to zero ;~ For $j = 1 To UBound($mydata) - 1 ;~ If $unwantedItemsDict.Exists($mydata[$j][0]) Then ;~ $mydata[$j][9] = 0 ;~ EndIf ;~ Next ;=========================================== ;If IsArray($mydata) Then _ArrayDisplay($mydata) ;==================================================== ;--------------------------------create open & save to new xlsx--------------------------- Global $pExcel = _Excel_Open() sleep(1000) Global $oWorkbook = _Excel_BookNew($pExcel, 1) sleep(1000) Global $sWorkbook = @ScriptDir & "\agouza-final.csv" sleep(1000) _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) sleep(1000) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True) sleep(1000) _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) Sleep(3000) _Excel_BookClose($oWorkbook) Sleep(3000) $oExcel.Quit() sleep(2000) EndFunc

i have tried with 1000 rows sheet instead of 65000 it takes about 1 min the slowdown happening when replace items from arrays cell by cell Here If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2] ;; Quantity 1>0 If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1] ;price

Hello I have a problem when executing this code it takes very long time i even run it on hp server with 12g ram and 12 core cpu still very slow after i run it it loads the 2 sheets into arrays in seconds then keep loading forever the main usage of it is to load all itmes card and replace quantity and price from Agouza.xlsx is there any other ways to do the same scenario but faster Thanks in advance Itemcard.xlsx about 65000 rows SKU Barcode reason start_date end_date Campaing_status discounted_price max_no_of_orders original_price active 84729 0 0 0 114032 0 0 0 87306 0 0 0 Agouza.xlsx about 7000 rows Code quantity price 97918 4 22.5 105628 1 27.5 48991 1 1800 49073 1 400 #include <INet.au3> ; needed for get source (hmtl) #include <String.au3> ; needed for stringbetween #include<Array.au3> ; #include <Date.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> ;==================================== Global $brname="agouza" Global $htfilename="agouzaold.htm" Global $exfilename= "agouzaold.xls" Global $path = "D:\Desktop\Autoit project\" ;==================================== _movetosheet() Func _movetosheet() ;-----------------------------mydata current stock--------------- Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\itemcard.xlsx") $datawb.worksheets("Sheet1").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata = _Excel_RangeRead($datawb, Default, Default ) _Excel_BookClose($datawb) sleep(1000) $oExcel.Quit() sleep(1000) ; ---------------------------------------------------------- Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Agouza.xlsx") $datawb.worksheets("Sheet1").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata2 = _Excel_RangeRead($datawb, Default, Default ) ;_ArrayColDelete($mydata2, 3) ;_ArrayDelete($mydata2, 0) _Excel_BookClose($datawb) sleep(1000) $oExcel.Quit() ;;--------------------Add quantity & price to mydata ------------------ ;~ Loop through $mydata2 For $i = 1 To UBound($mydata2)-1 If StringStripWS($mydata2[$i][0],7) = "" Then ContinueLoop ;~ Loop through $mydata For $j = 1 To UBound($mydata)-1 ;~ Check if $mydata[x][column 0] = $mydata2[x][column 0] and set $mydata[x][column 4] to $mydata2[x][column 1] If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][8]=$mydata2[$i][2] ;; Quantity 1>0 If StringStripWS($mydata[$j][0],7) = StringStripWS($mydata2[$i][0],7) Then $mydata[$j][9]=$mydata2[$i][1] ;price Next Next ;---------------------mydata Set on------------------DOne For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][9])="" Then $mydata[$i][9] =0 Next For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][9])>1 Then $mydata[$i][9] =1 Next ;--------------mydata set unwanted items to zero Local $filea = @ScriptDir & "\inc\snagouza.txt" Local $filesn _FileReadToArray($filea, $filesn) For $i = 1 To UBound($filesn) - 1 For $j = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$j][0]) = $filesn[$i] Then $mydata[$j][9] =0 ;_ArrayDelete($mydata, $j) Next Next ;'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ DOne ;=========================================== ;If IsArray($mydata) Then _ArrayDisplay($mydata) ;==================================================== ;--------------------------------create open & save--------------------------- Global $pExcel = _Excel_Open() sleep(1000) Global $oWorkbook = _Excel_BookNew($pExcel, 1) Global $sWorkbook = @ScriptDir & "\agouza-final.csv" _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True) _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) _Excel_BookClose($oWorkbook) $oExcel.Quit() EndFunc

Thank you for all this work it works very nice Here is the full script ;-------------------------------------------------------------------------------------- #AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d #include "encryption\AutoIt\CryptoNG.au3" #include <String.au3> #include <Array.au3> #include <String.au3> #include <Inet.au3> #include <MsgBoxConstants.au3> #include <Constants.au3> $IP_Address = @IPAddress1 $MAC_Address = GET_MAC($IP_Address) Func GET_MAC($_MACsIP) Local $_MAC,$_MACSize Local $_MACi,$_MACs,$_MACr,$_MACiIP $_MAC = DllStructCreate("byte[6]") $_MACSize = DllStructCreate("int") DllStructSetData($_MACSize,1,6) $_MACr = DllCall ("Ws2_32.dll", "int", "inet_addr", "str", $_MACsIP) $_MACiIP = $_MACr[0] $_MACr = DllCall ("iphlpapi.dll", "int", "SendARP", "int", $_MACiIP, "int", 0, "ptr", DllStructGetPtr($_MAC), "ptr", DllStructGetPtr($_MACSize)) $_MACs = "" For $_MACi = 0 To 5 If $_MACi Then $_MACs = $_MACs & ":" $_MACs = $_MACs & Hex(DllStructGetData($_MAC,1,$_MACi+1),2) Next DllClose($_MAC) DllClose($_MACSize) Return $_MACs EndFunc ; The URL of the web page Local $sUrl = "http://www.test.com/mac.php" ; Get the source code of the web page Local $sSource = _INetGetSource($sUrl) aes_cbc_decrypt_example() ;================================================= Func aes_cbc_decrypt_example() Const $ENC_BASE64_MESSAGE = $sSource, _ $SECRET = "Test12345678@A" Local $sDecryptedMessage = "" Local $vEncryptKey = "" Local $xFullEncryptedMessage = Binary(""), _ $xEncryptedMessage = Binary(""), _ $xIV = Binary("") ;Pad short key with null characters to be compatible with PHP's openssl_encrypt() function $vEncryptKey = Binary($SECRET & _StringRepeat(Chr(0), 18)) ;Convert encrypted message from base64 to binary $xFullEncryptedMessage = _CryptoNG_CryptStringToBinary($ENC_BASE64_MESSAGE, $CNG_CRYPT_STRING_BASE64) If @error Then ConsoleWrite("ERROR: " & _CryptoNG_LastErrorMessage() & @CRLF) Return False EndIf ;Parse out IV and encrypted message from full encrypted message $xIV = BinaryMid($xFullEncryptedMessage, 1, 16) $xEncryptedMessage = BinaryMid($xFullEncryptedMessage, 17) ;Decrypt encrypted message $sDecryptedMessage = _CryptoNG_AES_CBC_DecryptData($xEncryptedMessage, $vEncryptKey, $xIV) If @error Then ConsoleWrite("ERROR: " & _CryptoNG_LastErrorMessage() & @CRLF) Return False EndIf Global $pagecontent = $sDecryptedMessage EndFunc If StringInStr($pagecontent, $MAC_Address) Then MsgBox(0, $MAC_Address, "Found "&$MAC_Address) ; ;Do some tasks Else MsgBox(0, $MAC_Address, "Not Found "&$MAC_Address) EndIf

here is the result Viah/Gz3hotsXtll2d1eSrXv3cT70QyuMizS5HBsxV9PgjgsLSA8UHL1AAkHtuJN php code <?php function encrypt($data, $key) { $method = 'aes-256-cbc'; $ivSize = openssl_cipher_iv_length($method); $iv = openssl_random_pseudo_bytes($ivSize); $encrypted = openssl_encrypt($data, $method, $key, OPENSSL_RAW_DATA, $iv); $encryptedData = base64_encode($iv . $encrypted); return $encryptedData; } // Content to be encrypted $content = "00:5F:99:41:C3:00"; // Encryption key $encryptionKey = "Test12345678@A"; $encryptedContent = encrypt($content, $encryptionKey); echo $encryptedContent; ?>

Hello i try to do some kind of security for my script so i test this code bellow and it works fine to get pc mac address and search it in my website if exit then continue running. but for now it all show in plain text with some search i encrypt mac address on php page but i tried to decrypt it on my script but no luck i want to decrypt the website content before searching for mac and i was planning to use code obfuscator is there any free or open source version ? Thanks in advance #include <Array.au3> #include <String.au3> #include <Inet.au3> #include <MsgBoxConstants.au3> #include <Constants.au3> $IP_Address = @IPAddress1 $MAC_Address = GET_MAC($IP_Address) Func GET_MAC($_MACsIP) Local $_MAC,$_MACSize Local $_MACi,$_MACs,$_MACr,$_MACiIP $_MAC = DllStructCreate("byte[6]") $_MACSize = DllStructCreate("int") DllStructSetData($_MACSize,1,6) $_MACr = DllCall ("Ws2_32.dll", "int", "inet_addr", "str", $_MACsIP) $_MACiIP = $_MACr[0] $_MACr = DllCall ("iphlpapi.dll", "int", "SendARP", "int", $_MACiIP, "int", 0, "ptr", DllStructGetPtr($_MAC), "ptr", DllStructGetPtr($_MACSize)) $_MACs = "" For $_MACi = 0 To 5 If $_MACi Then $_MACs = $_MACs & ":" $_MACs = $_MACs & Hex(DllStructGetData($_MAC,1,$_MACi+1),2) Next DllClose($_MAC) DllClose($_MACSize) Return $_MACs EndFunc ; The URL of the web page Local $sUrl = "http://www.Test.com/mac.php" ; Get the source code of the web page Local $sSource = _INetGetSource($sUrl) If StringInStr($sSource, $MAC_Address) Then MsgBox(0, $MAC_Address, "Found "&$MAC_Address) ; ;Do some tasks Else MsgBox(0, $MAC_Address, "Not Found "&$MAC_Address) EndIf <?php function encrypt($data, $key) { $method = 'aes-256-cbc'; $ivSize = openssl_cipher_iv_length($method); $iv = openssl_random_pseudo_bytes($ivSize); $encrypted = openssl_encrypt($data, $method, $key, OPENSSL_RAW_DATA, $iv); $encryptedData = base64_encode($iv . $encrypted); return $encryptedData; } // Content to be encrypted $content = "00:5F:99:41:C3:00"; // Encryption key $encryptionKey = "test12345678"; $encryptedContent = encrypt($content, $encryptionKey); echo $encryptedContent; ?>

Thanks alot it work perfect ❤️

unfortunately, it didnt work with me No errors but it doesnt insert price value to col4

Hello, i have 2 arrays $mydata & $mydata2 i want to search on $mydata with code and if the code exist insert offer price to Col4 here what i have done but with no luck with looping it using $mydata2 array values For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][0]) = 33004 Then $mydata[$i][4]=10000 Next Thanks alot $mydata $mydata2 #region --- Internal functions Au3Recorder Start --- Func _Au3RecordSetup() Opt('WinWaitDelay',100) Opt('WinDetectHiddenText',1) Opt('MouseCoordMode',0) EndFunc #AutoIt3Wrapper_Version=Beta #AutoIt3Wrapper_Res_Fileversion=1.0.0.0 #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #Include<file.au3> #include <INet.au3> ; needed for get source (hmtl) #include <String.au3> ; needed for stringbetween #include<Array.au3> ; #include <Date.au3> #include <MsgBoxConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #include <OutlookEX 1.7.0.1/OutlookEx.au3> HotKeySet("{DEL}", "_Quit") ; quit option = DEL ;==================================== Global $brname="agouza" Global $htfilename="agouzaold.htm" Global $exfilename= "agouzaold.xls" Global $path = "D:\Desktop\Autoit project\" ;==================================== _movetosheet() Func _movetosheet() Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\agouzaold.xls") $datawb.worksheets("agouzaold").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata = _Excel_RangeRead($datawb, Default, Default ) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 2) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 4) _ArrayColDelete($mydata, 4) _ArraySwap($mydata, 0, 2, True) _ArraySwap($mydata, 0, 1, True) _ArraySwap($mydata, 0, 3, True) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayColInsert($mydata, 4) _Excel_BookClose($datawb) sleep(1000) Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Data\Offers.xlsx") $datawb.worksheets("Sheet1").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata2 = _Excel_RangeRead($datawb, Default, Default ) _ArrayColDelete($mydata2, 3) _Excel_BookClose($datawb) sleep(1000) If IsArray($mydata2) Then _ArrayDisplay($mydata2) ;--------------------------------------------------------- ;-----Here For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][0]) = 33004 Then $mydata[$i][4]=10000 Next ;-------------------------------------------------------------- For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][3]) = 0 Then _ArrayDelete($mydata, $i) Next Local $filea = @ScriptDir & "\inc\snagouza.txt" Local $filesn _FileReadToArray($filea, $filesn) For $i = 1 To UBound($filesn) - 1 For $j = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$j][0]) = $filesn[$i] Then _ArrayDelete($mydata, $j) EndIf Next Next ;-------------------------------- If IsArray($mydata) Then _ArrayDisplay($mydata) EndFunc

Thank you so much that's exactly what i needed

Hello i need help and explanation with loop through 2 arrays here is my code and it works but without loop it just read only 30 lines from sn.txt and i use it line by line to delete rows that includes that values all i need is find the cell with specific code that will be written in sn.txt and delete the entire row and loop it till the end of file Thanks and appreciate the earlier help of yesterday Local $oExcel =_Excel_Open() $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\agouzaold.xls") $datawb.worksheets("agouzaold").select $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count $mydata = _Excel_RangeRead($datawb, Default, Default ) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 2) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 4) _ArrayColDelete($mydata, 4) _ArraySwap($mydata, 0, 2, True) _ArraySwap($mydata, 0, 1, True) _ArraySwap($mydata, 0, 3, True) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayDelete($mydata, UBound($mydata) - 1) _Excel_BookClose($datawb) ;-------------------------------- For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][3]) = 0 Then _ArrayDelete($mydata, $i) Next ;-------------------------------- $filea = "sn.txt" Global $filesn[30] _FileReadToArray($filea, $filesn) For $i = UBound($mydata) - 1 To 1 Step -1 If Number($mydata[$i][0]) = $filesn[1] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[2] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[3] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[4] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[5] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[6] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[7] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[8] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[9] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[10] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[11] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[12] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[13] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[14] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[15] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[16] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[17] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[18] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[19] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[20] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[21] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[22] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[23] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[24] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[25] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[26] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[27] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[28] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[29] Then _ArrayDelete($mydata, $i) If Number($mydata[$i][0]) = $filesn[30] Then _ArrayDelete($mydata, $i) Next ;-------------------------------- If IsArray($mydata) Then _ArrayDisplay($mydata)

Thanks for quick reply and the solution it works perfect

Hello i need some help to remove some rows from the array before i insert it to new excel sheet after i did search all i found was deleting entire empty rows but in my case i want to delete rows only if the "QTY" cell is empty Thanks in advance Func _movetosheet() Local $oExcel =_Excel_Open() sleep(1000) $datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\agouzaold.xls") sleep(1000) $datawb.worksheets("agouzaold").select sleep(1000) $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count sleep(1000) $mydata = _Excel_RangeRead($datawb, Default, Default ) sleep(1000) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 0) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 1) _ArrayColDelete($mydata, 2) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 3) _ArrayColDelete($mydata, 4) _ArrayColDelete($mydata, 4) sleep(1000) _ArraySwap($mydata, 0, 2, True) _ArraySwap($mydata, 0, 1, True) _ArraySwap($mydata, 0, 3, True) sleep(1000) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) _ArrayDelete($mydata, 0) sleep(1000) _ArrayDelete($mydata, UBound($mydata) - 1) _ArrayDelete($mydata, UBound($mydata) - 1) sleep(1000) ;If IsArray($mydata) Then _ArrayDisplay($mydata) _Excel_BookClose($datawb) sleep(1000) ;-------------------------------- Global $pExcel = _Excel_Open() sleep(1000) Global $oWorkbook = _Excel_BookNew($pExcel, 1) sleep(1000) Global $sWorkbook = @ScriptDir & "\agouza.xlsx" sleep(1000) _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) sleep(1000) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mydata, "a1", Default, True) sleep(1000) _Excel_BookSaveAs($oWorkbook, $sWorkbook, $xlWorkbookDefault, True) Sleep(3000) _Excel_BookClose($oWorkbook) Sleep(3000) $oExcel.Quit() sleep(2000) EndFunc

Done i solved it with if statement