Running a batch file in a given directory using VBA

The following should give you the effect you seek.

My test code is:

Option ExplicitSub 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 %1dir *.* >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.

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.