Changing working directory from Excel vba shell

Question

For work, I'm trying to run a Python script from an Excel VBA macro.

The macro is as follows -

Sub Plot()

Shell "C:\Users\maheshda\AppData\Local\Continuum\Anaconda3\python.exe C:\Users\maheshda\Tool\Tool\Main.py", vbNormalFocus

End Sub

The script runs just fine when run from the command line, but unfortunately, because I'm using relative filepaths in the Python script (at one point, I call files from '../Input/') the script crashes because the current working directory isn't C:\Users\maheshda\Tool\Tool.

How can I change the working directory from within the macro? Thank you!

Answer 1

Extending on Wiktor Stribiżew's answer and comments, the sub below can be used to change the Current Directory in any case.

Public Sub Change_Current_Directory(NewDirectoryPath as string)
    Dim CurrentDirectoryPath as string
    CurrentDirectoryPath = curdir
    if Strings.StrComp(Strings.Left(CurrentDirectoryPath,2), Strings.Left(NewDirectoryPath,2), vbTextCompare) then
        ChDrive Strings.Left(NewDirectoryPath,1)
    end if
    ChDir NewDirectoryPath
End Function

Happy coding!

Answer 2

FCastro, why did you bother with that StrComp line? And, for that matter, why bother with the Strings object?

I suppose if the drive were external and hadn't been accessed yet it might take a moment, but as long as the path is not expected to be a USB/CD/DVD/etc..., then:

Public Sub Change_Current_Directory(NewDirectoryPath as string)
    ChDrive Left(NewDirectoryPath,1)
    ChDir NewDirectoryPath
End Function

Answer 3

This is a trivial task in VBA, use ChDir:

ChDir Statement

Changes the current directory or folder.

Syntax

ChDir path

The required path argument is a string expression that identifies which directory or folder becomes the new default directory or folder. The path may include the drive. If no drive is specified, ChDir changes the default directory or folder on the current drive.

Since your main.py resides in C:\Users\maheshda\Tool\Tool\, use the following right before calling the Python script:

ChDir "C:\Users\maheshda\Tool\Tool"

Or (since it is on drive C):

ChDir "\Users\maheshda\Tool\Tool"

Answer 4

If your behavior is to open an excel window and then open your recent file, please note that you should not forget to add change Drive and then change Directory into your VBA code.

Cause the Excel always start with the default Directory even it's just open your recent file !

Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts As Variant

ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
                        Application.PathSeparator)

ChDrive ThisWorkbookPathParts(LBound(ThisWorkbookPathParts))
ChDir ThisWorkbookPath