Get Excel data into array for use in other program.

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
ab9361
Newbie
Posts: 2
Joined: Sun Dec 16, 2012 5:55 am

Get Excel data into array for use in other program.

Post by ab9361 » Sun Dec 16, 2012 9:20 am

Update: Managed to solve the problem. Posting this at 4 in the morning was part of the problem. Also, was not setting the focus properly and using incomplete key entries. Great results!

New to MS. I have made some progress, but apparently need technical ehlp to complete

I have a simple one-column Excel spreadsheet of stock symbols that I want to load into an array and then paste them one at a time into a charting package. I can load the array, and a recorded macro lets me paste to the charting software, but when I merge the two, something is wrong with the way I am trying to paste and it doesn't work.

Any assistance would be greatly appreciated.


Here is the macro....
//The first part works great....
Let>WW_TIMEOUT=5
CapsOff

XLOpen>C:\users\new user\documents\my trading\TradeCoWatchlist.xlsx,1,xlBook
XLGetSheetDims>xlBook,Sheet1,rows,cols
WaitWindowOpen>Microsoft Excel*
ArrayDim>Sym,72
Let>c=1

Repeat>c
Let>r=c+1
XLGetCell>xlBook,Sheet1,r,1,Symbol
Let>Sym_%c%=%Symbol%
Let>c=c+1
Until>c,rows

//...to here. The array is loaded.
//The rest doesn't paste the symbols.

Let>y=-1535
Let>x=93
//coordinates of where I need to left click to open the symbol dialog box
MouseMove>x,y
Wait>.10
//this is from the recorded macro. seems to appear on the first click, but not thereafter.
LDown
WaitWindowOpen>TC2000 ® Beta
MoveWindow>TC2000 ® Beta,-1601,-1
ResizeWindow>TC2000 ® Beta,4803,902
Wait>0.10
LUp
Let>k=1
Repeat>k
//here I try to put the symbol on the clipboard and paste.
//tried to simply Send the symbol, but that didn't work either
Let>Symbol=Sym_%k%
WaitClipBoard>
Put>%Symbol%
MouseMove>x,y
Wait>.1
LClick
Wait>.1
LCTRL
Wait>.1
Send>v
Wait>.1
Press Enter
Wait>.1
//This changes the coordinates based on the number of symbols entered.
IF>y=2970
Let>y=-1534
Let>x=x+205
ELSE
Let>y=y+265
ENDIF
Let>k=k+1
Until>k,c-1




Here is the recorded macro that I used. It successfully pastes whatever is on the clipboard to the proper chart.

//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
//Let>IGNORESPACES=1
// C:\Users\NEWUSE~1\AppData\Local\Temp\msr38D7.tmp.scp
// Recorded on Sunday, December 16, 2012, at 04:02 AM

//Recorded Events
Let>WW_TIMEOUT=5
CapsOff
WaitWindowOpen>Macro -
MoveWindow>Macro - ,1953,-876
ResizeWindow>Macro - ,1004,702
MouseMove>-1533,96
Wait>5.61
LDown
WaitWindowOpen>TC2000 ® Beta
MoveWindow>TC2000 ® Beta,-1601,-1
ResizeWindow>TC2000 ® Beta,4803,902
Wait>0.11
LUp
Wait>1.17
Press LCTRL
Wait>0.67
Send>v
Wait>0.19
Release LCTRL
Wait>0.78
Press Enter
Wait>1.72
MouseMove>-1261,93
Wait>1.32
LClick
Wait>0.55
Press LCTRL
Wait>2.17
Send>v
Wait>0.28
Release LCTRL
Wait>0.55
Press Enter
Wait>3.81
Press LCTRL
Wait>0.02
Press LALT * 12
Wait>0.03

hoangvo81
Pro Scripter
Posts: 69
Joined: Tue Feb 07, 2012 8:02 pm

Post by hoangvo81 » Mon Dec 17, 2012 5:37 pm

I dont know if this is related but your script might not end,

repeat>k
...
..

until>k,c-1

.. i did a simple notepad paste it pasted no problem.
but it ran endlessly.

maybe change it to:

let>c=c-1
repeat>k
....
..
until>k,c

ab9361
Newbie
Posts: 2
Joined: Sun Dec 16, 2012 5:55 am

Post by ab9361 » Mon Dec 17, 2012 6:09 pm

Thank you very much for your reply. With some trial and error, I managed to correct a number of errors, including the looping, and get it working. I do appreciate you replying.

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts