Problem using the Shell command in Access VBA running Windows 10

eantar
eantar used Ask the Experts™
on
Hi all, I'm stumped as to why this doesn't work. It works without a hitch on the command line, but when I run this through the shell command in Access, it runs, but doesn't produce the results as in a command line window.

Here's the full command line prompt for the software that I'm shelling to:
C:\Program Files\MSG MCE Software I10>mce.bat -i "/ClassMSystems/QualCode/MCE Output/test.txt" -o "/ClassMSystems/QualCode/MCE Output/testout.txt"

Open in new window


Here's the VBA code:
Shell "C:\Program Files\MSG MCE Software I10\mce.bat -i ""/ClassMSystems/QualCode/MCE Output/test.txt"" -o ""/ClassMSystems/QualCode/MCE Output/testout.txt""", vbHide

Open in new window


in general there is a .bat file called that takes an input file (the path/name following the  -i) and puts out an output file (the path/filename following the -o)

I'm using the double quotes because my folder names have spaces. I'm lost.

It runs without errors, but the output file is not created. If I copy and paste the code to a command window and remove the double quotes it works fine.

Is there any way to stop or capture the output of a call with Shell? That might help me solve this.

Any ideas would be great appreciated.

-Eddie
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
you may try use Chr(34) instead for double quote:

Shell "C:\Program Files\MSG MCE Software I10\mce.bat -i " & Chr(34) & "/ClassMSystems/QualCode/MCE Output/test.txt" & Chr(34) & " -o " & Chr(34) & "/ClassMSystems/QualCode/MCE Output/testout.txt" & Chr(34) , vbHide

Open in new window


or use this:

Set objShell = CreateObject("Shell.Application")
    para = "-i " & Chr(34) & "/ClassMSystems/QualCode/MCE Output/test.txt" & Chr(34) & " -o " & Chr(34) & "/ClassMSystems/QualCode/MCE Output/testout.txt" & Chr(34) 
    objShell.ShellExecute "C:\Program Files\MSG MCE Software I10\mce.bat", para, "", "open", 1
    Set objShell = Nothing

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Eddie,

  One other suggestion in general; build the batch file on the fly, then execute the batch file.   This allows for easy troubleshooting because you can break point the code after the batch file is built and execute it from the command line to easily see the result.   You get absolutly no difference then between you VBA code and what you manually do.

 Little extra overhead, but it's worth it.   Here's an example which has a separate script file of commands and a batch file.   In your case, it would be a bit simpler in that you'd only be writing a batch file (this was doing FTP):

          ' Generate file names
20        strFTPScriptFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".txt"
30        strFTPCommandFile = "\FTP_" & AppShortName() & "_" & RoutineName & ".bat"
40        strFTPLogfile = "\FTP_" & AppShortName() & "_" & RoutineName & ".log"

          ' Write script file
50        intFileNum = FreeFile
60        Open strFTPScriptFile For Output As #intFileNum
70        Print #intFileNum, strUserName
80        Print #intFileNum, strPassword
90        If left$(strMode, 1) = "P" Then
100           Print #intFileNum, "passive"
110       End If
120       Print #intFileNum, "type " & IIf(strTransferType = "B", "binary", "ascii")
130       Print #intFileNum, "put " & Chr$(34) & strLocalFileName & Chr$(34) & " " & Chr$(34) & strFTPFilename & Chr$(34)
140       Print #intFileNum, "quit"
150       Close #intFileNum

          ' Write command file
160       intFileNum = FreeFile
170       Open strFTPCommandFile For Output As #intFileNum
180       Print #intFileNum, "@ftp -i -s:" & strFTPScriptFile & " " & strFTPSiteName & " > " & strFTPLogfile
190       Close #intFileNum

          ' Execute
200       lngHWnd = Shell(strFTPCommandFile, vbHide)
210       WaitWhileRunning (lngHWnd)

Open in new window

instead of SHELL try MSGBOX and see if you get the same output (exact same command line) as the one you use in your command line.
that will help you discard any missed quote or space.
Commented:
Hey all, thanks for your help! I actually resolved this yesterday. The .bat  file (not created by me) has some real specific codes and calls that require that the program be in the specific  Program Files sub-directory BEFORE I MAKE THE SHELL CALL! My app is in a completely different directory. I just did a simple ChDir before the Shell call and it worked!

Thanks for all the responses.

Best,
Eddie
PS Jim, that's great FTP code!

Author

Commented:
Thanks for all the help guys! Good to have a place to ask these questions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial