import python module when launching from VBA

Question

It is the first time I write as I really didn't find any solution to my issue. I want to allow my user to launch some Python program from Excel. So i have this VBA code at some point:

lg_ErrorCode = wsh.Run(str_PythonPath & " " & str_PythonArg, 1, bl_StopVBwhilePython)

    If lg_ErrorCode <> 0 Then
        MsgBox "Couldn't run python script! " _
                + vbCrLf + CStr(lg_ErrorCode)
       Run_Python = False
    End If

str_PythonPath = "C:\Python34\python.exe C:\Users\XXXX\Documents\4_Python\Scan_FTP\test.py"
str_PythonArg = "arg1 arg2"

After multiple testing, the row in error in Python is when I try to import another module (I precise that this VBA code is working without the below row in Python):

import fct_Ftp as ftp

The architecture of the module is as follow:

4_Python
-folder: Scan_FTP
  - file: test.py (The one launch from VBA)
-file: fct_Ftp.py

(For information, I change the architecture of the file, and try to copy the file at some other position just to test without success)

The import has no problem when I launch Test.py directly with:

import sys, os
sys.path.append('../')

But from VBA, this import is not working. So I figured out this more generic solution, that dont work as well from Excel/VBA

import sys, os

def m_importingFunction():
    str_absPath = os.path.abspath('')
    str_absPathDad = os.path.dirname(str_absPath)
    l_absPathSons = [os.path.abspath(x[0]) for x in os.walk('.')] 
    l_Dir = l_absPathSons + [str_absPathDad]
    l_DirPy = [Dir for Dir in l_Dir if 'Python' in Dir]
    for Dir in l_DirPy:
        sys.path.append(Dir)
        print(Dir)

m_importingFunction()



try:
    import fct_Ftp as ftp
#    ftp = __import__ ("fct_Ftp")
    write += 'YAAAA'    # write a file YAAAA from Python
except:
    write += 'NOOOOOO'  # write a file NOOOOO from VBA

f= open(write + ".txt","w+")
f.close()

Can you please help me as it is a very tricky questions ?

Many thanks to you guys.

This is probably due to the fact that your Python script should run on a working directory that is not the one by default of the VBA shell. You should first set the working directory of your VBA shell to the same directory you would use to run your Python script by command line. Check [this question](https://stackoverflow.com/questions/32276705/changing-working-directory-from-excel-vba-shell) to see how to do it. — Matteo NNZ, Jan 21 '19 at 06:47
Hello, It changes the DIrectory indeed but doesn't make the import work anyway. — Laurent T, Jan 21 '19 at 10:29
In the python code, if you check, I already add all the possible folder : sys.path.append(Dir) I tried the code you advise me : 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 Sub I see it changes the Directory where the file is created But it still cannot do the import Thanks anyway — Laurent T, Jan 21 '19 at 10:30
Did you know you can call python directly from VBA by making the class COM callable. That way you do not need to shell. Much more control. Hope this helps, https://exceldevelopmentplatform.blogspot.com/2018/11/python-revisited-grouping-data-from.html — S Meaden, Jan 22 '19 at 20:34

score 0 · Answer 1 · answered Jan 22 '19 at 09:23

0

You are able to start your program from the command line?

Why not create a batch file with excel which you then start in a shell?

answered Jan 22 '19 at 09:23

simple-solution

1,109
1
6
13

Thanks for your answer. But if I want to launch it from Excel, it is for my users which can be very reluctant to use something complicated that they dont understand. Excel is known to all, so I want to launch it from Excel. Also, I could put all the code in one module but I am sure there is a solution on my specific issue. – Laurent T Jan 23 '19 at 02:24
If you are able to start the program from the command line then you can create a batch file with excel and start this batch file with excel. Nothing complicated for your users. Do I misunderstand something? – simple-solution Jan 24 '19 at 13:22
I dont understand. Excel launches a Python file. Excel -> Python Do you advise me to do: Excel -> Batch -> Python ?? If yes, can you be a bit more specific ? I remind you that I launch a Python file with arguments, how do you do when you passed by Batch file ?? – Laurent T Jan 25 '19 at 04:25
1) You write a batch file with Excel. The arguments are part of the newly written batch! 2) You start this newly created batch! – simple-solution Jan 25 '19 at 11:01

import python module when launching from VBA

1 Answers1