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.
Microsoft ExcelMicrosoft DOS

Avatar of undefined
Last Comment
leonstryker

8/22/2022 - Mon
unknown_routine

Provide the code that Excel runs the batch file...
ASKER
leonstryker

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
oBdA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
leonstryker

Will try it when I am at my PC and let you know. Thanks.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
leonstryker

Worked. Thank you for your help.