Data capture, paste

**Jiser001** · 02-21-2010, 10:01 AM

Does anyone have suggestions how to automate the following;

I have a list containing several hundred names; ABC, DEF, GHI, etc.

My VBA loops throught this NameList, - one by one, until the list is empty, and for each loop, the VBA shall exit Excel { thru a shell command..? } and enter into a third-party application, to copy some information from this application into the Windows Clipboard. (Manually this works...).

This is done if the following keysequence is peformed while I am into the third-party program;

"ABC[Enter][ctrl-A][Ctrl-C]"

Of couse Excel must change ABC to DEF for the second round, etc.

So, for each round my program will have new data in clipboard, that the rest of my VBA puts in an appropriate place. This works..

But my problem is how to handle the key command line, while outside the Excel VBA.

Anyone have suggsetions on how to do this..?

**JeanRage** · 02-21-2010, 10:05 AM

Hi,

Take a look at Chip's excellent solution ...
http://www.cpearson.com/excel/ShellAndWait.aspx

HTH

**royUK** · 02-21-2010, 10:05 AM

Can't you import the data into Excel first?

**Jiser001** · 02-21-2010, 11:42 AM

That could be done, but as I am a very lazy person, I want to make this application i order to avoid moving back and forth between the external datasource and Excel.

If I have to do this manually, I need to enter differnt names hundreds of times, to get the right data captured in the Clipboard, then open Excel to paste the content into the correct position in my spreadsheet. It can be done, but my idea is to avoid this....

I will try to implement Chips solution, thanks a lot JeanRage....

**Jiser001** · 02-21-2010, 04:25 PM

Have tested the code from Chips. That works excelent. However, it does not transfer keystrokes to the third application running under the Shell command; Need to have the following keystroke sequence entered, when this application is running; "[name] + CR + LF + Ctrl-A +Ctrl-C", - to capture the data into the clipboard. Tried a test.bat file with a %1 transfer of the string, without any success. Anyone have ideas..?