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.
LVL 29
leonstrykerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
Provide the code that Excel runs the batch file...
0
leonstrykerAuthor Commented:
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

0
oBdACommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leonstrykerAuthor Commented:
Will try it when I am at my PC and let you know. Thanks.
0
leonstrykerAuthor Commented:
Worked. Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.