Run .bat file from Excel VBA, dynamically

[raghuram.star]

Hi

I have problem in running ".bat" files through VBA, but when I run ".bat" file it executing properly. but not through VBA. Below is the code I'm using

Sub RunBatFile()    Dim txtFpath, FilePath As String


    txtFpath = wksCmdJen.Range("C12").Value ' given path is C:\Elements
    FilePath = txtFpath & "\FetchElements.bat"

    ChDrive "C"
    RSP = Shell(Environ$("COMSPEC"), vbNormalFocus)
    Application.Wait Now + TimeValue("00:00:01")
    SendKeys "CD " & txtFpath & "{ENTER}", True
    Application.Wait Now + TimeValue("00:00:01")
    SendKeys "start " & FilePath & "{ENTER}", True
    Application.Wait Now + TimeValue("00:00:01")
End Sub

Another peace of code I used is

Call Shell("cmd " & FilePath, vbNormalFocus)

But none of them are working... I have used this code a long back, but not sure what I'm missing....

Is there a way to run a .bat file directly, without opening two CMD windows? or a way to close the first CMD window (which is been opened through VBA), after running the .bat file (second CMD window is closed using Exit Command with in .bat file)

Thank you

 

[raghuram.star]

No response....
I think it is not possible.....

 

[poolhall]

you don't need to use SendKeys at all. You want to write a BAT file containing all commands you need (cd, etc) and then call the BAT file.

Sub WriteAndRunBatFile()
Dim fs As Scripting.FileSystemObject
Dim file As TextStream
Dim retVal
Dim strFilePath As String

strFilePath = "C:\Elements\FetchElements.bat"

Set fs = New Scripting.FileSystemObject 'Requires a reference to Microsoft Scripting Runtime
'Set fs = CreateObject("Scripting.FileSystemObject") 'does not require the reference

Set file = fs.CreateTextFile(strFilePath)

file.WriteLine "blah-blah"
file.WriteLine "blah-blah"
file.Close

retVal = Shell(strFilePath)

End Sub

However, if you don't need to add anything to the existing batch file from Excel, just run it:

Dim retVal
retVal = Shell(strFilePath)

 

[raghuram.star]

@ poolhall

When I give the code

Dim retVal
Dim FileFolder, FileName, FilePath as String

FileName = "FetchElements.bat" '- FileName is constant which can be hard coded
FileFolder = "C:\ElementsFetched\" ' - FileFolder is user defined, which will referred to a cell in excel sheet as " = Sheet1.Range("B5").value

FilePath = FileFolder & FileName

retVal = Shell(FilePath)

Batch file is not executed. CMD window is just blinking and disappeared. Please let me know if I'm missing anything...

 

[poolhall]

This code is fine. To test it, I put the line "C:\windows\system32\calc.exe" in the batch file and Calculator opened. So, I guess there must be something wrong with your batch file's content.

 

[raghuram.star]

@ Poolhall

Thanks for the help, need to check it by altering the batch file. Hope for the best...

Thanks once again...

 

[poolhall]

you're welcome

 

[taurean]

This is one more example of DOS executed through VBA:
VBA Express : Multiple Apps - Command Line from VBA (DOS Prompt Commands)

 

[raghuram.star]

@ taurean

Thanks for sharing such an informative link. It helped me in better understanding about executing CMD via VBA, Thank you so much...