PDA

View Full Version : Solved: Shell Command and Flash Drive Paths



aerodoc
10-19-2010, 05:18 PM
I am trying to use a modified version of the "Shell and Wait" code from this source:

*****
Google "shell and wait vba" and it will be on the appspro site (I cannot link to it now since I am a newbie)
*****

But I found that if I use the "save as" feature and save to a flash drive, it will not run:

The modified version will first set the path and then run an executable. A modified example would be to copy the calc.exe to calc2.exe and put it in dir "test".

Original code from link:
If Not bShellAndWait("Calc.exe", vbNormalFocus) Then Err.Raise 9999

Modified Code:
ChDir "C:\test\"
If Not bShellAndWait("calc2") Then Err.Raise 9999

I changed it up to represent the way I am actually calling the executable. Any idea why after using the "save as" to a flash drive, it wont' run properly? If you restart the file, then it will work.

Thanks for any help! I simply cannot lick this, nor understand what is going on. Fixing this may also solve another problem...but one step at a time.

Aussiebear
10-19-2010, 10:28 PM
Have a look here http://www.cpearson.com/excel/ShellAndWait.aspx

Kenneth Hobs
10-20-2010, 05:12 AM
Use the full path to the EXE rather than leaving it to Windows to look for it. Windows only looks in the paths that are Set and on occasion the current path. Using the current path assumption may be the wrong assumption.

So, the reason Calc.exe works is that Windows looks at c:\windows\system32 by default. To see the paths set, Start > Run > CMD > OK > PATH > Enter > Exit. You can use VBA to get the environment variable Path as well.

aerodoc
10-20-2010, 07:41 AM
Aussiebear,

I tried that, but got the same result. But thanks for mentioning it since there may some other benefits of that approach.

Kenneth,

I currently use the following to set the path, right before I have the shell command:
ChDir "C:\test\"
I think it is possible that there is a path problem. But exactly how would I implement the path as you suggest? I am confused by that.

Kenneth Hobs
10-20-2010, 07:58 AM
If Not bShellAndWait("c:\test\calc2.exe") Then Err.Raise 9999


You could also use Dir() to determine if the file even exists before calling the function.

aerodoc
10-20-2010, 09:52 AM
Ken,

Thanks. I did not give a very good example of my actual problem though. I need to run a command like "run input" where run.exe is the executable and input is the file that it calls.

So I tried the following, but that does not appear to work. Ironically if I set the path first and keep the same line, it does. Strange.

If Not bShellAndWait("C:\test\run input", vbNormalFocus) Then Err.Raise 9999

Kenneth Hobs
10-20-2010, 10:01 AM
It is a good idea to use the file extension when doing that sort of thing. Passing parameters can be handled in various ways depending on the function.

If Not bShellAndWait("C:\test\run.exe input", vbNormalFocus) Then Err.Raise 9999
In some cases, you will need to encapsulate parameters and or paths in quotes. Space characters embedded in the paths often warrant the extra delimiting quotes.

aerodoc
10-20-2010, 10:58 AM
Ken,

I added the .exe, but that did not help. I also tried something like

