Values fed to an array in a loop

[zirjeo]

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
For ticker in XL.Range("A3:A4")  ; 1st loop grabs cell A3 from excel then 2nd loop grabs A4
{
    WinWaitActive, ************** ; Activates Window
    Click, 90, 47 Left, 1  ; Actives symbol field in inspector
    SendRaw, % ticker.Text  ; Pastes Ticker grabbed from Excel A3 then A4 on 2nd loop
    Send, {Control Down}{a}{Control Up}
    Send, {Control Down}{c}{Control Up}  ; Copies inspector Output
    result := [%clipboard%,%clipboard%]  ; Saves clipboard/symbol field
    For cell in XL.Range("A1","A2")
    {
        cell.Value :=result[A_Index]  ; Pastes Inspector Output into Excel cells A1 & A2
    }
}

Newbie here I'm hitting a road block on 2nd to last line...
result := [%clipboard%,%clipboard%]

I'm looking for the array to be [clipboard value from 1st loop,clipboard value from 2nd loop]

But on 1st loop I get [clipboard value from 1st loop,clipboard value from 1st loop]
and after 2nd loop it ends up [clipboard value from 2nd loop,clipboard value from 2nd loop]

[TLM]

Remove the percent signs from around the clipboard variable.
Also you should only need to assign the result key once result := [ clipboard ]

Edit:
Now that I look again, to grab 2 cell values, you need to structure your code slightly different:

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
	
result := [] ; declare array
For ticker in XL.Range("A3:A4")  ; 1st loop grabs cell A3 from excel then 2nd loop grabs A4
{
    WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
    Click, 90, 47 Left, 1  ; Actives symbol field in inspector
    SendRaw, % ticker.Text  ; Pastes Ticker grabbed from Excel A3 then A4 on 2nd loop
    Send, {Control Down}{a}{Control Up}
    Send, {Control Down}{c}{Control Up}  ; Copies inspector Output
    result.push( clipboard )  ; array.push() appends values to the end of an array
}

For cell in XL.Range("A1","A2") ; remove 2nd loop from 1st
{
    cell.Value := result[A_Index]  ; Pastes Inspector Output into Excel cells A1 & A2
}

Or if you're only going to use A1 and A2:

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")
	
For ticker in XL.Range("A3:A4")  ; 1st loop grabs cell A3 from excel then 2nd loop grabs A4
{
    WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
    Click, 90, 47 Left, 1  ; Actives symbol field in inspector
    SendRaw, % ticker.Text  ; Pastes Ticker grabbed from Excel A3 then A4 on 2nd loop
    Send, {Control Down}{a}{Control Up}
    Send, {Control Down}{c}{Control Up}  ; Copies inspector Output
    
    XL.Range( "A" A_Index ).Value := Clipboard ; assigns the value of the current clipboard to the appropriate cell
}

I'm assuming `pasting` ticker values from A3 & A4 into inspector is performing some process on them.
If not, you can go:XL.Range( "A" A_Index ).Value := ticker.Text or XL.Range( "A1:A2" ).value := XL.Range( "A3:A4" ).value

[zirjeo]

hah! It worked, that's amazing. Not exactly sure mechanics behind it but thanks a ton!

[zirjeo]

Oh just read your update ya there's more code in there that clicks a go button and does some other things to get an output that I get on the clipboard, will actually end up having 50 maybe 100 elements(ticker symbols) or who knows the possibilities not sure yet, was just using 2 elements in the array to keep it simple.

[zirjeo]

TLM looks like I'm missing one more thing. Tried to start a new post but it seems like I'm being sent on a wild goose chase by the responder, or it's possible/more likely I'm just not understanding them.

I'm simply just trying to add another column in excel into the mix, so say B3 and B4 for example
So Originally the relevant part of the code was..

Code: Select all

Click, 90, 47 Left, 1  ; Actives symbol field in inspector
SendRaw, % ticker.Text  ; Pastes Ticker grabbed from Excel A3, then A4 on 2nd loop

And now looking for..

Code: Select all

Click, 90, 47 Left, 1  ; Actives symbol field in inspector
SendRaw, % ticker.Text  ; Pastes Ticker grabbed from Excel A3, then A4 on 2nd loop
;and then adding below code
Click, 110, 60 Left, 1  ; Actives symbol field in inspector
SendRaw, % date.Text  ; Pastes Date grabbed from Excel B3, then B4 on 2nd loop

Not sure if I need to create another array or a subset of the existing array etc.

thanks

[TLM]

Not sure if I need to create another array or a subset of the existing array etc.

If I'm reading you correctly you can do something like this

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

; ColArray: Click Coordinates (X, Y), Source Range, Target Range 
ColArray := [ [ 90, 47, "A3:A4", "A1:A2" ], [ 110, 67, "B3:B4", "B1:B2" ] ]

For Each, Col in ColArray
{
	X := Col.1, Y := Col.2, SrcRange := Col.3, TrgRange := Col.4, result := []
	For Row in XL.Range( SrcRange )
	{
	    WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
	    Click, %X%, %Y% Left, 1  ; Actives symbol field in inspector
	    SendRaw, % Row.Text 
	    Send, {Control Down}{a}{Control Up}
	    Send, {Control Down}{c}{Control Up}  ; Copies inspector Output
	    result.push( clipboard )  ; array.push() appends values to the end of an array
	}

	For cell in XL.Range( TrgRange )
	{
	    cell.Value := result[A_Index] 
	}
}

Each time the nested loop completes, the result array is reset so no need to create or use another one.
The advantage of this approach is you can add as many different click coordinates and columns as you like to the ColArray without having to write a new loop into your script.
Also notice that the Column type name ( eg. ticker, date etc ) is replaced with the generic title Row.
btw I never use the command Click so I'm not sure if Click, %X%, %Y% Left, 1 will work.

