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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.)
 
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 ha
already been completed on any given day. At the time that the Shel
Function is executed, there are no Workbooks open. My DOS batch fil
is in a folder on our G: Drive; it contains about 25 command lines tha
refer to various other files stored on the same drive.

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

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

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

Thanks for a response.
Chuckles12
 
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.
 
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?)
 
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?
Click to expand...

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?
Click to expand...

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.
 
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
 
Back
Top