If Not bShellAndWait("C:\test\"run.exe input"", vbNormalFocus) Then Err.Raise 9999

but Excel VBA did not like the extra quotes. How should I implement that?

Kenneth Hobs
10-20-2010, 02:33 PM
Since I don't have run.exe, I don't know how it expects parameters.

Getting the syntax right for embedded quote strings can be tricky for new programmers. Using the method below, should be easy to understand. I commented some other examples that work.

Sub Test_ShellWait()
'ShellAndWait "c:\windows\explorer.exe /e,/root,c:\windows,w:\ken.txt", 1000, vbNormalFocus, PromptUser
'ShellAndWait "explorer /e,/root,c:\windows,w:\ken.txt", 1000, vbNormalFocus, PromptUser

Dim q As String, ie As String, txtFile As String
q = """"
ie = "c:\windows\explorer.exe"
txtFile = "w:\ken.txt"

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

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

ShellAndWait ie & " " & _
"/e,/root,c:\windows," & q & txtFile & q, 1000, vbNormalFocus, PromptUser
MsgBox "Did the shell wait?"

ShellWait "explorer /e,/root,c:\windows,w:\ken.txt", vbNormalFocus
MsgBox "Did the shell wait?"
End Sub

The ShellWait() routine is a shorter version of what Chip Pearson's routine does. http://www.mvps.org/access/api/api0004.htm

aerodoc
10-21-2010, 02:58 PM
Ken,

It still does not seem to work correctly. I would have though calling a batch file (and hence no spaces) would have worked, but that did not either.

Maybe you can help directly? I tried to send a PM, but it will not go through for a newbie. I think I can receive them though.

Kenneth Hobs
10-21-2010, 07:45 PM
I could not duplicate your problem. Of course I can't be sure until I test on a computer with the files only on the thumb drive. Some programs unzip to a folder but put others in c:\windows\system32. I suspect that it is the typical problem with USB drives and Windows. The drive sometimes takes a 2nd read to actually be seen. I usually see this from a File > Open dialog in some programs. It is a matter of refreshing the drive in other words.

Since your program is a DOS program it needs to run in DOS. I attached your sample file for others to test if they like. The EXE file is a large download.

Notice that I have various cases for testing. Just comment out or modify the lines to suit your file locations and needs.

The EXE actually adds the inp file extension to the passed parameter (the inpFile) so one has to know that and code for it. Notice that I added it back to test for its existence.

Of course this can be made into a Procedure or Function with the two input parameters exe and inpFile easily.



Sub Test_ShellWait()
Dim exe As String, inpFile As String

exe = "c:\sample\ccx.exe"
inpFile = "c:\sample\stability"

exe = "f:\sample\ccx.exe"
inpFile = "f:\inp\stability"

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

If Dir(inpFile & ".inp") = "" Then
MsgBox "inpFile Does Not Exist:" & vbLf & inpFile & ".inp", vbExclamation, "Macro Ending"
Exit Sub
End If

ShellAndWait exe & " " & _
inpFile, 1000, vbNormalFocus, PromptUser
'ShellAndWait Environ$("comspec") & " /c " & exe & " " & _
inpFile, 1000, vbNormalFocus, PromptUser
'ShellAndWait Environ$("comspec") & " /c " & exe & " " & _
inpFile, 1000, vbHide, PromptUser

MsgBox "Did the shell wait?"
End Sub

aerodoc
10-21-2010, 08:18 PM
Ken,

Well it looks promising. I coupled your code with Chip Pearson's (just checking if that was your intent). I did the normal "save as" to the flash drive and could generate the problem that I had always witnessed with the other code I was using. The only problem is that Chip's is quite long and it will take some time to digest it and implement it, but the proof of concept appears to work.

I was also having another error, which I cannot duplicate on my system but that others were seeing. I will have them check it out and see if this fixed that problem at the same time.

Thanks very much! This has been quite a problem for me and it looks like it may have been resolved.

Kenneth Hobs
10-22-2010, 11:21 AM
I wouldn't let complex code issues bother me. The code is modular in that you can put Chip's code into a Module and it will work fine.

Many built-in functions are very complex. You just don't see the code that was used to create them.

aerodoc
10-23-2010, 10:09 AM
Ken,

That is fair. Thanks a lot for the help so far. Now that this issue may be resolved, I have an even more perplexing one (perhaps I should make a new topic?)

On some systems, the shell will not run, regardless of the approach taken. For the latest attempt, it fails at this point in Chip Pearson's code:

ProcHandle = OpenProcess(SYNCHRONIZE, False, TaskID)
If ProcHandle = 0 Then
ShellAndWait = ShellAndWaitResult.Failure
Exit Function
End If

My best guess is that the computer system/company has incorporated something that restricts Excel VBA from actually running the shell command. Perhaps this is set by IT or anti-virus, etc.? Might you have any idea?