ShellExecute Sendkeys and terminating the process

[NomanAziz]

Hi Gurus,
I am trying to automate an application (yeas, i am novice!). Baiscally i need to run a software called traned from excel. The software doesnt have a com interface.
So far i have managed to open the file using shellexecute and i want to use sendkeys to send the keystrokes to the application to run it. However in doing so i am facing the following problems;
1) application opens and goes in the background
2)sendkeys sends keystrokes to excel instead

In addition to above, i would really appreciate if any one could help me with the following;
1) once run is finished, terminate the application
2) control windows, so if i call the same application more than once i could switch between windows.
3) A better way to wait for the run to finish. I am using application.wait at the moment. but a better option is need due to variability in duration of runs.
4)terminate the process and shift focus back to excel

I have search many forums but can't seem to find the right answer.
Please help!!

here is my code (for running the application once);
please note im using windows XP and office 07. Thanks

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Private Sub Open_TranedFile()

ShellExecute Application.hWnd, "open", "C:\Documents and Settings\naziz.EGR\Desktop\a.tnd", vbNullString, vbNullString, 2

'Wait for application to load
Application.Wait Now + TimeValue("00:00:10")


SendKeys "%"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{right}"
SendKeys "{down}"
SendKeys "~"

End Sub

[Siddharth Rout]

Hi Gurus,
I am trying to automate an application (yeas, i am novice!). Baiscally i need to run a software called traned from excel. The software doesnt have a com interface. ....

What software is that?

[NomanAziz]

Its a traffic signal modelling software produced by JCT Consulting UK.
There are always issues with manual data entry, esp in cases of large road networks. Thats why I wanted to automate the process so all data entry, running the model and extraction of results could be done throgh excel. Any help would be much appreciated.

Thanks

[Siddharth Rout]

Hmmm...

1) application opens and goes in the background
2)sendkeys sends keystrokes to excel instead

Have you considered the Findwindow API before sending sendkeys?

[NomanAziz]

Thanks fro your response. no. I am very new to macros. I havent used it as yet. is it possible to switch between windows i.e. if i am running 2 -3 files at the same time, using this Findwindow?

[Siddharth Rout]

Yes.

Search the forum for Findwindow ApI

There are plenty of examples in the forum... Since you are new to vba you might have to put in extra efforts to understand that

[Grimfort]

To note, there are loads of apps out there that do this (with nice little interfaces to help you build up the process), like winbatch etc. Trying to maintain an external process using a one off custom app can be a reallllly bad thing, everytime they change it you have to rewrite your app, where as with a tool you just rewrite a script using the editor. Of course you don' learn, but its also a good idea to use the right tool for the job .

[NomanAziz]

hi i made chages to the code. now it finds the correct window. but having problems with using sendkeys.sendwait . When i run the macros it comes up with 'compile error: Argument not optional'. If i use sendkeys only it works but its too fast! i need sendkeys to send keystrokes only when previous key strokes have finished!
can you please help me. and also help me with finding the way to shut the application once the analysis is finisehed. Thanks

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
Private Const SW_MAXIMIZE = 3
Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Sub Open_TranedFile()
 
'ShellExecute Application.hwnd, "open", "C:\Documents and Settings\naziz.EGR\Desktop\a.tnd", vbNullString, vbNullString, 1
'Application.Wait Now + TimeValue("00:00:06")
 
Dim hwndMs As Long
hwndMs = FindWindow("Afx:400000:8:10011:0:2ab0c27", vbNullString)
If hwndMs <= 0 Then MsgBox hwndMs
'MsgBox hwndMs
'Wait for application to load
 
'SetForegroundWindow (hwndMs)
Call ShowWindow(hwndMs, SW_MAXIMIZE)
 


SendKeys.SendWait "%"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{right}"
SendKeys.SendWait "{down}"
SendKeys.SendWait "~"

End Sub