5

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!

Improve this question
8
  • ChDir() may do what you want. Commented Aug 28, 2015 at 17:36
  • So would I add the line Shell ChDir("C:\Users\maheshda\Tool\Tool") to the macro? Commented Aug 28, 2015 at 17:37
  • Yes, or modify your Python script to determine where it's being run from, and construct your paths according to that. stackoverflow.com/questions/4934806/… Commented Aug 28, 2015 at 17:39
  • Okay, added the line, with vbNormalFocus added to the end. Now the macro does nothing! Commented Aug 28, 2015 at 17:49
  • Sorry - don't use Shell - just ChDir "C:\Users\maheshda\Tool\Tool" Commented Aug 28, 2015 at 17:51

4 Answers 4

Reset to default
10

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

And a note: you can check which directory is set as current with CurDir.
Note - if your current directory is on a different drive from the one you want to switch to, you will need to use ChDrive() before using ChDir()
FYI this works when using shell to call R, with Rscript.exe too.
2

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!

Improve this answer

Comments

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
Improve this answer

Comments

0

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
Improve this answer

1 Comment

Good solution. However, one can safely do ChDrive SomePath followed by ChDir SomePath, since, according to Microsoft, "if the drive argument is a multiple-character string, ChDrive uses only the first letter", and "on the Macintosh, ChDrive changes the current folder to the root folder of the specified drive". This means that ChDir automatically takes the drive part from any valid path without the need to split it manually in the code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.