insert data to excel cell when it find the next cell equal to yesterday date

[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

expandcollapse popup

#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

 

Edited October 9, 2024 by kemo1987