Compare values in Excel

[ioa747]

Example ?

 

test2.xlsx 11.66 kB · 8 downloads

Edited December 29, 2022 by Nilkimas

 

 

[Nilkimas]

@GMK 

No, as I need to enter the information in a different application. So changing the data won't do anything. 
As previously mentioned I receive an excel file that contains data on OrderIDs, Values of items etc. 
What I need to do is enter the values of the items in Application X, but there can be between 1 and 30 items per OrderID. 

So I need to be able to compare one line to the next, know the amount of items per OrderID. 

[ioa747]

On 1/16/2023 at 7:20 PM, ioa747 said:

 

you didn't say anything it wasn't OK ?

Quote

>Running:(3.3.16.1)
from line 2 [10] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
from line 3 [10] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
reset Application = True --> 1496; 10200;
from line 4 [20] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
from line 5 [20] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
reset Application = True --> 5; 100020;
from line 6 [30] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
reset Application = True --> 5;
from line 7 [40] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
reset Application = True --> 134.3;
from line 8 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,
from line 9 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,
from line 10 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,
reset Application = True --> 123.45; 98.21; 23.79;
 

 

Edited January 17, 2023 by ioa747

[Nilkimas]

Sadly it is not what I wanted. 
So at the moment it is just counting 1 to the order number, that is really not what I need. 
I mentioned in my other reply that the wanted output would be:
 

Instead, based on the same initial file I would like to get: 

On first press of Ok: 

Send 10 (as the orderID)

Then I need to add in waits, mouse clicks etc. 

Send 1496

tab

send 10200

stop

Second press of OK:

20 (send next orderID)

script does things

send 5

tab

send 100020

stop 

 

and so forth

At the moment it is just sending everything in one go and that is not what will work for Application X. 
I need to have it in steps, 1 order ID at the time. 

[ioa747]

2 hours ago, Nilkimas said:

I need to have it in steps, 1 order ID at the time. 

anyway everything is in steps, it just runs. you can  add more control take a look

expandcollapse popup

; https://www.autoitscript.com/forum/topic/209362-compare-values-in-excel/page/2/?tab=comments#comment-1511933

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xlsx") ; open file

Run("notepad.exe") ; Run Notepad
Local $hNPad = WinWait("[CLASS:Notepad]")

Local $OrderId, $OrderNext, $Reset = False, $index = 2
Local $iFrom = $index
While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    To_ApplicationX($OrderId, $index)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("-- " & $OrderId & " ;")
        WinActivate($hNPad)
        kSend($OrderId) ;Send 10 (as the orderID) <-------
        kSend("{TAB}") ;this is extra from me to nice output <-------
            Sleep(100) ;Then I need to add in waits, mouse clicks etc. <-------
            MouseClick("left", 0, $OrderId * 10, 1) ;Then I need to add in waits, mouse clicks etc. <-------
            WinActivate($hNPad)

            For $i = $iFrom to $index
                ConsoleWrite($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 4).Value & "; ")
                kSend($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 4).Value) ;Send 1496
                if $i < $index or $i = $index Then kSend("{TAB}") ;tab <-------
            Next

            ;kSend("stop") ;stop ?? <-------
            kSend("{ENTER}") ;stop ?? <-------

            ConsoleWrite( "" & @CRLF)
            $iFrom = $i
    EndIf

    $index += 1
WEnd

kSend("{ENTER}")
kSend(" :) Happy? ")

