×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

(OP)

Quote (3ddave)

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!

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

Sheesh. Just rewrite the old dos program.

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

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.

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

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/

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

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.

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

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

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close