Link to home
Start Free TrialLog in
Avatar of Eddie Antar
Eddie AntarFlag for United States of America

asked on

Problem using the Shell command in Access VBA running Windows 10

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

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

Avatar of Arana (G.P.)
Arana (G.P.)

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.
ASKER CERTIFIED SOLUTION
Avatar of Eddie Antar
Eddie Antar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eddie Antar

ASKER

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