CODEWITHSUNDEEP
windowsvbaexcelbatch-file
L.P
L.P

Reputation: 43

Call specific excel cells in Shell commands with VBA

I have a bat file that I wish to execute from VBA. In my workbook is the name of the batch file : test.bat. Since test.bat and my workbook will change folders I am using

ThisWorkbook.Path

to always have the current directory.

However I am unable to insert the cell which contains the batch file name in the shell commands after putting ThisWorbook.Path.

Here is my code so far :

Sub helpme ()

Shell "cmd.exe /k cd " & ThisWorkbook.Path & Range("B41")

End Sub

Note that with just

Shell "cmd.exe /k cd " & ThisWorkbook.Path

the code opens a cmd window and sets it to the directory of the workbook, so it's just a question of inserting the "test.bat" string contained in cell B41 to execute the bat file.

Thank you very much for your help, I hope it's an easy fix :)

Upvotes: 0

Views: 1181

Answers (2)

Tim Williams
Tim Williams

Reputation: 166331

To add to imtheman's answer: if your workbook path contains spaces then you should wrap the path in quotes

Sub helpme ()

    'Shell "cmd.exe /k cd """ & ThisWorkbook.Path & "\" & Range("B41") & """"
    'if you just want to run the bat file...
    Shell """" & ThisWorkbook.Path & "\" & Range("B41") & """"

End Sub

Upvotes: 1

imtheman
imtheman

Reputation: 4843

ThisWorkbook.Path doesn't have a trailing \

Sub helpme ()

Shell "cmd.exe /k cd " & ThisWorkbook.Path & "\" & Range("B41")

End Sub

Upvotes: 1

Related Questions