SendKeys with DOS program

[evildad]

Hi all,

Please help as I'm going INSANE.......................................

I have written a VBA macro in Excel, which attempts to do the following:
1. Loop through spreadsheets in a directory
- Create a text input file from data held in spreadsheet.
- Open a DOS program with the SHELL command
- Send name of this input file to the DOS program
- Retrieve data from the output file, and paste into spreadsheet
- Save and close spreadsheet, open next spreadsheet in directory.

The SendKeys command is failing on what appears to be hundreds of different
fronts:-

I have tried to activate the DOS program with :-
retval = Shell("DOS_Program.exe, 1)
AppActivate retval, True

This yields errors, so from this forum I have tried :-
On Error Resume Next
Do
Err.Clear
AppActivate retval, True
Loop Until Err.Number = 0

I have tried wrapping every SendKeys statement with :-
retval = DoEvents()
SendKeys string_Filename & "{ENTER}", True
retval = DoEvents()

I have also tried adding manual delays, to try and ensure the keys are being
sent to the correct application :-
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
(and have tried varying the delay here from 1 to 10 seconds)

The errors I am getting vary, but tend to involve the following:-
- Command Prompt windows staying open, with an empty command line
- Command Prompt window stays open with incorrect filename and no enter key
- Macro attempting to close itself, i.e. not one of the processed
spreadsheets.

I have done a similar task in the past, i.e. batch processing DOS based
programs that don't have a command line option, using the Windows Script
Host. Remembering back, I had to play about with the delay to get the DOS
program to successfully accept the SendKeys and run the file.
In this case though, I need to use VBA from within EXCEL, and not externally
using VBScript.

ANY IDEAS OR HELP PLEASE BEFORE I SHOOT MYSELF ;-)

p.s. All files and the DOS program are stored in the same sub-directory to
the spreadsheet holding the macro code.

 

[Rody]

Try search for een API solution. Google is your friend. Search for
"PushKeys". Next webside give's a ZIP file with a DLL for u. (I didn't try
it)

http://www.alan-warriner.co.uk/software/PushKeysDLL.zip

HTH,

Rody

 

[evildad]

Thanks for the link, Rody.
I've had a good search as you suggested, and I've also downloaded both the
PushKeys DLL, and the enhanced TPushKeys.
I am still struggling though. Basically, I was hoping for an easier answer.
I don't have the time (or the intelligence) to try to understand how to
interface my VBA code with the VB code.

What I am going to try to do is to prepare all the input files first, and
then to escape out to the Windows Script Host to use the SendKeys command in
VBScript.
I have used VBScript successfully in the past, but admittedly I have had to
play about varying the delay. This is the code I have used in the past
(VBScript):-

Set wshShell = WScript.CreateObject("WScript.Shell")
wshShell.Run "DOS_Program.exe"
WScript.Sleep conDelay
retval = wshShell.AppActivate("DOS_Program.exe")
If retval Then
wshShell.SendKeys strInputFile & vbCr
End If

--
Cheers,

Paul.

Try search for een API solution. Google is your friend. Search for
"PushKeys". Next webside give's a ZIP file with a DLL for u. (I didn't try
it)

http://www.alan-warriner.co.uk/software/PushKeysDLL.zip

HTH,

Rody

 

[Robert Bruce]

Hi all,

Please help as I'm going INSANE.......................................

I have written a VBA macro in Excel, which attempts to do the
following:
1. Loop through spreadsheets in a directory
- Create a text input file from data held in spreadsheet.
- Open a DOS program with the SHELL command
- Send name of this input file to the DOS program
- Retrieve data from the output file, and paste into spreadsheet
- Save and close spreadsheet, open next spreadsheet in directory.

Sendkeys is nasty and dirty. However, if you really must use it in your
situation you should avoid trying to send keystrokes to the dos session.
Sendkeys is designed to send keystrokes to a Window and the dos session
running inside a dos window isn't really a, er, Windows window. What you
*can* do is to place your keystrokes into a string variable, put that string
onto the clipboard and then send keystrokes to the window that wraps the dos
session to paste the string - Alt+Spacebar then E then P

I've had this work well in the past, but you may need to put some delays in
to ensure that everything is waiting ready for keystrokes to be sent.

HTH

Rob

 

[evildad]

Thanks for that Rib, it looks promising........
Cheers,

Paul.

 

[evildad]

Nope, that still didn't work ;-(

I've now changed my approach......

I create the input files for the DOS program from within EXCEL using VBA.
I then quit EXCEL, and use a script written in VB Script running on the
Windows Script Host, to send the name of each file to the DOS program in
turn. Setting the delay to 1600ms prior to sending the keys seems to
work...so I'll stick with this solution chaps and chappesses.

Cheers for your suggestions.