Copy data from an excel sheet to another window/program.

[Shahveer]

hi all. im pretty new at using MS, and its great! Currently i MANUALLY copy data from a cell in excel, paste it into another program, go back to excel, copy data from another cell on the same row, paste it, and THEN run my macro.
Is there any way for MS to capture the data from excel, run the macro, then go 2 the NEXT line of excel and repeat the process.
Each excel line is different.

I'm no programmer or anything, so i just let MS record my keystrokes and it plays it back. If it helps, its shown below.

Thanks a lot for any help that i may get. I searched the forum, but didnt find anything that applies - i think.
Take care ppl...

Let>WW_TIMEOUT=5
CapsOff
MouseMove>354,620
Wait>51539608.03
Send>Electronic Toys
Wait>0.25
Press Tab
Wait>0.16
Send>E
Wait>0.19
Press Tab
Wait>0.19
Send>s
Wait>0.13
Press Tab
Wait>0.27
Send>9
Wait>0.11
Press Tab
Wait>0.81
Send>e
Wait>0.5
Press Tab
Wait>0.34
Send>s
Wait>0.25
Press Tab * 3
Wait>0.48
Send>10
Wait>0.17
Press Tab * 2
Wait>0.23
Send>
Wait>0.2
Press Tab * 9
Wait>0.61
Send>e
Wait>0.75
MouseMove>121,55
Wait>0.35
LDblClick
Wait>3.53
LDblClick
Wait>1
Send>a
Wait>0.11
Press Tab
Wait>1.06
Send>m
Wait>0.11
Press Tab
Wait>1.34
Send>hrd
Wait>0.19
Press Tab
Wait>0.78
Send>wwt
Wait>0.08
Press Tab
Wait>0.72
Send>ger
Wait>0.53
Press Tab * 2
Wait>0.03
Send>n
Wait>0.03
Press Tab
Wait>0.08
Send>n
Wait>0.05
Press Tab
Wait>0.05
Send>n
Wait>0.03
Press Tab
Wait>0.78
Send>n
Wait>0.05
Press Tab
Wait>0.69
Send>r
Wait>0.19
Press Tab * 4
Wait>0.34
Send>n
Wait>0.06
Press Tab
Wait>0.14
Send>n
Wait>0.06
Press Tab
Wait>0.05
Send>n
Wait>0.06
Press Tab
Wait>0.05
Send>n
Wait>0.16
Press Tab
Wait>0.05
Send>n
Wait>0.13
Press Tab
Wait>0.52
Send>n
Wait>0.06
Press Tab
Wait>0.5
Send>box
Wait>0.64
Press Tab
Wait>0.25
Send>n
Wait>0.06
Press Tab
Wait>0.03
Send>n
Wait>0.03
Press Tab
Wait>0.67
Send>n
Wait>0.03
Press Tab
Wait>0.6
Send>d
//
Wait>0.95
LClick
Wait>2
//Send>1
//Wait>0.3
//Press Tab * 2
//Wait>0.81
//Send>2
//Wait>0.53
LClick
Wait>3.82
Send>02
Wait>0.34
Press Tab
Wait>0.89
Send>08
Wait>0.38
Press Tab
Wait>1.2
Send>02
Wait>0.38
Press Tab * 2
Wait>0.06
Send>08
Wait>0.45
Press Tab
Wait>2.33
LClick
Wait>1.66
Press Tab * 2
Wait>0.83
Send>rdxb-jh7
Wait>0.52
Press Tab * 10
Wait>0.44
Send>n
Wait>0.27
Press Tab
Wait>0.22
Send>n
Wait>1.16
//
LClick
Wait>3
//Press Tab
Wait>1.03
LClick
//Press Tab
//Send>0
Wait>0.58
LClick
Wait>1
//Press Tab
//Wait>0.95
//Send>1
Wait>0.98
LClick
Wait>1.51
Press Tab
Wait>0.92
LClick
Wait>0.09
MouseMove>443,444
Wait>1.61

[Lumumba]

You should have a look at MS's DDE commands on how to transfer data from an open Excel worksheet to another application.

About the recorded scripts.

a) You sh-/could replace all different Waits with a default value eg. 0.5, which makes it easier to check the code.

b) have a try to get rid of unnecessary Waits

c) what about to create standard format source files (csv,txt) with WLN> and import it into your target application (if it's possible ).

[Shahveer]

Lumumba,
Thanks for your reply. I had a look at the DDE commands and some examples from the net. i'll try and figure out how to make it work, coz i'll have 2 keep getting diff. data from diff. cells.
...gonna be a major case of trial & error!

Also, thanks for your tips. Reason i dont wanna change the wait time is coz my application is on a server, which sometimes hangs etc, so if MS runs too fast, it may get stuck.

I have no idea what you mean about the .csv thing! lol.

thanx a lot tho... appreciate it!

[support]

Hi,

Use the Excel/DDE example at http://www.mjtnet.com/scripts.hts

Another approach is to use VBScript. Using VBScript or DDE allows you to get the data from Excel directly without using any keyboard simulation.

As for moving from row to row just use a simple loop. You probably want two nested loops - one for each dimension (columns and rows).

[Lumumba]

The csv-thang --> CSV = Comma Separated Value

As you may be aware apps like Excel are able to work with different file formats. So you could create/write a datasources like this:


database.txt
name,surname,address,zipcode,city,country

database.csv
bob;hope;Belevue Drive 180;11111;Bollywood;India


If you create a file with MS's WLN> command based on this syntax (other delimiters are optional), it could be used/imported by Excel without a problem.

Nearly all apps could import this kinda raw data. Check yours.

[Shahveer]

Thanks a lot for your replies ppl!
Appreciate it!
It's amazing how ppl r so helpful on the net. Too bad its not so true/common (or so it seems) in real life, non-computer issues!

Cheers!

[crozzer]

Hi,
Since your server might take different times to finish each time you run, you might consider using the WaitPixelColor> function. If there is a place on the screen that changes to confirm the server is done (like the flag in Internet Explorer), then you can wait for that to happen before continuing. I made a subroutine like this to wait for a clock to disappear from my server app:

SRT>WaitDone
WPC>16777215,652,355,10
if>WPC_RESULT=FALSE,TROUBLE
END>WaitDone

Label>TROUBLE
...

Good Luck!

[shahveer]

hey crozzer,
thanx 4 ur tip.
un4tunately, theres no color change thing etc. basically the screen just hangs for a few seconds...becomes totally grey...then comes 2 normal.
i guess i cud use a waitwindow thing, and specifiy a timeout (sometimes it just take toooo long).

However, 2 be honest, i dont think i'll need it; b'coz i didnt really manage to make MS get the data from excel into my window.
dont wanna trouble all u nice ppl out there, so i'll just continue the old way...copy/paste!

take care..

[Lumumba]

Are you able to check the taskmanager process statement during that delay? Something like "blablabla is not responding" ...

[crozzer]

That's fine to give up, but in case anybody else is watching, the pixel color change from "just gray" to something else will probably work in this case...