We help IT Professionals succeed at work.

Problem using the Shell command in Access VBA running Windows 10

2,719 Views
Last Modified: 2017-04-28
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

CERTIFIED EXPERT

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
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

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

CERTIFIED EXPERT

Commented:
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.
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions