Running Batch File from within Excel

[Chris]

I'm trying to run a batch file from within Excel. The batch file zips
up two files and renames the zip file with the date and time. It
works fine when run in a DOS box, but when run from Excel, all I get
is a DOS box flashing up *very* briefly, and that's it: the zip file
isn't created.

I'm very much a novice when it comes to VBA. I've tried various bits
of code that I've found, but mostly they are smilar to this:

Sub RunBatchFile()

Dim RetVal
RetVal = Shell("G:\From G Data\Data Files & Test Programs\Work
Data\zipbatch.bat", 1)

End Sub

I've also tried some 'Shell and Wait' code which I have to admit were
a bit over my head. However, one of them ran the Windows Calculator,
and didn't complete until you'd closed Calculator. That worked
perfectly, but when I substituted the name of my batch file (together
with its full path) for "calc.exe", again, all I got was a very brief
DOS Box, and no output zip file.

If it's any help, my batch file looks like this:

"C:\Program Files\System Utilities\WinZip\wzzip" "g:\From G Data\Data
Files & Test Programs\Work Data\temp.zip" files "g:\From G Data\Data
Files & Test Programs\Work Data\London 9100 Revisions.xls" "g:\From G
Data\Data Files & Test Programs\Work Data\test auto.xls"

set d=%date:~-4,4%%date:~-7,2%%date:~0,2%
set d=%d: =_%
set t=%time:~0,2%%time:~3,2%
set t=%t: =0%

RENAME "g:\From G Data\Data Files & Test Programs\Work Data\temp.zip"
"IF098_132%d%%t%.zip"

pause

Any ideas where I might be going wrong?

Many thanks in advance for any help,

Chris.

 

[RB Smissaert]

RetVal = Shell("G:\From G Data\Data Files & Test Programs\Work
Data\zipbatch.bat", 1)

Is this a valid file path. I am thinking of the & here.

Otherwise try with code like this:

Option Explicit
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadId As Long
End Type

Private Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long


Private Declare Function WaitForSingleObject _
Lib "kernel32" (ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As
Long

Private Declare Function CreateProcessA _
Lib "kernel32" (ByVal lpApplicationName As String,
_
ByVal lpCommandLine As String, _
ByVal lpProcessAttributes As Long,
_
ByVal lpThreadAttributes As Long,
_
ByVal bInheritHandles As Long, _
ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, _
ByVal lpCurrentDirectory As
String, _
lpStartupInfo As STARTUPINFO, _
lpProcessInformation As
PROCESS_INFORMATION) As Long

Private Declare Function GetExitCodeProcess _
Lib "kernel32" (ByVal hProcess As Long, _
lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = 32

Function ExecCmd(strAppName As String, _
lmSecsWait As Long, _
Optional bShowWindow As Boolean, _
Optional strCommandLineArg As String) As Long

'will start an external process and
'wait till this process is finished
'returns > -1 if successfull and -1 if not
'-----------------------------------------
Dim proc As PROCESS_INFORMATION
Dim Start As STARTUPINFO
Dim lReturn As Long

With Start
.cb = Len(Start)
.dwFlags = 1
If bShowWindow Then
.wShowWindow = 1
End If
End With

lReturn = CreateProcessA(strAppName, _
strCommandLineArg, _
0, _
0, _
1, _
NORMAL_PRIORITY_CLASS, _
0, _
vbNullString, _
Start, _
proc)

lReturn = WaitForSingleObject(proc.hProcess, lmSecsWait)
GetExitCodeProcess proc.hProcess, lReturn
CloseHandle proc.hThread
CloseHandle proc.hProcess
ExecCmd = lReturn

End Function


RBS

 

[Chris]

Data\zipbatch.bat", 1)

Is this a valid file path. I am thinking of the & here.

Otherwise try with code like this:

Thanks very much for taking the trouble to reply. Actually it turns
out that the '&' *was* causing the failure. I tried on a folder with
no ampersand in its name, and everything worked perfectly.

I never expected that, as the batch file itself was quite happy with
the ampersand in the path name.

This shouldn't be a problem in practice, as I don't think any of the
folders I will actually be using (as opposed to the one I tried for
testing - bad choice!!) have ampersands in their name.

But, is there a way of using a path with an ampersand in it?

Anyway, thanks again for your help - it was invaluable!

Chris.

 

[Mike S]

Thanks very much for taking the trouble to reply. Actually it turns
out that the '&' *was* causing the failure. I tried on a folder with
no ampersand in its name, and everything worked perfectly.
I never expected that, as the batch file itself was quite happy with
the ampersand in the path name.
This shouldn't be a problem in practice, as I don't think any of the
folders I will actually be using (as opposed to the one I tried for
testing - bad choice!!) have ampersands in their name.
But, is there a way of using a path with an ampersand in it?
Anyway, thanks again for your help - it was invaluable!
Chris.

I hope nobody minds me jumping in, you might try using VB to change to
the folder where the batch file reside, then running it, maybe something
like this (but I don't know about the other arguments to ExecCmd so make
sure you use values you like):

CurDir "C:\From G Data\Data Files & Test Programs\Work Data\"
ExecCmd "zipbatch.bat", 15, True, ""

 

[Mike S]

I hope nobody minds me jumping in, you might try using VB to change to
the folder where the batch file reside, then running it, maybe something
like this (but I don't know about the other arguments to ExecCmd so make
sure you use values you like):

CurDir "C:\From G Data\Data Files & Test Programs\Work Data\"
ExecCmd "zipbatch.bat", 15, True, ""

Apologies, CurDir works in VB6 but not VBA I test this in an Excel 2000
macro and it worked, so I think this may work for you:

Public Sub doin()
Dim path As String
ChDir "C:\From G Data\Data Files & Test Programs\Work Data"
path = CurDir
MsgBox path
End Sub

ChDir "G:\From G Data\Data Files & Test Programs\Work Data\"
ExecCmd "zipbatch.bat", 15, True, ""

Sorry for not testing before posting, I need to remember that there are
some differences between VB6 and VBA.

 

[Chris]

Apologies, CurDir works in VB6 but not VBA I test this in an Excel 2000
macro and it worked, so I think this may work for you:

Public Sub doin()
Dim path As String
ChDir "C:\From G Data\Data Files & Test Programs\Work Data"
path = CurDir
MsgBox path
End Sub

ChDir "G:\From G Data\Data Files & Test Programs\Work Data\"
ExecCmd "zipbatch.bat", 15, True, ""

Sorry for not testing before posting, I need to remember that there are
some differences between VB6 and VBA.

Mike - thanks for your trouble. I did say you were dealing with a
novice, and, to be honest, simply not using ampersands in folder names
is a much simpler solution for me!

But I do appreciate your time. Thanks again.

Chris.