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.
--
Cheers,

Paul.