Search code examples
pythonexcelvbabatch-filepython-venv

Is this possible: Cloning virtual environments to each users laptops while source code and related files remain in a network drive

I've written a model.py script which takes in a csv file, runs a bunch of calculations, then outputs the data into my Dashboard.xlsm file. All of these files are in the same folder in a shared network drive. This .py script needs to be able to be run by clicking a button within the xlsm file. To set this up, I've written a simple VBA macro (linked to the aforementioned button) which runs a .bat file. This .bat file contains shell commands to create a virtual environment in the same folder as my files, and then run my .py script within this venv.

However, activating this venv takes a long time (~ 1 min). If instead I setup a venv in a local folder, I believe it would speed up the runtime considerably.

Top level question: Is my current setup the best way for each user to be able to run my model.py file from within excel, if not, what alternatives are possible?

Mid level question: Is cloning a venv, one on each users laptop, the way to go to speed up my code's runtime, if not, what else could I try to do?

Code specific question: What is the best way to create cloned venv, and ensure each user will run my model.py file from within their own local venv?

My answer to the top level question:

I've spent the better part of the past 2 weeks looking into alternatives. I've tried transforming my .py file into an .exe, but neither myself, nor a colleague of mine were able to get the executable file to run properly (we tried using Pyinstaller and Py2exe).

I then looked into virtual environments, and, through lots of trial and error, finally got a working prototype (but just working for me for now).

My answer to the mid level question:

I honestly don't know and am unsure what to even google to find the answer to this.

My answer to the specific question:

I thought I could do the following to (hopefully) speed up the process:

  • Instead of saving the venv in the network folder, I would save/create n versions of the venv, one on each user's laptop (in a folder they wouldn't modify).
  • I create n .bat files, one for each user's specific path information. This is the file which, when run, creates the venv and runs the python script.
  • In my simple VBA macro, I check which user is using the xlsm file, and activate the correct .bat file accordingly.

Here is what I've coded so far:

The VBA script which calls the bat file

Sub run_model()

    Dim folderPath As String
    Dim shellCommand As String

    ActiveSheet.EnableCalculation = False 
    ActiveSheet.EnableCalculation = True

    ActiveWorkbook.save

    folderPath = Application.ActiveWorkbook.Path
    shellCommand = folderPath & "\" & "USER_NAME_GOES_HERE.bat"
    Call Shell(shellCommand, vbNormalFocus)

End Sub

the bat file which sets up the venv and runs the py script

chdir I:\NETWORK_PATH
I:

python -m venv venv/
"./venv/Scripts/activate.bat" & pip install -r ./src/requirements.txt & "./venv/Scripts/activate.bat" & python "./src/MODEL.py"
'''

the python script

import USER_DEFINED_MODULE #this .py file contains functions I use later on in my script
import other_stuff

do stuff then output to dashboard.xlsm

Some final details:

  1. no one has admin rights,
  2. everyone has python installed via anaconda,
  3. I tried installing python independently, but I was unable to make it functional (thus the fallback to the Anaconda distribution),
  4. Yes, I have to use Excel as both the input/output files.
Solution

  • Top level comments:

    I'd say using C or C++ with gcc to compile a DLL which the excel vba could interface would be much cleaner.

    If you need to/want to use python then you have the problem of writing to an xlsm file while it is open. So the python script has to be kicked off from a different workbook from the one holding the results. But then a shortcut to the script would be just as good.

    Best option IMO is to have your model.py always running, monitoring the dir and automatically updating the xlsm when new files are added, or files modified. This probably requires a new book made whenever the csv files are added. You could add the date/time to the file names. This also means no one needs their local machine setup to run your script.

    Mid level comments:

    Activating your venv shouldn't be slow. It'll just be show the first time to setup the venv. So I'd take the hit unless leave the venv to be setup per user as this is the most robust method.

    If most people will only use your script once or be put off after one use due to the slow initialisation routine, then you need something better. The alternative is to get an installer to be run on everyone's computer on next login if your IT department agrees. Or email people with a link which will kick off a background process to set things up for the first time.

    Low level comments:

    A key would be to not run pip install everytime you run the script. That is slow. Either your venv is setup or not. Just check if the venv dir exists prior to running pip install.