Exit
;----------------------------------------------------------------------------------------
Func To_ApplicationX($RepeatTimes, $Line)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("from line " & $Line & " [" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX
;----------------------------------------------------------------------------------------
Func kSend($sKey, $iDelay = 120) ;increase $iDelay if need more time <-------
        Send($sKey)
        Sleep($iDelay)
EndFunc   ;==>kSend
;----------------------------------------------------------------------------------------

 

Edited January 18, 2023 by ioa747

[Nilkimas]

Almost there

It is working quite well. The Stop that I mentioned is basically me needing to manually submit what I need to do in Application X and reset it... 

The manual submission is due to the fact that whenever the 'Evil Developers' break the page again it doesn't just keep putting numbers everywhere and potentially make unwanted changes. 

The resetting the page is something that needs to be done in between OrderIDs. If there was an automated bulk way of doing it, we would have gone for it.

But thank you very much. I still need to wrap my head around this scripting language. 

[ioa747]

[Nilkimas]

Thank you @ioa747 for the code, I have made it work in the end. 

Edit: Mostly works... 
Sadly there is an issue I am running into. (And yes my code is poorly written, I know)
I am getting the MsgBox($MB_SYSTEMMODAL, "Check", "Next Line will be: " &$iFrom) popping up for each line, even for the repeated Order IDs. 

I would need to have it enter all the amounts and then pop the MsgBox. 

Any guidance you can give me?

expandcollapse popup

#include <WindowsConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#AutoIt3Wrapper_UseX64=n ; In order for the x86 DLLs to work
#include "GUIConstantsEx.au3"
#include "OpenCV-Match_UDF.au3"
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>

_OpenCV_Startup();loads opencv DLLs

HotKeySet("{ESC}", "_Terminate")

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

Opt("GUIOnEventMode", 1) ; Change to OnEvent mode

Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
;Local $vOrderID 
;Local $vAmount 
Local $Index = 2
;$OrderID = $oWorkbook.ActiveSheet.Cells($Index,3).value
;$Amount = $oWorkbook.ActiveSheet.Cells($Index,9).value

Local $iOKButton = GUICtrlCreateButton("OK", 70, 50, 60)
GUICtrlSetOnEvent($iOKButton, "Process")
GUICtrlCreateLabel("Start from:", 8, 72, 59, 17)
Local $index = GUICtrlCreateInput("3", 72, 72, 57, 21)

; create labels. it's generally not necessary to set the returned handle
;  to a variable on static label controls you know you won't be interacting with
GUICtrlCreateLabel("Time:", 8, 8, 30, 17)
;GUICtrlCreateLabel("Total pay:", 8, 32, 51, 17)
; create read-only status boxes
$iNextL = GUICtrlCreateInput("", 72, 8, 121, 21, BitOR($ES_AUTOHSCROLL,$ES_READONLY))
;$iAmount = GUICtrlCreateInput("", 72, 32, 121, 21, BitOR($ES_AUTOHSCROLL,$ES_READONLY))

GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton")

GUISetState(@SW_SHOW, $hGUI)

While 1
        Sleep(30) ; Sleep to reduce CPU usage
WEnd

Func Process()
Local $OrderId, $OrderNext, $Reset = False, $index=3
Local $iFrom = $index
While 1
    $Amount = $oWorkbook.ActiveSheet.Cells($iFrom, 9).value
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    
    If $OrderId <> $OrderNext Then
        WinActivate("[Title:Application_X]","")
        Call("SetUp")
        Send($OrderId) ;Send 10 (as the orderID) <-------
        Sleep(100)
        Send("{ENTER}")
        Call ("MoveMountain")               

            For $i = $iFrom to $index
                ;ConsoleWrite($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 4).Value & "; ")
                kSend($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 9).Value) 
                kSend ("{DEL 3}")
                if $i < $index or $i = $index Then kSend("{TAB}") ;tab <-------
            Next
            $iFrom = $i
    EndIf
    $index += 1
    
Call("CleanUp", $iFrom)
WEnd

EndFunc ;==> Process

Func SetUp()
;MsgBox($MB_SYSTEMMODAL, "Step1", "Beginning of SetUp")
WinActivate("[Title:Application_X]","")
$Match1 = _MatchPicture(@ScriptDir&"\Match\OrderEnter.PNG", 0.70,False,10,500) ;Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
if Not @error Then
_ClickMouse($Match1)
EndIf
            
EndFunc ;==> SetUp

Func MoveMountain()
;MsgBox($MB_SYSTEMMODAL, "Step2", "Beginning of MoveMountain")


$Match2 = _MatchPicture(@ScriptDir&"\Match\Arrows.PNG", 0.70,False,10,500);Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
                                if Not @error Then
                                ;_MarkMatch($Match2)
                                _ClickMouse($Match2) ;Debugging: Draws a rect on the screen/coordinates of the match to show the user where the match was found
EndIf
            $Match3 = _MatchPicture(@ScriptDir&"\Match\Order.PNG", 0.70,False,10,500);Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
                                if Not @error Then
                                ;_MarkMatch($Match3)
                                _MouseMover2($Match3) ;Debugging: Draws a rect on the screen/coordinates of the match to show the user where the match was found     
EndIf
            Local $aPos = MouseGetPos()
            local $MouseX = $aPos[0] - 155
            local $MouseY = $aPos[1] + 14
            Sleep (50)
            MouseClick($MOUSE_CLICK_LEFT, $MouseX, $MouseY, 1, 0)
            Send("{PGDN}")
            sleep (100)
            Send("{PGDN}")
            sleep (100)
            Send("{PGDN}")
            sleep (100)
            Send("{PGDN}")
            
            $Match4 = _MatchPicture(@ScriptDir&"\Match\Initiate.PNG", 0.70,False,10,500);Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
                                if Not @error Then
                                _ClickMouse($Match4) ;Debugging: Draws a rect on the screen/coordinates of the match to show the user where the match was found  
