Calling *.BAT file from VBA app

ASP Free Forums Sponsor:

January 30th, 2007, 09:41 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Calling *.BAT file from VBA app

Hi,

I have a old-style dos bat file that I need to call from a VBA script. Not sure how to do - shell out to it? The VBA script is actually going to create the BAT file on the fly in a particular folder on the local machine and subsequently have it to execute. Lastly the BAT file will be deleted.
Creating and deleting is no problem, but I am used to instantiating and using DLLS, not old time BAT files, so a little clueless....

thanks for any thoughts,

fv

January 30th, 2007, 10:53 PM

Doug G

Grumpier Old Moderator

ASP Free God 11th Plane (10000 - 10499 posts)

Join Date: Sep 2003

Posts: 10,143

Time spent in forums: 3 Weeks 4 Days 23 h 19 m 36 sec
Reputation Power: 181

Look for wscript.shell

__________________
======
Doug G
======
I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain

January 30th, 2007, 11:00 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Thanks, Doug. Would something like:
dim ret as integer
ret = Shell("C:\Directory\my.bat",1)

work as well?

thanks,

fv

January 31st, 2007, 11:31 AM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Doug G

Look for wscript.shell

Still looking for an answer....
So far I have:

Code:

Dim Ws as Object
Dim batFileName as String

batFilename = "C:\Program Files\Blah\Blah.BAT"

Set Ws = CreateObject("WScript.Shell")
Ws.Exec (batFileName)

I don't seem to get any errors reported, but niether does the bat file seem to run.

Any ideas?

thanks,

fv

January 31st, 2007, 02:09 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Folding Points: 206875 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

There's no need to instantiate the Wscript Shell from VBA. Go back to your original code and change it slightly.

vb Code:

Original - vb Code
Dim ret As Integer ret = Shell "C:\Directory\my.bat"

Dim ret As Integer
ret = Shell "C:\Directory\my.bat"

If you want to wait for your batch to complete before continuing, make sure it includes and END or EXIT and use this:

vb Code:

Original - vb Code
Dim ret As Integer ret = Call Shell "C:\Directory\my.bat"

Dim ret As Integer
ret = Call Shell "C:\Directory\my.bat"

__________________

Click the image if at any point you don't like my decision.

Scripting problems? Windows questions? Ask the Windows Guru!

Subscribe to Nilpo's Live Article Feed

January 31st, 2007, 10:11 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Nilpo

There's no need to instantiate the Wscript Shell from VBA. Go back to your original code and change it slightly.

vb Code:

Original - vb Code
Dim ret As Integer ret = Shell "C:\Directory\my.bat"

Dim ret As Integer
ret = Shell "C:\Directory\my.bat"

If you want to wait for your batch to complete before continuing, make sure it includes and END or EXIT and use this:

vb Code:

Original - vb Code
Dim ret As Integer ret = Call Shell "C:\Directory\my.bat"

Dim ret As Integer
ret = Call Shell "C:\Directory\my.bat"

Ah, excellent information - thank you very much! I had to look for a dusty old book on MS-Dos stuck in a closet yesterday to even remember what a BAT file looked like!

thanks again,

FV

February 1st, 2007, 01:22 AM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

No problem at all. Glad I could help.

What exactly is your batch file doing? You can almost certainly do it all in VBA.

February 1st, 2007, 05:08 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Nilpo

No problem at all. Glad I could help.

What exactly is your batch file doing? You can almost certainly do it all in VBA.

Trying out some file compare software that does not have an ActiveX interface - you either run the software on the client via a GUI or you can run from command line:

HTMLMATCH.EXE <originalFile> <revisedfile> <output file to show differences>

Had a bunch of these to compare, so the VBA script reads through the directory of original files, gets the name of each and builds the BAT fly on the fly. Then it shells out and runs the BAT file and lastly deletes the BAT file.

fv

February 1st, 2007, 06:14 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

You can do that in VBA, but you're probably saving yourself a lot of code work this way.

#10

February 1st, 2007, 09:33 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Nilpo

You can do that in VBA, but you're probably saving yourself a lot of code work this way.

Yep - why reinvent the wheel. There are tons of compare utilities out there that will create a nice html output document highlighting differences between original and revised.

fv

#11

February 1st, 2007, 10:58 PM

Nilpo

Shaolin Scripter!™

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,048

Time spent in forums: 5 Days 12 h 32 m 33 sec
Reputation Power: 555

I agree. I generally try to avoid the use of third-party programs when I can help it, but that's just for portability reasons. In your case, I believe you're working with the best solution.

#12

February 2nd, 2007, 11:40 PM

fractalvibes

$fractalvibes is offline$

Registered User

Join Date: May 2004

Posts: 12 $fractalvibes User rank is Just a Lowly Private (1 - 20 Reputation Level)$
Time spent in forums: 4 h 6 sec
Reputation Power: 0

Quote:

Originally Posted by Nilpo

I agree. I generally try to avoid the use of third-party programs when I can help it, but that's just for portability reasons. In your case, I believe you're working with the best solution.

I understand that sentiment entirely. But project constraints will force one to use 3rd party components for some things that really would be a long project unto themselves to develop and perfect. I probably could write the file compares, file upload utilities for classic ASP, any file to PDF conversion utilities, etc. given a lot of time - but - when I need this working by next week and not next year, the choice is already made. And most of these
3rd party components are pretty well tried and true.
fv