Solved: Shell and Wait Issue

[aerodoc]

I am using the Shell and Wait code listed below. Everything works fine, but if I do a "save as" and then run it in the new file, it does not work (at least I am pretty sure it is this portion of the VBA that is causing the issue).

Is there anything below that would be relevant to the action of a "save as" that is causing it to trip up?

-------------------------------------------------------------------------------------

Option Explicit

'Errors
Public gszErrMsg As Long
'Constant for the dwDesiredAccess parameter of the OpenProcess API function.
Private Const PROCESS_QUERY_INFORMATION As Long = &H400
'Constant for the lpExitCode parameter of the GetExitCodeProcess API function.
Private Const STILL_ACTIVE As Long = &H103
'Libraries (DLL's)
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long

Function MeShellAndWait(szCommandLine As String, Optional iWindowState As Integer = vbHide) As Boolean

Dim lResult As Long
Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long

On Error GoTo ErrorHandler

'Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

'Check for errors in the command line variable.
If lTaskID = 0 Then Err.Raise 9999, , "Shell function error."

'Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

'Check if process was started
If lProcess = 0 Then Err.Raise 9999, , "Unable to open Shell process handle."

'Loop while the shelled process is still running.
Do
'lExitCode will be set to STILL_ACTIVE as long as the shelled process is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

MeShellAndWait = True

Exit Function

ErrorHandler:
gszErrMsg = Err.Number
MeShellAndWait = False
End Function

[Kenneth Hobs]

I suspect that your problem is with your use of the procedure. For those sorts or things, I write to a BAT file that executes an EXE with command line parameters. Some EXE's are like that.

[aerodoc]

Ken,

But if the batch file is used, how will Excel know when to go the next procedure? Isn't the point of the Shell and Wait routine to only to the next step once the .exe has been completed? If you write a batch file, I think it will spawn the .exe but has no idea when to go to the next step, which cannot be completed until the .exe has finished its business.

[Kenneth Hobs]

All I can say is, try it to see what I mean as I seldom make recommendations without testing what I recommend. That routine is based on a shell process. Some processes like a Fortran program's EXE require a DOS environment. So, not all EXE's are created equal. I gave an example in a past thread where I created the BAT file and then did a shell wait. Of course it is easy to just hard code a BAT file with wildcard input parameters.

You other thread was: http://www.vbaexpress.com/forum/showthread.php?t=34604

[aerodoc]

I tried it and it did as you said it would, which was a bit of a surprise to me :)

Actually, reviewing the previous thread, it looked like I tried that before. It has been a while and I started noticing the issues pop up. But now, getting back to the root problem, after doing a "save as" it still did not work though.

I am sort of wondering about an "initialization" that may occur when a file is opened, that does not happen when a file is a "save as". If I reopen the file after the "save as" then it works fine again. Perhaps I could lock out the save as feature, but that creates an inconvenience for the user.

[Kenneth Hobs]

Here is the thread that I talked about. http://www.vbaexpress.com/forum/showthread.php?t=37457

[aerodoc]

Ken, any idea here? I used Chip Pearson's Shell and Wait code and the batch file, as follows. It works fine until I do a "save as". Then, if restarted it works fine again. It is only a problem if I do a save as and then try to run it immediately afterwards. Something is odd about this! It still seem to me like something needs to be initialized in order to work?

-------------------------------------------------------------------------------------

Sub ShellWait()
Dim exe As String, inpFile As String, outFile As String

exe = "C:\path\test.bat"

If Dir(exe) = "" Then
MsgBox "EXE File Does Not Exist:" & vbLf & exe, vbExclamation, "Macro Ending"
Exit Sub
End If

ShellAndWait exe, 10000, vbNormalFocus, PromptUser

End Sub

[Kenneth Hobs]

I don't see a Save As in your code. Is this a manual SaveAs of the Excel file and then a 2nd run?

Check your running processes after the first run to be sure that the process was removed.

[aerodoc]

Sorry for the confusion. Yes, you do a manual Save As and then try to ShellWait from the "new" file. This is what makes me think there needs to be some sort of "initialization" of the file? If you restart the file, it will work.

Everything looks good on the process side.

[Kenneth Hobs]

Try posting your xls file so that we can test it. The type of EXE file might be the problem. Try something like NotePad or WinWord.

[aerodoc]

I don't want to the file because it is proprietary. I tried notepad and it worked fine, but in the past I found that I pointing to the correct dir was a problem with the exe I am using. Notepad doesn't have that issue.

I did try another exe that was only in a specific dir and it seemed to work OK too, leading to the possible conclusion that the problem is exe specific.

One possible solution is that the exe is an opensource code which might be able to be compiled another way. I know the originator is fond of unix, which might be the problem. That is probably beyond me though. Still seems strange that the exe could actually change the way it behaves.

[Kenneth Hobs]

Sounds like your EXE is like some Fortran EXE's where the BAT file method is needed. We will need an EXE that behaves like yours to test your scenario. If I find the Fortran EXE that I used to help the other user, I will try your scenario.

[aerodoc]

On post #7, I *thought* I used the BAT file approach? Is this not what you were talking about?

The result was the same though.

The other option would be to directly send you the files to see if you can work it out. I can throw a few bucks in for your time. My fear is that it just won't work out. I have worked it pretty hard and its not coming out.

P.S. The author of the exe said he will help too and I know he has done some VBA. I can try that as well if the BAT file approach is the last trick up your sleeve.

[Kenneth Hobs]

I can keep the file confidential and delete after I help or not if you like. You can send to my khobson at aaahawk dot com address if you like. However, if the BAT file approach did not work, I am not confident of success.

[aerodoc]

Ken, thanks very much. The problem was that I was not changing the drive in my batch file. Ken provided me with a working batch file like:

c:
cd C:\temp
notepad

The other option was to add the following line in my VBA and bypass the batch file:

ChDrive "C:\"

Even though I already had a line that had that "seemed" good enough, in the form of ChDir "C:\temp", this was not good enough. The drive needed to be changed first.

[Kenneth Hobs]

I am glad that it worked out for you. I would suggest though that if you use the ChDrive method that you change it back to the user's drive and folder before you ran your macro. I think it good practice to leave the user's computer in the state that they will expect.

[aerodoc]

Ken, how would one do this? Where would it be noticed? If they opened a CMD shell?

[Kenneth Hobs]

The scenario is that the user saves the excel file to a thumb drive. After playing the macro, they decided to save the file, do another SaveAs for new data, they would be at the macros folder, not their thumb drive and folder.

Obviously, with the BAT file method, it sets the drive and folder in the DOS shell rather than the Windows shell that the Excel file checks for.