Link to home
Start Free TrialLog in
Avatar of leonstryker
leonstrykerFlag for United States of America

asked on

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.
Avatar of unknown_routine
unknown_routine
Flag of United States of America image

Provide the code that Excel runs the batch file...
Avatar of leonstryker

ASKER

Sure
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

ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Will try it when I am at my PC and let you know. Thanks.
Worked. Thank you for your help.