Run a dos batch file as soon as I exit/save the spreadsheet

[Lynda]

I need to be able to run a dos batch file as soon as I save and exit a
spreadsheet/workbook.

[Snake Plissken]

just writ down a line of code similar to following example:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
..
..
Call Shell("C:\file.bat", 1)
..
..
end sub

[Chuckles123]

My .bat is executed, but each command line is viewed very quickly and none of the command lines are executed.

HELP.

Chuckles123

[Norie]

Are you sure your batch file hasn't been executed?

[Dave Peterson]

You can keep that command window around by using something like:

Option Explicit

Sub testme()

Dim myBatFileName As String
myBatFileName = "C:\my file.bat"
Shell Environ("comspec") & " /k " & Chr(34) & myBatFileName & Chr(34), _
vbMaximizedFocus

End Sub

Change the /k to /c to dismiss that DOS window when it's done.

And fiddle with the vbMaximizedFocus to make it what you want. (See VBA's help
for Shell to see all the options.)

Chuckles123 wrote:
>
> My .bat is executed, but each command line is viewed very quickly and
> none of the command lines are executed.
>
> HELP.
>
> Chuckles123
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=384310

--

Dave Peterson

[Chuckles123]

I, with your help, am getting closer.

First, we are running W XP Pro, XCL 2002, M VB 6.3 on a network.
This application is an utility to determine if a W Scheduled Task has already been completed on any given day. At the time that the Shell Function is executed, there are no Workbooks open. My DOS batch file is in a folder on our G: Drive; it contains about 25 command lines that refer to various other files stored on the same drive.

I inserted the code you recommended and ran the macro; also, I inserted a 'PAUSE' at the end of my DOS batch file. The macro finds the correct batch file, but the command prompt is 'C:\Documents and Settings\myusername\My Documents>'.

I would prefer not to move my DOS batch file (the G: Drive is a network shared drive) and I also prefer not to change the path references in the batch file.

Is there a way to modify my macro to accommodate my situation?

Thanks for a response.
Chuckles123

[Dave Peterson]

Does that mean you want to change the drive and path?

If yes, then I'd include that in the .bat file:

rem If you want turn off the screenupdating
@echo off
G:
cd "G:\what ever\folder\you need"
rem rest of your .bat file after this...

======
But if you fully qualify your file names (include both the drive and folder),
then it shouldn't matter what the active drive and current folder is.

But it is sometimes easier to just change the drive and folder.

Chuckles123 wrote:
>
> I, with your help, am getting closer.
>
> First, we are running W XP Pro, XCL 2002, M VB 6.3 on a network.
> This application is an utility to determine if a W Scheduled Task has
> already been completed on any given day. At the time that the Shell
> Function is executed, there are no Workbooks open. My DOS batch file
> is in a folder on our G: Drive; it contains about 25 command lines that
> refer to various other files stored on the same drive.
>
> I inserted the code you recommended and ran the macro; also, I inserted
> a 'PAUSE' at the end of my DOS batch file. The macro finds the correct
> batch file, but the command prompt is 'C:\Documents and
> Settings\myusername\My Documents>'.
>
> I would prefer not to move my DOS batch file (the G: Drive is a network
> shared drive) and I also prefer not to change the path references in the
> batch file.
>
> Is there a way to modify my macro to accommodate my situation?
>
> Thanks for a response.
> Chuckles123
>
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
> View this thread: http://www.excelforum.com/showthread...hreadid=384310

--

Dave Peterson