Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
On Mon, 16 Aug 2010 01:06:52 +0100, "RB Smissaert"
wrote: 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: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
On 8/16/2010 3:23 PM, Chris wrote:
On Mon, 16 Aug 2010 01:06:52 +0100, "RB Smissaert" wrote: 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: 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, "" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
On 8/16/2010 4:45 PM, Mike S wrote:
On 8/16/2010 3:23 PM, Chris wrote: On Mon, 16 Aug 2010 01:06:52 +0100, "RB Smissaert" wrote: 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: 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, "" 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Batch File from within Excel
On Mon, 16 Aug 2010 19:20:02 -0700, Mike S wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
batch file to ftp code not running - please help! | Excel Programming | |||
Running a Batch file from VBA | Excel Programming | |||
running a batch file | Excel Programming | |||
Running a batch file from VB | Excel Programming | |||
Running a batch file from Excel VBA | Excel Programming |