How to run a batch file or VBS from Access VBA code?

[Ben]

Hi all,

Just two questions:
1) How to run a series of batch file about 6 of them from Access VBA code?
2) How to run a series of VBScripts from Access VBA code?

I tried using the Shell command, but it is not working.
Thanks for your help.

Ben

--

 

[Douglas J. Steele]

Let's see your code. Shell should work with batch files.

For VBScripts, you'll probably have to explicitly invoke cscript.exe (or
else use the ShellExecute API, which will run the scripts with whatever the
extension has been registered with).

I'm curious, though. What can you do in VBScript that you can't do in VBA?
Even reproducing the functionality of the batch files may be possible in
VBA.

 

[Ben]

Douglas,
I had something like this: shell("c:\Rename_myfile.bat"), which basically
renames a .txt file into .bat. But after running this command, I don't see
the .bat file, I still see the .txt file

Another one is like this: shell("c:\ftp_myfile.bat"), didn't seem to work.
I inherited a series of old ftp bat files, but I find it cubersome to use
the.bat files because I have to change the name of the files, for each file I
ftp. What I really would like to do is to replicate the ftp batch commands
inside Access VBA, so that I can just write a loop and ftp all the files like:

ftp file1
ftp file2...etc

the old batch files also write to a log file at the end of the script, and
that is something I want to simulate in Access VBA, so that for each of the
files: file1, file2, ..., etc, I can see the "successful" log message status
of each of these files after they are ftp-ed.

Any suggestions or code snippets you can point me to is greatly appreciated.
Thanks again.

Ben

 

[John Nurick]

IIRC to run a .bat or .cmd file you must invoke the Windows command
interpreter, e.g.

Shell "cmd.exe /C ""C:\ftp_myfile.bat"""

To run a VBScript I think you can invoke cscript.exe - but except for
one-offs it's almost certainly better in the long run to incorporate the
VBS code into your VBA procedure: it usually doesn't take much
translation.

One particular problem I've encountered with shelling to cmd.exe is that
AV programs may interfere. Currently I'm using "Panda Titanium"
considers this a hostile act and blocks it with no possibility of
exemption, the bastard - and no cooperation from tech support. So I'm
looking for another AV provider.

 

[Ben]

Thanks John for the command line syntax and info about the AV.