Execute .bat file from Excel VBA Macro

Question

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?

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
– InContext, Commented Nov 23, 2012 at 13:48
Is the string "&&" in "&&code.bat" supposed to mean anything special, or is that really part of the file name? — RBarryYoung
– RBarryYoung, Commented Nov 23, 2012 at 13:53

twasbrillig · Accepted Answer · 2013-05-12 09:56:15Z

8

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.

edited May 12, 2013 at 9:56

twasbrillig

19.2k9 gold badges47 silver badges71 bronze badges

answered Nov 23, 2012 at 15:17

ElektroStudios

20k39 gold badges222 silver badges462 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

josh.thomson Over a year ago

It's either blackslash '\' or to use single quotes to contain the double quotes '"'

Bali C · Accepted Answer · 2012-11-23 14:45:57Z

4

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"

answered Nov 23, 2012 at 14:45

Bali C

31.4k37 gold badges128 silver badges155 bronze badges

Comments

user3350522 · Accepted Answer · 2014-02-25 09:39:15Z

3

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.

answered Feb 25, 2014 at 9:39

user3350522

311 bronze badge

1 Comment

starsplusplus Over a year ago

Hi, thanks for contributing to the site. As it stands your answer is a little short of our guidelines for good answers. Would you be able to expand it at all?

Andrew Slentz · Accepted Answer · 2016-11-30 16:26:36Z

2

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(")

answered Nov 30, 2016 at 16:26

Andrew Slentz

1091 silver badge3 bronze badges

Comments

Peter Csala · Accepted Answer · 2021-10-25 08:54:20Z

1

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

edited Oct 25, 2021 at 8:54

Peter Csala

23.7k16 gold badges51 silver badges96 bronze badges

answered Oct 25, 2021 at 8:28

Duy Phuong

111 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Bala Bharathy · Accepted Answer · 2023-09-05 05:34:01Z

0

' Run Batch File

Sub RunBatchFile()

Dim batchFilePath As String
batchFilePath = "**actual path to your batch file**"

' Run the batch file and capture the process ID
Dim shellResult As Double
Dim shellProcess As Object
Set shellProcess = CreateObject("WScript.Shell")
shellResult = shellProcess.Run(batchFilePath, vbHide, True)

' Check if the batch file was executed successfully
If shellResult = 0 Then
    ' Batch file completed successfully
    MsgBox "Batch file completed successfully.", vbInformation
Else
    ' Batch file failed or returned an error code
    MsgBox "Batch file failed with error code: " & shellResult, vbExclamation
End If
End Sub

answered Sep 5, 2023 at 5:34

Bala Bharathy

11 bronze badge

Collectives™ on Stack Overflow

Execute .bat file from Excel VBA Macro

6 Answers 6

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related