Give this a test and let me know if it's you're after.

[zirjeo]

Is it possible to keep the clicks separate as I have now and have a % Ticker.txt and a % Date.txt or do they all have to be intertwined into % Row.txt?

I had kept the code a bit simple but in reality what I have to do is
1. Grab the symbol from Column A in Excel
2. Click in the symbol field and paste it in(I don't really need to ctrl a+c this, was just grabbing something to figure out how to do a paste back into excel)
3. Grab the date from Column B in Excel
4. Split this date up with formulas to make the month, day, yr separate
5. Click in the month field and paste month variable, click day field past day variable, same for yr field
6. Then complete some other fields which are static, hit a go button, then ctrl a+c what it computes and paste back into excel.

So I was hoping it was possible to somehow have a
% Ticker.txt
and a separate
% Date.txt so I can then split this up into 3 variables month, day, and year.

I get the feeling this might be difficult to do though.

[TLM]

Ahh you need to send data to fields from adjacent columns in the same loop...

Is it possible to keep the clicks separate..

Yes you can do this with if/else conditions.

Please give this test a run to see what I mean:

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

RangeObj := XL.Range( "A1:B3" )

For Cell in RangeObj ; The names are ambiguous and become the key "Cell"
{
	WinActivate, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
	WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)

	if ( Cell.Column = 1 ) 		; 1 is Column A
	{
		Click, 90, 47 Left, 1
	}
	else if ( Cell.Column = 2 ) ; 2 is Column B
	{
		; Process the Column B's Text/Value here!!!
		Click, 110, 60 Left, 1
	}

	SendRaw % Cell.Text ; Sends what's in the current Cell
}

I have now and have a % Ticker.txt and a % Date.txt or do they all have to be intertwined into % Row.txt?
...So I was hoping it was possible to somehow have a
% Ticker.txt
and a separate
% Date.txt..

The names themselves are ambiguous and become the key Cell.

So in the above example script I posted,
in loop 1, the key Cell represents "Ticker",
in loop 2, the key Cell represents "Date",
in loop 3, the key Cell represents "Ticker" again,
in loop 4, the key Cell represents "Date" again,
and so on ( assuming A is Ticker and B is Date ).

The fact that you're processing 2 fields for each loop actually is easier in a way.

NOTE: The number 1 here: if ( Cell.Column = 1 ) and 2 here: else if ( Cell.Column = 2 ) , correspond to Columns A and B.
If you wish to use data from different Columns, these numbers will have to be changed to match them!!! ( eg. Column D = 4, Column R = 18, Z = 26 and so on.. )

[zirjeo]

Hmmmm I think I have this correct below(updated click coords), for some reason I don't think it likes these two lines below, seems like it's getting stuck here..
RangeObj := XL.Range("A1:B3")
For Cell in RangeObj

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

RangeObj := XL.Range("A1:B3")

For Cell in RangeObj
{
    WinActivate, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
    WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)
    
    If (cell.column = 1)
    {
        Click, 90, 47 Left, 1
    }
    Else If (cell.column = 2)
    {
        Click, 145, 49 Left, 1
    }
    SendRaw, % Cell.Text
}

[TLM]

hrmm. I added these window commands

Code: Select all

WinActivate, Primus Inspector (prms-rdgw.primustrade.com)  ; Activates Window
WinWaitActive, Primus Inspector (prms-rdgw.primustrade.com)

but the script should work like before.
You can test this out by adding a MsgBox command after WinWaitActive and see if it fires.

RangeObj := XL.Range("A1:B3") just assigns the range object to RangeObj.
Feel free to to remove it and change the For Loop to For Cell in XL.Range("A1:B3").

The problem could also be if the range used in my example is empty in your spreadsheet.
This is the range I used, and order by which the loop will grab cell values/text:


You used this range earlier:

Also remember, if you use a columns outside of A or B, the numbers in the conditions will change!
( eg. if RangeObj := XL.Range("C1:D3"), then the conditions will be:
If (cell.column = 3) and Else If (cell.column = 4) and so on... )

[zirjeo]

Hmmm I'm not sure it won't click the fields in inspector when the click is inside the IF statement. If I put the click outside it then clicks the field but then it just shoves all 6 cells into the 1 field at once.

Actually I think I got this, I got thrown off by using cell.column = 1 & 2 and I was using C1:D3. So I need 3 & 4 instead as you explained above. I can't fully test though because software is down for maintenance until tmrw.

[zirjeo]

Just tested this out, works great! Thank you!

[TLM]

great to hear
I'm considering adding something so that you don't have to manage the conditions if you change the range..
just a little busy today but i'll @ you if I get to it..

[zirjeo]

That would be interesting, I assume you mean changing from say A1:B50 to A1:B75 for example. I believe I will always just have two columns I'm pulling from in Excel, a column with symbols and a column with dates. Then the output just goes to 1 column.

[TLM]

..I assume you mean changing from say A1:B50 to A1:B75 for example...

@zirjeo I meant adding properties that represent the 1st & 2nd pre-defined column numbers to the conditions
( so you don't have to manually change them if you change your Range ).

For example this assumes you're using 2 Columns:

Code: Select all

	if ( Cell.Column = RangeObj.Item( 1 ).Column )
	{
		Click, 90, 47 Left, 1
	}
	else if ( Cell.Column = RangeObj.Item( 2 ).Column )
	{
		Click, 110, 60 Left, 1
	}

htms

[zirjeo]

Oh awesome, makes it easier. Thanks again!