Using Shell to invoke cmd script with argument

[srcLakeJake]

Hi,
I am trying to get an access form to initiate a .cmd script and pass two
arguments to it. The contents of the script files are shown below:

initdb2.cmd
-------------------------
db2cmd -c -w -i %1.bat %2
-------------------------

runreport.bat
-------------------------
if "%1" == "" goto noparm1
set Date=%1

db2 connect to <db> user <uid> using <pwd>

db2 -x "select amount, chargeaccountnum, date(decisiontimestamp) from
<table> where date(decisiontimestamp)='%Date%' and
chargeaccountnum='001845969' and substr(captureseqnum,1,3)='003' and
substr(depositaccountnum,1,3)='001' order by amount desc" >>
c:\temp\eeHousehold\hh.txt

db2 disconnect <db>
goto exit1

:noparm1
echo error!

:exit1
-----------------------

when i call

c:\>initdb2 runreport 10/25/2007

from a standard cmd prompt, the scripts work properly. i have tried a number
of different calls from vba to invoke this command - none have actually
executed the actions in the scripts.

My most recent attempt was this:


Set objShell = CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec("%comspec% /c
\\homeserver\shared\AcctSvcs\Jake\initdb2.cmd runreport " & sInput)

Does anyone have any ideas how i can get this working? Any help is
appreciated.

Jake

 

[John Nurick]

Hi Jake,

I'd have thought that something like this would do it:

Dim Cmd As String
Dim FirstArg As String, SecondArg As String

...
'Assemble command string using quotes in case
'arguments contain spaces
Cmd = """D:\folder\initdb2.cmd"" " _
& """" & FirstArg & """ """ & SecondArg & """"

Shell "cmd /C " & Cmd

If you're not sure that CMD.EXE will be in the path, you could use

Shell Environ("ComSpec") & " /C " & cmd