Acessing CMD shell in Excel/VBA with keystrokes and copying resultant data 1

[Tuckabag]

Hi,
I am trying to incorporate via VBA opening a CMD shell to run an old DOS program, enter some values and then capture the results to paste back into Excel.
So far I can open the Shell, run the program and send keystrokes as required using the below VBA code.

works = "C:\Hydoff\hydoff.exe"
Shell (works), vbNormalFocus
Application.Wait (Now + TimeValue("00:00:03"))
SendKeys "~"​

However, I cannot find a way to take a screenshot, or copy/paste the resulting data shown in the Shell and dump it back into VBA/Excel.

I have found alternate methods to open the Shell, run the .exe file AND capture data from the Shell screen, however none of these methods have :

1) Kept the Shell in focus
2) Allowed me to send keystrokes.​

Without being able to send keystrokes, I can't get the program to produce the data required, so the screen captures are useless.
It would be nice if I could send command line arguments to it, but the software doesn't support this.

Any thoughts as I've exhausted my usual avenues of google....
Cheers.

 

[3DDave]

Perhaps redirect the output of the program to a file and then open that file.

https://helpdeskgeek.com/how-to/redirect-output-from-command-line-to-text-file/

 

[Tuckabag]

Perhaps redirect the output of the program to a file and then open that file.

The data I need isn't dumped out to the command line, it is just shown on the screen as the program executes. I don't think you can re-direct the data when the program runs this way unless I set the shell up as an object? And eveytime I've tried to do that I fail to be able to send keystrokes..... happy to shown I am wrong on this as it's doing my head in!

 

[SWComposites]

Sheesh. Just rewrite the old dos program.

 

[3DDave]

Oh - OK, more details that are important to solving the problem.

I was misled by "copy/paste the resulting data shown in the Shell"

At this point you should go to AutoIt which is designed to deal with problems exactly like this. Anything you could do it can do, and many things you cannot do it can manage to do. Copy/Paste, activate windows, wait for a large variety of events. No doubt all of this is sort of available via VBA by way of understanding Windows direct procedure calls and the underlying OS in great detail, but AutoIt is much more approachable.

 

[IDS]

Hadn't heard of AutoIt:

https://en.wikipedia.org/wiki/AutoIt

"AutoIt /ɔːtoʊ ɪt/[3] is a freeware programming language for Microsoft Windows. In its earliest release, it was primarily intended to create automation scripts (sometimes called macros) for Microsoft Windows programs[4] but has since grown to include enhancements in both programming language design and overall functionality."

Looks interesting.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

All sorts of handy means to deal with software that was designed to avoid being automated. For example, PTC's Creo doesn't use Windows' menu system, so normal requests to the window to perform menu operations won't work. The funnest is that one can check to see if a UI window of some name is open. PTC used to open ALL the UI windows, but give them negative coordinates, so you could not depend on "WinExists" because they all do. No problem - check the window coordinates to see if the interesting one it is displayed to the user.

 

[Tuckabag]

Thanks 3DDAve, AutoIt looks very interesting. I will download it and have a play.
Much appreciated!

 

[3DDave]

Start with getting AutoIt to open the CMD and sending the commands. Small steps. Very rewarding.

It has a huge user forum and a huge number of examples and user created examples.