Jump to content

Compare values in Excel


Go to solution Solved by ioa747,

Recommended Posts

Dear Hivemind, 

 

I am looking for a bit of help. 

As part of a project I need to go through an excel file and use some things from there in a different program. There are several operations that the script will need to do, so I am using a GUI to call on different functions. 

At the moment I am looking into a way to get a number for the amount of repeats in a row. 

So if there is just one and no repeats, it should return 1

If there are 4 in a row, it should return 4 etc. 

At the moment I am stuck as I keep getting an error that a variable needs to be an Object. I am still trying to wrap my head about the language. Any help that you guys can provide would be appreciated. 

 

At the moment I have the script as follows:

#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"

; Create application object and open an example workbook
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

Local $vBuyerID  ; Formula of the read cell
Local $vTransID
Local $vOrderID
Local $vItemID
Local $vAmount
Local $vCurr
Local $vReason
Local $oWorkbook  ; Object of the Excel workbook to process
Global $iLine = 3    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2

$vBuyerID = $oWorkbook.ActiveSheet.Cells($iLine,1).FormulaR1C1
$vTransID = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$vItemID = $oWorkbook.ActiveSheet.Cells($iLine,4).FormulaR1C1
$vAmount = $oWorkbook.ActiveSheet.Cells($iLine,9).FormulaR1C1
$vCurr = $oWorkbook.ActiveSheet.Cells($iLine,8).FormulaR1C1
$vReason = $oWorkbook.ActiveSheet.Cells($iLine,7).FormulaR1C1
$iLine2 = $iLine + $iAdd
$vTransID2 = $oWorkbook.ActiveSheet.Cells($iLine2,2).FormulaR1C1

UserInterFace()

Func UserInterFace()
Local $vBuyerID  ; Formula of the read cell
Local $vTransID
Local $vOrderID
Local $vItemID
Local $vAmount
Local $vCurr
Local $vReason
Local $oWorkbook  ; Object of the Excel workbook to process
Global $iLine = 3    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2
    Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
                                Local $idButton_Add = GUICtrlCreateButton("Process", 10, 10)
                                Local $idButton_Close = GUICtrlCreateButton("Exit", 210, 180)
                                Local $idButton_MacNote = GUICtrlCreateButton("Place MacNote", 210, 10)

        GUISetState(@SW_SHOW, $hGUI)

    Local $iPID = 0
    
            ; Loop until the user exits.
        While 1
                Switch GUIGetMsg()
                        Case $GUI_EVENT_CLOSE
                                ExitLoop
                        Case $idButton_Add
                                Looptest()
                        Case $idButton_MacNote
                                MsgBox(4096+16, "Line", "Line number  " & $Line "runs until  " & $Line2)
                        Case $idButton_Close
                                MsgBox($MB_SYSTEMMODAL, "", "the closing button has been clicked", 2)
                                Exit
                EndSwitch
        WEnd

        ; Delete the previous GUI and all controls.
        GUIDelete($hGUI)
        
        _OpenCV_Shutdown();Closes DLLs

        ; Close the Notepad process using the PID returned by Run.
        If $iPID Then ProcessClose($iPID)
EndFunc   ;==>Example


Looptest()

Func Looptest()

Local $vTransID
Local $oWorkbook  ; Object of the Excel workbook to process
Local $iAdd = 1
Local $iLine2
$vTransID = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$iLine2 = $iLine + 1

    If $TransID = $TransID2 then $Line2 = $Line Else
                Do
                    $iLine2 = $iLine2 + 1 ; Or $i += 1 can be used as well.
                Until $TransID <> $TransID2 

EndFunc

 

Link to post
Share on other sites
  • Replies 43
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

anyway everything is in steps, it just runs. you can  add more control take a look ; https://www.autoitscript.com/forum/topic/209362-compare-values-in-excel/page/2/?tab=comments#comment-15119

You could have a look at the CountIf function. Insert this function with your script and let do Excel the calculation, then extract the results.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - 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

Sadly that won't work. 

What I need is to get the order ID from excel, orders can have between 1 and 30 items. 

If there is 1, I need to do thing in a program and fill in a number from that line in a specific place. 

If there are more, then it needs to do the same initial thing and then repeat entering numbers on successive lines in a specific place as above. 

For example

line 4 is order number 1234 value 5

line 5 is order number 1235 value 6

line 6 is order number 1235 value 3

line 7 is order number 1235 value 8

line 8 is order number 1237 value 10

line 9 is order number 1237 value 100

