Running .bat from vba

[TiaXL]

Hi All

I am having problems running a batch file from excel vba using the shell command. The cmd windows appears briefly but it does not download the file following the commands in the batch file. When I run it manually it works fine so I know the batch commands are OK. my vba code is

Sub modTXTFile()
Dim sFile As String
Dim sText As String
Dim iFileNum As Integer
Dim wsh As Object
Dim statusnum As String
 
sFile = "S:\mypath\Files.bat"
sText = Workbooks("File.xls").Sheets("sheet1").Range("o1").Value
iFileNum = FreeFile
Open sFile For Output As iFileNum
Print #iFileNum, sText
Close #iFileNum

Set wsh = CreateObject("WScript.Shell")
statusnum = wsh.Run(Command:="%comspec% /c " & "S:\MyPath\Files.bat", WindowStyle:=5, waitonreturn:=True)

Please note I have modified the file paths to censor information.

Can anyone offer any assistance ?

 

[jfarc]

The little bit of coding I've done with running a .bat command from Excel VBA is it errors out when any part of the command path has a space in it. So, the actual path\file name you are using could be the problem.

Plus, with the limitations of .bat commands, I was able to re-write the code that was inside the .bat and run those instructions directly from VBA. Is this a possibility with your code?

 

[Andrew Poulsom]

You should be able to use:

statusnum = Shell(sFile)

 

[TiaXL]

hmm I guess it is possible to recreate the batch commands in vba, maybe I will try that! Thanks for the replys

 

[TiaXL]

You should be able to use:

statusnum = Shell(sFile)

This worked perfectly, thanks mate! (goes to show simplest usually does it) I introduced a wait into my code so it doesnt just kick off the .bat file and then carry on with the code and it is functioning perfectly.