EndIf
            Sleep (100)
            Send("{PGDN}")
            Sleep (500)
            Send("{PGDN}")
            $Match5 = _MatchPicture(@ScriptDir&"\Match\Reason.PNG", 0.70,False,10,500);Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
                                if Not @error Then
                                _ClickMouse($Match5) ;Debugging: Draws a rect on the screen/coordinates of the match to show the user where the match was found  
EndIf
            Send("{TAB 2}")
            Send("{SPACE}")
            Send("{TAB 2}")
            Send("{t}")
            Send("{TAB}")
            Send("{t}")
            Send("{TAB 2}")

EndFunc ;==> MoveMountain

Func CleanUp($iFrom, $index)

;$Amount = $oWorkbook.ActiveSheet.Cells($iFrom, 9).value

MsgBox($MB_SYSTEMMODAL, "Check", "Next Line will be: " &$iFrom)

GUICtrlSetData($iNextL, $iFrom)
;GUICtrlSetData($iAmount, $Amount)
    WinActivate("[Title:Application_X]","")
    Send("{PGUP 6}")
    $Match6 = _MatchPicture(@ScriptDir&"\Match\Reset.PNG", 0.70,False,10,500);Try to find the match picture on the screen. Number of tries: 10, Sleep between each try: 500ms. 
                                if Not @error Then
                                _ClickMouse($Match6) ;Debugging: Draws a rect on the screen/coordinates of the match to show the user where the match was found  
    EndIf
    _MouseMover2($Match6)
    Local $aPos = MouseGetPos()
            local $MouseX2 = $aPos[0]
            local $MouseY2 = $aPos[1] + 60
            Sleep (50)
            MouseClick($MOUSE_CLICK_LEFT, $MouseX2, $MouseY2, 1, 0)
            Send ("{LEFT 15}")
            Send ("{DEL 16}")

EndFunc ;==> CleanUp

Func _MouseMover2 ($Coordinates)
    Mouseup("")
    BlockInput(1)
    MouseMove(($Coordinates[0] + $Coordinates[2]) / 2, ($Coordinates[1] + $Coordinates[3]) / 2,0) 
    BlockInput(0)
EndFunc ;==>_MouseMover2

Func kSend($sKey, $iDelay = 100) ;increase $iDelay if need more time <-------
        Send($sKey)
        Sleep($iDelay)
EndFunc   ;==>kSend

Func _Terminate()
;MsgBox($MB_SYSTEMMODAL, "Alert", "Something Fucked Up")
        Exit
EndFunc   ;==>_Terminate

 

Edited January 27, 2023 by Nilkimas
Not quite fixed

[ioa747]

you here have to move mountains !

[Nilkimas]

I like to have fun with my code Life is serious enough as it is. 

[ioa747]

1512444-Compare-values-in-Excel.au3"(48,47) : error: CLOSEButton(): undefined function.

i thing  GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton")

must be GUISetOnEvent($GUI_EVENT_CLOSE, "_Terminate")

as for the MsgBox($MB_SYSTEMMODAL, "Check", "Next Line will be: " &$iFrom) popping up for each line

this is normal is in the loop, can you not comment out?

[Nilkimas]

No I can't, I need to have a stop in between. 
So after it fills in the amounts in the loop, it needs to stop. 
I need to do 1 thing by hand, then go to CleanUp() and SetUp() again ready for the next loop.  

[ioa747]

line 29 Local $Index = 2

line 36 Local $index = GUICtrlCreateInput("3", 72, 72, 57, 21)

I can not make sense of it

[ioa747]

however, it is very difficult to keep track of all this

[Nilkimas]

Line 29 can be commented out

Line 36 is for when the script messes up or something else interrupts things and you have to start over, I wanted to create an input field from where the script would start. That is still a work in progress, can be ignored for now. 

[ioa747]

then it's all about finding the right position for CleanUp. it's correct?

Edited January 27, 2023 by ioa747

[Nilkimas]

That is it, indeed. 

[ioa747]

try in line 72 under Call ("MoveMountain")

Edited January 27, 2023 by ioa747

[ioa747]

or line 79 under Next

[Nilkimas]

Sadly neither of those worked. 
The MsgBox doesn't show up at all. 

I tried to add an If...Then before the MsgBox to check if $iFrom matched $index, but that didn't work either.