Sunday, July 06, 2008

Batch Files

By all accounts, DOS is dead. But since cmd.exe is still part of Windows, you can still write DOS batch programs to automate certain tasks.

For example, I am working with an Access application that is rebuilt every month. The previous process was to have the user navigate to a shared drive and use Windows Explorer to copy a new version of the MDB file and then paste it to a specific folder on their C: drive.

The new process uses a simple batch file like this:

@ECHO OFF
CLS
ECHO.
ECHO *** XYZ file setup program ***
ECHO.
IF NOT EXIST C:\XYZ MD C:\XYZ

IF NOT EXIST C:\XYZ\XYZ.MDB GOTO NOFILE
ECHO.
ECHO You already have a copy of XYZ on your computer
ECHO ===============================================
ECHO.
ECHO Do you want to replace your existing copy?
ECHO ------------------------------------------
GOTO HASFILE

:NOFILE
ECHO *** COPYING FILE, PLEASE WAIT ***
COPY /Y \\MYSERVER\XYZ\XYZ.MDB C:\XYZ
GOTO END

:HASFILE
ECHO Y = Yes, replace my old file
ECHO N = No, keep my existing copy of the file
ECHO.
SET CHOICE=
SET /P CHOICE= Type the letter and press {ENTER}
IF /I '%CHOICE%'=='Y' GOTO NOFILE
ECHO.
ECHO (Nothing was copied or replaced)
ECHO.
:END
PAUSE
EXIT



For a new user, the folder is created if it does not exist.
Then, if the file exists the user is prompted to either overwrite or keep their existing copy.

Without the CHOICE statement, DOS would ask the overwrite question as
Yes/No/All
and this avoids the confusing "All" option.

9 comments:

grovelli said...

Hi Bill, can you run batch files from within a VBA routine? If so, does the execution of the VBA code stop until the batch file has run all its instructions?

Bill Mitchell said...

Yes, you can run batch files from VBA using something like this:

Dim s As String
s = "cmd.exe /c C:\BILL\MyBatch.bat"
Shell s, vbNormalFocus

but VBA won't wait. I think you'd have to use a Windows API call to look for the "cmd" title bar, and loop until it closes.

grovelli said...

Thanks Bill, do you already have a link to such Windows API call?

grovelli said...

Also(if you don't mind :-)), with reference to your http://www.wvmitchell.com/tips/Network%20-%20simulating%20a%20network%20drive.htm
How can you achieve that in XP?
I've tried Start > Run > GPEDIT.MSC
Computer Configuration > Windows Settings > Scripts (Startup/Shutdown)
but I don't see how to edit the Autoexec file in there.

Bill Mitchell said...

Look at the post by "Sir Loin" at http://www.vbforums.com/showthread.php?t=340769 it shows how to use FindWindowA to find a window. Your VBA could use that function to find the batch window and then loop until it closes. Don't forget to put a DoEvents in your loop code.

In XP, autoexec.bat is usually an empty zero-byte file but you can just edit with NotePad to put the SUBST statement in there.

grovelli said...

Thanks again, I've tried searching for Autoexec.bat on my hard drive, with the options for hidden and system files and folders enabled to no avail.

Bill Mitchell said...

That's very interesting. I've looked at four XP machines, three had it but one did not.

You'll just have to create the file youself.

grovelli said...

Thanks Bill. Do I place it on c:\ or does it have to be put in a particular folder?

Bill Mitchell said...

It needs to be on the C: drive,

C:\Autoexec.bat

it is not case-sensitive.