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

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

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

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

HELP.

Chuckles123

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

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

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

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

It's amazing! AFTER you pointed this out, your solution seems so obvious. I will go into my office tomorrow and run it, but I am confident it will work. Many THANKS!

A couple of questions:

- Why does the command prompt default to the ...\My Documents folder?

- I am not positive, but I seemed to be getting identical results to your code when I omitted the following characters: 'Environ("comspec") & " /k " &'. Any comments? Also, when I was Googling, I saw that several people were using 'Environ$("comspec")'. What does the '$' do for them?

Thanks again,
Chuckles123

The $ means that it's a string function. It'll be microseconds quicker.

If you do Windows|Start|Run
cmd (winXP/WinNT)
or
Command (win9x)

And then type:
Set | More
(set followed by the broken vertical bar followed by More)

You'll be able to page through all the stuff that's in your environment.

Comspec represents the folder that holds the cmd.exe or command.com file.

I like going through the comspec for shelling to .bat files. (I don't recall if
I had trouble, but that's my preference now. Not too good of a reason, huh?)



Chuckles123 wrote:
>
> It's amazing! AFTER you pointed this out, your solution seems so
> obvious. I will go into my office tomorrow and run it, but I am
> confident it will work. Many THANKS!
>
> A couple of questions:
>
> - Why does the command prompt default to the ...\My Documents folder?
>
> - I am not positive, but I seemed to be getting identical results to
> your code when I omitted the following characters: 'Environ("comspec")
> & " /k " &'. Any comments? Also, when I was Googling, I saw that
> several people were using 'Environ$("comspec")'. What does the '$' do
> for them?
>
> Thanks again,
> 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

On Fri, 8 Jul 2005 23:00:16 -0500, Chuckles123
<[email protected]> wrote in
microsoft.public.excel.programming:

>It's amazing! AFTER you pointed this out, your solution seems so
>obvious. I will go into my office tomorrow and run it, but I am
>confident it will work. Many THANKS!
>
>A couple of questions:
>
>- Why does the command prompt default to the ...\My Documents folder?

CMD.EXE is designed that way. I believe it establishes %HOMEDRIVE% and
%HOMEPATH% from the registry (HKLM\SOFTWARE\Microsoft\Windows
NT\CurrentVersion\ProfileList\{SID}\ProfileImagePath) and that's where
it starts. You can change it on the fly by invoking it
CMD PUSHD d:\folder1\folder2 & mybatfile.cmd
or similar (e.g. with CD /D), but it is much better practice that a
batch file establish its current directory itself if it needs to.

>- I am not positive, but I seemed to be getting identical results to
>your code when I omitted the following characters: 'Environ("comspec")
>& " /k " &'. Any comments? Also, when I was Googling, I saw that
>several people were using 'Environ$("comspec")'. What does the '$' do
>for them?

Dave Peterson already pointed out the purpose of Environ$().

As for using the environment variable COMSPEC: there is a very good
reason for it, as Microsoft OSs always allowed alternative command line
interpreters/shells. COMMAND.COM/CMD.EXE are just free samples of what a
real CLI might be - just as Notepad/Wordpad are free examples of proper
editors/word processors. The user's preferred CLI is known by inspecting
%COMPSPEC%.

As for omitting COMSPEC or CMD in the invokation altogether: you then
rely on the default action associated in the registry with .BAT or .CMD
files. In most systems, that association would be with CMD.EXE, but it
doesn't have to be - for security reasons it might well be with Notepad.

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Thanks guys,

I used G:, CD ... at the beginning of my batch file and it works great. When I was Googling, I saw that several people were making exactly this same error.

I have learned more from this simple (now, I can say that) application than I have from several much more complicated ones.

Thanks again,
Chuckles123

I have a program that will shell to a batch file and this thread was
helpful.

The program may be installed with different paths on different machines
so I am working on creating the batch file within VB to get the active
path.

I'm finding that if I set up the batch file to work with a drive other
than C that if I copy the program to the c drive it no longer works and
vice versa.

The problem comes from if the program is on the c drive the command
prompt is at ...My Documents. This is the homepath or homedrive
setting. I can't change to another directory on the c: drive when this
is the case or invoke the invoke the program I want to run with a fully
qualified path.

For instance if my appliction is on the c: drive then a batch file
consisting of

cd c:\
pathtofiletorun
filetorun

works,

However,

If I copy the folder that all these files are in to another drive say e:
and try it it doesn't work as the prommpt stays at ...My Documents

In that case I can I can set up the batch file as:

e:
filetorun

In this case after specifying e: the prompt is at the active directory.

The program files are only copied from one place to the other. Would
actually installing them and writing their location to the registry
affect this?

Thanks for any help

Is there some way for it to work for both situations?


*** Sent via Developersdex http://www.developersdex.com ***