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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 17 Jan 2006 22:36 GMT

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

Signature

Reply to this Message

Douglas J. Steele - 17 Jan 2006 23:13 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi all,
>
[quoted text clipped - 6 lines]
>
> Ben

Reply to this Message

Ben - 18 Jan 2006 03:16 GMT

Douglas,

>> Shell should work with batch files

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.

>>Even reproducing the functionality of the batch files may be possible in VBA.

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

Signature

> Let's see your code. Shell should work with batch files.
>
[quoted text clipped - 16 lines]
> >
> > Ben

Reply to this Message

John Nurick - 18 Jan 2006 07:16 GMT

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.

>Douglas,
>
[quoted text clipped - 23 lines]
>
>Ben

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Ben - 18 Jan 2006 13:21 GMT

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

Signature

> IIRC to run a .bat or .cmd file you must invoke the Windows command
> interpreter, e.g.
[quoted text clipped - 44 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message