What I want to do it open the script, press a button, do mouse move and clicks (can't control directly), fill in the value in a specific field. Once done reset the program and do it again. I also do not want to fully automate it. 

If there are multiple values per order number I need to do the mouse moves and clicks first and then I can enter the value, press tab and enter the next and so on. 

I hope that makes it clearer what I am trying to achieve. 

Link to post
Share on other sites

The code in question is this (it has been trimmed down (the trimming down helped as I am no longer getting the error):

#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"

; Create application object and open an example workbook
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 & "\test2.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

Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Global $iLine = 2    ; Line number to read
Local $iWidthCell = 350
Local $iLine2

$vBuyerID = $oWorkbook.ActiveSheet.Cells($iLine,1).FormulaR1C1
$vItem = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$vAmount = $oWorkbook.ActiveSheet.Cells($iLine,4).FormulaR1C1
$iLine2 = $iLine + 1
$vOrderID2 = $oWorkbook.ActiveSheet.Cells($iLine2,3).FormulaR1C1

UserInterFace()

Func UserInterFace()
Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Global $iLine = 2    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2
    Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
                                Local $idButton_Add = GUICtrlCreateButton("Process", 10, 10)
                                Local $idButton_Close = GUICtrlCreateButton("Exit", 210, 180)
                                Local $idButton_MacNote = GUICtrlCreateButton("Lines", 210, 10)

        GUISetState(@SW_SHOW, $hGUI)

    Local $iPID = 0
    
            ; Loop until the user exits.
        While 1
                Switch GUIGetMsg()
                        Case $GUI_EVENT_CLOSE
                                ExitLoop
                        Case $idButton_Add
                                Looptest()
                        Case $idButton_MacNote
                                MsgBox($MB_SYSTEMMODAL, "", $iLine & " And " & $iLine2)
                        Case $idButton_Close
                                MsgBox($MB_SYSTEMMODAL, "", "the closing button has been clicked", 2)
                                Exit
                EndSwitch
        WEnd

        ; Delete the previous GUI and all controls.
        GUIDelete($hGUI)
        
        _OpenCV_Shutdown();Closes DLLs

        If $iPID Then ProcessClose($iPID)
EndFunc   ;==>Example


Looptest()

Func Looptest()

Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Local $iLine2
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$iLine2 = $iLine + 1
$vOrderID2 = $oWorkbook.ActiveSheet.Cells($iLine2,3).FormulaR1C1

    If $vOrderID = $vOrderID2 Then 
        $Line2 = $iLine 
            Else
                Do
                    $iLine2 += 1
                Until $vOrderID <> $vOrderID2 
    EndIf
EndFunc

I am no longer getting an error, I am not getting the result I want either sadly. 

I should be getting for the first time clicking the process button and then pressing lines "2 and 3".  It gives "2 And"

The file I am checking has been attached.  
 

 

test2.xlsx

Edited by Nilkimas
Fixed Excel file
Link to post
Share on other sites

Sorry to say but your code is badly written.  I hardly can understand what you are trying to achieve here.  So base on the excel file you gave us can you explain what you want to calculate ?  Is it possible that you want to sum the number of 1s in column C by pressing some buttons, then sum the number of 2s and so on ?

Link to post
Share on other sites

I just started writing in AutoIt, so that is why it is badly written. Still in the early stages in figuring out how it works.

Also I am not a programmer, just someone who is using building blocks he understands to build something that does the job.  

What I want the script to do is enter the value of an orderid in a field in a different program. 

If the orderid is the same I need there will be multiple value fields in the other program and the script will need to write one line at the time until the orderid's are no longer matching. 

I hope that makes it more clear. 

I have a working script in AutoHotKey, but I need to recreate it in AutoIt. 

Edited by Nilkimas
Added clarification.
Link to post
Share on other sites

Clear as mud.  No idea what you are talking about.  What is this "different program" ?

I am really trying to understand what you want so I can help you, but at this stage I simply cannot do anything without a proper explanation.

I suspect English is not your native language, maybe using google translate could help ?

Link to post
Share on other sites

I think my English is quite ok actually. 

What I want to do is the following: 
Go through the excel file, one OrderID at the time. The OrderID can have between 1 and 30 items. 

I need to enter the value of those items in a field in (lets call it) Application X, the number of fields for the values is dependent on the number of items in the order. 

If there is 1 item, it will be 1 field, if there are 10 there will be 10. 

Application X has been written by developers who are evil (in my humble opinion) and they make the life of users like me difficult. That is why I am making this script to help me (and my team) process orders a lot quicker than I can do by hand. There is a bit of scrolling involved and clicking of buttons, once per order, not per item. I can not bypass Application X sadly. 

In my AutoHotKey script I used the following: 

;for multiple items in the same order, we do this check and decide on which version to use, either entering a single item or a multi-item order
LineCheck := Line +1 ;this is declaring a variable
A3 := test.ActiveSheet.cells.(Line,3).FormulaR1C1
Check := test.ActiveSheet.cells.(LineCheck,3).FormulaR1C1

If (A3 != Check) ;this would be a single item per order
{
Goto Step4
}
if (A3 == Check) ;multi item order
{
Goto Step5
}
If A3 = ""
{
MsgBox All done at line %line%
}
else return

Step5: ;This would be similar to Func in AutoIt. 
FirstM := Line ;again variable
FirstM0 := Line ;this sets the base for the entries later
Loop { ;as long as it matches it goes on until it hits 1000
Line3 := Line +1
A3 := test.ActiveSheet.cells.(Line,3).FormulaR1C1 ;etc
Check := test.ActiveSheet.cells.(Line3,3).FormulaR1C1
if (A3 != Check) ;if it no longer matches, it goes on to the next step. 
Goto NoMoreMatches
Line +=1
}
until (Line2=1000)
goto Error

NoMoreMatches: 
Var3 := Line - FirstM +1 ;this is the loop count for later

It first checks if there is a repeat of the OrderID, if not the script does the clicking and enters the value into a field in Application X. 

If there are multiple items, the script checks how many there are. That is the value that is used in a loop function later. The scrolling and clicking will be the same for both single item and multi item orders. The loop function will repeat the read/write into the fields as required, luckily a simple tab (or 2) is enough to advance to teh next field and fill it in. 

I don't think I can explain it more clearly. 

Link to post
Share on other sites

maybe it helps  :idea:

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

Local $OrderId, $index = 2

While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop
    To_ApplicationX($OrderId)
    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

Link to post
Share on other sites

It helps  little, as far as I understand this will keep writing $OrderID until the field is empty. 

That is only part of what I need sadly, I need to compare the order ID on line 2 with the one on line 3. If they are the same then I can do them one after another. 

If they are not the same I need to do the one on line 2 only. Then reset Application X before I can go onto line 3. 

Link to post
Share on other sites

 something like this ? :huh2:

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

Local $OrderId, $OrderNext, $Reset = False, $index = 2

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)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & @CRLF)
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

