Execute .bat file from Excel VBA Macro

7
votes

Im having a problem with my excel vba macro. I need it to execute a batch file which is in the same folder as the excel workbook. The code works well sometimes. I don't know whats causing the error. Here's the code:

Sub writebatch()
  Sheets("code").Select
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs FileName:=ThisWorkbook.path & "\code.bat",
  FileFormat:=xlTextPrinter, CreateBackup:=False
  Application.DisplayAlerts = True
  ThisWorkbook.Saved = True
  Shell "cmd.exe /k cd " & ThisWorkbook.path & "&&code.bat"
  Application.Quit
End Sub

It writes the batch file, but then doesn't execute it. Only once I got the command window to not close and it said the code.bat file could not be found. So the changedir command worked. Is it possible to run cmd.exe and run the code.bat with the relative path without having to changedir?

5
vbaexcelbatch-file
not sure I understand. You have an Excel workbook which you are saving as a batch file and then your trying to run the "Excel" file as a .bat? – InContext
Is the string "&&" in "&&code.bat" supposed to mean anything special, or is that really part of the file name? – RBarryYoung

5 Answers

8
votes

First of all, when you launch a CMD instance you need to enclose the entire CMD argument if you use any type of operators (&):

CMD /K "command1 & command2"

And then enclose the sub-internal arguments, like this:

CMD /K "command "path with spaces" & command"

So you need to do something like this:

Shell "cmd.exe /k ""cd " & """ & ThisWorkbook.path & """ & " && code.bat"""

Notice I've used """ to escape a quote, but I don't know the way to escape a quote in VBA.

PS: remember to also enclose the code.bat if you have spaces, but ONLY if you have spaces.

4
votes

I'm pretty sure the problem is down to this line

Shell "cmd.exe /k cd " & ThisWorkbook.path & "&&code.bat"

You need a space in front of the && to separate it from the cd command and after it to separate it from the code.bat.

Shell "cmd.exe /k cd " & ThisWorkbook.path & " && code.bat"
3
votes

Shell "cmd.exe /k cd /d" & ThisWorkbook.path & "&& code.bat"

here, without /d cmd will open in document folder. by /d it will open in d drive, you may change this as per your easy.

2
votes

One way to insert a quote(") into a string is by using the character code conversion function Chr(34), 34 being the ASCII value for quotes(")

0
votes

I hope it will useful for you.

Dim sfilename, fileName As String
Sub Run_file_bat()
    fileName = "" & ThisWorkbook.Path & "\code.bat" & ""

    VBA.Shell "Explorer.exe " & fileName, vbNormalFocus

End Sub