How do I make it so users don't need python to run the code?

[christopher white]

First of all, I found this last night and it is very nice. Thanks for it!!!!

From the article "http://www.codeproject.com/Articles/639887/Calling-Python-code-from-Excel-with-ExcelPython" it mentions:

"highly embeddable: it's possible to use a packaged up Python distribution so that the spreadsheet can be distributed without installing Python"

How do I do that?

[Eric Reynolds]

This is a bit tricky so I probably need to write a tutorial for this.

1. Basically the idea is to get a portable version of Python, either by packaging it up yourself somehow or downloading a ready-made portable python.

2. Then when your workbook loads up you need make sure that the folder containing python27.dll is in the environment variable PATH so that the windows DLL loader can find the python runtime.

3. Finally you just set the Path variable in the PyModule calls to point to the folders containing the Python standard libraries.

For point 2 the following code is useful:

Private Declare Function GetEnvironmentVariable Lib "kernel32" Alias "GetEnvironmentVariableA" _
    (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long

Private Declare Function SetEnv Lib "kernel32" Alias "SetEnvironmentVariableA" _
    (ByVal lpName As String, ByVal lpValue As String) As Long

Public Function GetEnv(Name As String) As String
    GetEnv = String(5000, 0)
    Dim n As Long
    n = GetEnvironmentVariable(Name, GetEnv, Len(GetEnv))
    GetEnv = Mid$(GetEnv, 1, n)
End Function

Public Function AddToPath(folder As String) As String
    Dim path As String
    path = GetEnv("PATH")
    path = Replace(path, folder + ";", "")
    path = Replace(path, ";" + folder, "")
    SetEnv "PATH", folder + ";" + path
End Function

Private Sub Workbook_Open()
    AddToPath "C:\Path\Where\Python26DLL\Lives"
End Sub

[CTC]

Hi Eric,

Thanks for the detailed explanation. I have followed points 1-3 above in an attempt to make my excel to point to a portable version of python but failed.

To be precise, I have 3 versions of python on my PC (64-bit Win 7):

(a) 64-bit python
(b) 32-bit python
(c) 64-bit WinPython

(a) is the default python (i.e. "where python27.dll" will points to "C:\Windows\System32\python27.dll)

(c) is a portable version of python. I use it instead of the popular "Portable Python" because the latter has 32-bit only (correct me if I am wrong).

I am wondering if the VBA scripts above only works for a PC on which only the portable version of python is installed? If yes, how can I modify the scripts in order to ensure that Excel will point to the 64-bit WinPython (c), instead of the default 64-bit python (a)?

Many thanks.
CTC

[Eric Reynolds]

Hi Cheung,

In theory it should work, please note though that if you want to use bit Python you need 64-bit Office -- just because Windows is 64bit doesn't necessarily mean Office is.

It is possible that the trick only works on a PC which only contains portable versions of Python. The idea behind it is to set the path in such a way that when ExcelPython looks for Python27.dll, it finds the one in the specified folder. But there it doesn't take into account things like the information that Python puts in the registry when it is installed, and I think that the Python runtime takes this information into account when setting up the library paths and so on.

So it is possible it doesn't work very well... I did say it was fiddly!

I am actually working on a new project to try and solve some of these issues. It's still very much at an alpha stage, but in the meantime you can take a look at it here. The key is the xlpython.cfg file which you can set up to point to your portable python. I will be developing this project over the next few days so be sure to check back every so often - in particular I want to develop a system for dealing with side-by-side Python installations.

[CTC]

Hi Eric,

Thanks very much for your quick reply. My Excel is 64-bit and ExcelPython works great when using the default (not portable) python. I will definitely keep checking the xlpython project. Yes, it will be very useful if things work on side-by-side python installations.

CTC

[christopher white]

Yeah, a full tutorial would be nice.

[waldemir]

I'm wondering whether this really works via the PATH variable, since Python installs the DLLs into the system32 directory, which is looked at first when loading a DLL.

[Eric Reynolds]

Are you sure Python installs the DLLs in system32?