Link to post
Share on other sites

:oops:  so is better

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

Local $OrderId, $OrderNext, $Reset = False, $index = 2

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)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & @CRLF)
        $index += 1
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

Link to post
Share on other sites

try this

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

Local $OrderId, $OrderNext, $Reset = False, $index = 2

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("reset Application = " & $Reset & @CRLF)
;~      $index += 1
    EndIf

    $index += 1
WEnd

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

 

Edited by ioa747
Link to post
Share on other sites
  • 3 weeks later...

Sadly this isn't what I was looking for. 

I added to the code as I want to use a GUI in order to run the script. 

#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"

Opt("TrayAutoPause", 0)

; Create application object and open an example workbook
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 & "\test2.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 = 1
$vOrderID = $oWorkbook.ActiveSheet.Cells($Index,3).value
$vAmount = $oWorkbook.ActiveSheet.Cells($Index,4).value

Local $iOKButton = GUICtrlCreateButton("OK", 70, 50, 60)
GUICtrlSetOnEvent($iOKButton, "OKButton")

GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton")


GUICtrlCreateLabel("Order ID "&$vOrderID, 10, 100)
GUICtrlCreateLabel("Line1 "&$Index, 10, 130)

GUISetState(@SW_SHOW, $hGUI)

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

Func OKButton()
    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
            WinActivate ("Document1")
            Send("reset Application = " & $Reset & @CRLF)
;~      $index += 1
    EndIf

        $index += 1
    WEnd
EndFunc

Func CLOSEButton()
        ; Note: at this point @GUI_CtrlId would equal $GUI_EVENT_CLOSE,
        ; @GUI_WinHandle will be either $hMainGUI or $hDummyGUI
        If @GUI_WinHandle = $hGUI Then
                MsgBox($MB_OK, "GUI Event", "You selected CLOSE in the main window! Exiting...")
                Exit
        EndIf
EndFunc   ;==>CLOSEButton 

Func To_ApplicationX($RepeatTimes, $Line)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    WinActivate ("Document1")
    Send("from line " & $Line & " [" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

It is giving me the following output: 

From line 1 [orderId]

Reset Application = True

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

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

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

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

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,

 

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

On first press of Ok: 

10 (as the orderID)

Then the script will do other things, followed by writing

1496

10200

Second press of OK:

20 

script does things

5

100020

Third press: 

30

Does things 

5

Fourth press: 

40

Does things

134.3

Fifth press:

50

Does things

123.45

98.21

23.79

 

I hope that this makes it more clear. 

The 'does things' part is mainly to press buttons in Application X and wait for things to load. 

Link to post
Share on other sites

:guitar:

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

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("reset Application = " & $Reset & " --> ")
            For $i = $iFrom to $index
                ConsoleWrite($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 4).Value & "; ")
            Next
            ConsoleWrite( "" & @CRLF)
            $iFrom = $i

;~      $index += 1
    EndIf

    $index += 1
WEnd

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

 

Edited by ioa747
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.

×
×
  • Create New...