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
LVL 5
eantarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

0
aranaCommented:
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.
0
eantarAuthor 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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eantarAuthor Commented:
Thanks for all the help guys! Good to have a place to ask these questions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 10

From novice to tech pro — start learning today.