• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Running folder

I am running some code in VBA which executes a BAT files with a Shell command. The BAT calls some exe files. It is important that the Excel file be in the same folder as the BAT and the EXE files for everything to work.

My issue is that if the Excel file is opened from the Recent Files list and the code is executed, the start up location is different than the one where the Workbook has been saved. This cause the BAT code to fail since the EXE files can not be found. The only thing that seems to help is to do a Save As to re-save the workbook in the proper folder.

I have tried adding ChDir command before running the code, but that does not seem to help.

Solutions and suggestions are welcome.
  • 3
1 Solution
Provide the code that Excel runs the batch file...
leonstrykerAuthor Commented:
Sub RunMorningFiles()
Dim retVal As Variant
Dim strBatFile As String, strFile As String
    With Worksheets("CDOImport")
        strBatFile = "C:/CSES/Client/batMorningLoad.bat"
        ChDir "C:/CSES/Client/"
        strFile = Format(.Range("rngDate").Value, "yyyymmdd")
        retVal = Shell(strBatFile & " " & strFile, vbMaximizedFocus)
    End With
End Sub

Open in new window

Somewhere at the beginning of your batch file (or post the file here), add the line
cd /d "%~dp0"
That will change the current directory to the directory where the bat file is actually located.
leonstrykerAuthor Commented:
Will try it when I am at my PC and let you know. Thanks.
leonstrykerAuthor Commented:
Worked. Thank you for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now