Call .exe file with input file

[Feliks]

Greetings,
I'm best described as dangerous when it comes to vba. I have a Fortran source code that I've compiled as a .exe file that I want to call and run in excel vba. The .exe requires an input file be utilized, this is where I'm running into trouble. I've tried using shell command but to no avail. Any assistance would be greatly Appreciated.
Thanks,
Marc

[mancubus]

does this help?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=341

[Paul_Hossler]

This is what I usually do



Option Explicit
Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Declare Function WaitForSingleObject Lib "kernel32" _
(ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Public Enum ShellTiming
SH_IGNORE = 0 'Ignore signal
SH_INFINITE = -1& 'Infinite timeout
SH_PROCESS_QUERY_INFORMATION = &H400
SH_STILL_ACTIVE = &H103
SH_SYNCHRONIZE = &H100000
End Enum

Public Enum ShellWait
SH_WAIT_ABANDONED = &H80&
SH_WAIT_FAILED = -1& 'Error on call
SH_WAIT_OBJECT_0 = 0 'Normal completion
SH_WAIT_TIMEOUT = &H102& 'Timeout period elapsed
End Enum

Public Enum ShellWindow
SH_HIDE = 0
SH_SHOWNORMAL = 1 'normal with focus
SH_SHOWMINIMIZED = 2 'minimized with focus (default in VB)
SH_SHOWMAXIMIZED = 3 'maximized with focus
SH_SHOWNOACTIVATE = 4 'normal without focus
SH_SHOW = 5 'normal with focus
SH_MINIMIZE = 6 'minimized without focus
SH_SHOWMINNOACTIVE = 7 'minimized without focus
SH_SHOWNA = 8 'normal without focus
SH_RESTORE = 9 'normal with focus
End Enum

Function Shell_AndLoop(ByVal CommandLine As String, _
Optional ExecMode As ShellWindow = SH_SHOWNORMAL) As Boolean
Dim ProcessID As Long
Dim hProcess As Long
Dim nRet As Long
Const fdwAccess = SH_PROCESS_QUERY_INFORMATION
If ExecMode < SH_HIDE Or ExecMode > SH_RESTORE Then
ExecMode = SH_SHOWNORMAL
End If
ProcessID = Shell(CommandLine, CLng(ExecMode))
hProcess = OpenProcess(fdwAccess, False, ProcessID)
Do
GetExitCodeProcess hProcess, nRet
DoEvents
Sleep 100
Loop While nRet = SH_STILL_ACTIVE
Shell_AndLoop = (nRet <> 0)
End Function

'The Shell_AndWait routinestops your app dead in its tracks --
'doesn't repond to keyboard, mouse, etc -- until the shelled app is finished.

Function Shell_AndWait(ByVal CommandLine As String, _
Optional ExecMode As ShellWindow = SH_SHOWNORMAL, _
Optional Timeout As Long = SH_INFINITE) As Boolean
Dim ProcessID As Long
Dim hProcess As Long
Dim nRet As Long
Const fdwAccess = SH_SYNCHRONIZE
If ExecMode < SH_HIDE Or ExecMode > SH_RESTORE Then ExecMode = SH_SHOWNORMAL
ProcessID = Shell(CommandLine, CLng(ExecMode))
hProcess = OpenProcess(fdwAccess, False, ProcessID)
nRet = WaitForSingleObject(hProcess, CLng(Timeout))
Shell_AndWait = (nRet <> 0)
End Function

Sub CallProgWithParms()
Dim sCmdLine As String
sCmdLine = Environ("WINDIR") & "\notepad.exe c:\CalledTextFile.txt"
Call Shell_AndWait(sCmdLine)
End Sub


Paul

[Kenneth Hobs]

I would recommend Chip Pearson's code that is similar to what Paul posted. http://www.cpearson.com/Excel/ShellAndWait.aspx

[Paul_Hossler]

Ken - Chip's is more powerful

Thanks for the link

Paul

[Feliks]

All,
Thanks for all the replies and good info. As I alluded to before I'm a bit of a rookie with VBA.

Paul,
I tried your code and have a few simple questions. I dropped your code into a VBA module inside excel 07 and changed a few things in the below code: "WINDIR" to my working directory, "\notepad.exe" to my program and "c:\calledtextfile" to my input file. Of the whole piece of code this appeared to be the only things I needed to change. Is this right?
Is "c:\calledtextfile" the entire path directory for the input file?
sCmdLine = Environ("WINDIR") & "\notepad.exe c:\CalledTextFile.txt"

[Feliks]

At the risk of appearing too ignorant, I dont see a where I specify my .exe file or input file in Chip's code. Does it require a sub similar to the end of Paul's code?

[Kenneth Hobs]

Yes, it is similar. I would put Chip's code in one module and the test code to use it in another. See an example where I used Explorer with Chip's routine: http://www.vbaexpress.com/forum/showthread.php?t=34604

[Feliks]

Kenneth,
I put chip's code in one module and put your explorer code in another module. I was able to run your code after making the appropriate input file. I then input my .exe and input w/ respective path's and got nothing. A command prompt flashes but my .exe produces no output file. A little more background on the current operation of the .exe might be helpful. Like I mentioned before, this is Fortran code compiled into an executable. The .exe program requires that a specifically written input file be located in the same directory as the .exe. So if the input file is in the same directory, I double click on the .exe, a dos command window flashes on and off then an output file appears in the same directory. I'd like to be able to run this .exe via vba so that I can have it crunch through the .exe a set number of times. Ive had some luck using WshShell, it produces an output file but there are no results in it (perhaps suggesting the program was terminated before it could complete. Thanks for all the help

[Kenneth Hobs]

I am not sure what you are doing. If your EXE is expecting an input parameter, the Explorer example shows how to pass it. If it expects that the file that it needs is in the EXE file, then the EXE must insure that the file exists or writes to the output file. If you need VBA to insure that a blank file exists in the EXE path, that can be done in previous VBA code.

Try running the EXE from a Start > Run, or a DOS prompt. What command are you sending? e.g. MyFortranProgram.EXE or MyFortranProgram.EXE c:\fortran\output\MyFortranProgramOutput.txt.

Sometimes, you will need to encapsulate passed parameter strings with quotes if it contains space characters.

[Feliks]

To run the program from a dos prompt all I have to do is get to the right directory and enter the name of the program, seals.exe. a few calculation details are displayed in the dos prompt and the output file appears in the directory I'm working out of. I don't have to specify the input file or the output file path.

[Feliks]

Kenneth,
I got your pm but whenever I try to email you I get a undeliverable notice.

[Feliks]

I cant PM since I don't have 10 posts yet, I'm getting close though

[Kenneth Hobs]

IF the EXE file is too big, some ISP's don't allow them. You could put the file on a free site like box.net or mediafire.com and such. Then email me at khobson at aaahawk.com or khobson at odot.org.

[Feliks]

the zipped file size is 340kb. I sent it w/ my yahoo account and I didnt receive any undeliverable messages. Have you received my email?

[Kenneth Hobs]

Your program probably needs DOS to be active for that drive and folder. To handle that scenario, change the drive and path below to fit your setup.


Sub test()
Dim exe As String, batFile As String, FileNumber As Integer
batFile = Environ("temp") & "\seals.bat"
exe = "x:\seals\seals.exe"
FileNumber = FreeFile ' Get unused file number.
Open batFile For Output As #FileNumber ' Create file name.
Print #FileNumber, "x:"
Print #FileNumber, "cd x:\seals"
Print #FileNumber, exe
Close #FileNumber ' Close file.
ShellAndWait batFile, 1000, vbNormalFocus, PromptUser
Kill batFile
End Sub

[Feliks]

Kenneth,
Thanks so much, that looks like its going to do the trick!
Now I can write input files through excel, run them through the EXE, get an output, rinse and repeat. Thanks again.