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!

So would I add the line Shell ChDir("C:\Users\maheshda\Tool\Tool") to the macro? — , Aug 28 '15 at 17:37
Yes, or modify your Python script to determine where it's being run from, and construct your paths according to that. http://stackoverflow.com/questions/4934806/how-can-i-find-scripts-directory-with-python — Tim Williams, Aug 28 '15 at 17:39
Okay, added the line, with vbNormalFocus added to the end. Now the macro does nothing! — , Aug 28 '15 at 17:49
Sorry - don't use Shell - just `ChDir "C:\Users\maheshda\Tool\Tool"` — Tim Williams, Aug 28 '15 at 17:51
It worked!!! :D Thank you! Would you care to write this up as an answer so I can accept it? — , Aug 28 '15 at 17:55
Shouldn't this question be 'How do I get the relative paths out of my script so it can be run from anywhere?' Why not use VBA's (or the equivalent python command) `environ("USERPROFILE") & "\Tool\Tool"` to preface anything relative? Other options might be `environ("LOCALAPPDATA")` or `environ("APPDATA")`. — , Aug 28 '15 at 19:05
Oh, God. I'm a Pythonista. Everything you just said went over my head. What does environ do? — , Aug 28 '15 at 20:53

score 10 · Accepted Answer · edited Jun 20 '20 at 09:12

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"

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 28 '15 at 21:34

Wiktor Stribiżew

607,720
39
448
563

2

And a note: you can check which directory is set as current with [`CurDir`](https://msdn.microsoft.com/en-us/library/office/gg251649.aspx). – Wiktor Stribiżew Aug 28 '15 at 21:41
6

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()` – Tim Williams Aug 28 '15 at 23:15
FYI this works when using shell to call R, with Rscript.exe too. – Oliver Humphreys Jul 16 '20 at 16:19

score 2 · Answer 2 · edited May 23 '17 at 12:17

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!

score 2 · Answer 3 · answered May 26 '17 at 14:40

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

score 0 · Answer 4 · answered Nov 27 '18 at 04:36

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

answered Nov 27 '18 at 04:36

NOTSermsak

356
1
8
8

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. – Yin Cognyto Dec 02 '19 at 21:21

Changing working directory from Excel vba shell

4 Answers4

Linked