Avatar of Eddie Antar
Eddie Antar
Flag 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
Windows 10VBAMicrosoft Access

Avatar of undefined
Last Comment
Eddie Antar

8/22/2022 - Mon
Ryan Chong

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)

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

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Eddie Antar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Eddie Antar

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