Running a batch file in a given directory using VBA

Question

When I go to my batch file's location and open it, the batch file works. My batch file is simply:

cd .\data
dir/b/o:n > names.txt

As you can see, I'm in my current directory and moving down to the sub directory "data" and coping all the names and creating a file called names.txt.

When I say

shell "location of file" 

it opens the batch file, but the directory that is defaulted to is C:\my documents, so my commands won't work because it cannot find the sub directory. I want this to be a dynamic batch file, and therefore i need to write something in VBA that will open the batch file under its current directory or something to this effect.

How do I do this?

Answer 1

The following should give you the effect you seek.

My test code is:

Option Explicit
Sub TryShell()

  Dim PathCrnt As String

  PathCrnt = ActiveWorkbook.Path
  Call Shell(PathCrnt & "\TryShell.bat " & PathCrnt)

End Sub

My test batch file is named TryShell.bat and contains:

cd %1
dir *.* >TryShell.txt

I have placed my batch file in the same folder as the workbook containing my macro.

The statement PathCrnt = ActiveWorkbook.Path sets PathCrnt to the name of the directory containing the active workbook. You can set PathCrnt to whatever directory you require.

When I call Shell, I have added PathCrnt as a parameter.

In my batch file, I set the current directory to %1 which is the first parameter.

The dir command works as I wish because the current directory is my directory and not the system default directory.

Hope this is clear.

Answer 2

C:\My Documents is probably the directory where your speadsheet is located. If you add

ChDir "C:\TheFolderWhereYourBatchIs"

before launching your Shell command and that should work...

Alternatively, you could change your batch file to use an absolute directory instead of a relative one.