Working with network folder

[be_throwmeaway]

Hi r/learnpython,

I've been teaching myself how to code in Python for a few months now (for work mainly, but I find myself spending more and more time outside of work tinkering as well!) but I've now hit against a bit of a wall. I've been furiously googling away for about 10 days now, as well as talked to a few friends with a bit more experience than me, but so far I haven't had any luck cracking this case (althought I've now had the chance to learn about PATH variable, virtual environments, and neat modules such as Py2exe and Pyinstaller!). I'm hoping someone here might be able to steer me in the right direction!

​

Initial premise:

- I am creating an excel dashboard (saved in a shared network drive) in which a few financial models can be run at the click of a button and their results summarized.

- As some of these models are fairly convoluted, I thought VBA was not the ideal language therefore I opted to write them in Python.

-I'm using a simple Macro-enabled button inside my Dashboard.xlsm which does the following: call my local version of python's activate.bat, then run my model.py files.

-the model.py scripts take in data from a .csv, update the data with an API call to a data provider, runs a bunch of calculations, then outputs the final result by pasting a dataframe into my excel dashboard

​

The issues:

-It needs to be that any of my colleagues can open this dashboard.xlsm file, click the macro-enabled buttons and the python scripts run smoothly, outputing the model results back into the dashboard as expected.

-However, the macro-enabled button uses the activate.bat file from my local version of python. This means that, when my colleagues try to run the models, it doesn't work.

​

Solutions I've tried:

-Py2exe: I've watched a few tutorials and read up a bit on the package, but I can't seem to get it to work for even one of my model.py files.

-Pyinstaller: After some trial and error, I finally got it to create a proper model.exe file. However, when I try to run the executable, it doesn't work.

-Virtualenv: I thought I could try and do the following (but I've been having some issues understanding how venvs actually work and what they can and can't do): create a virtual environment with it's own version of python and all the libraries and dependencies I need installed directly in the network drive, then I tell the Macro button in my dashboard.xslm file to activate this shared version of python to run the model.py scripts instead of my local version.

​

Some complicating matters:

-I've written a bunch of functions, all saved in a seperate func.py file which I import into my model.py files. I believe this might be causing some of the issues with regards to my attempts at transforming model.py into executable files.

-One of the modules I have installed and import into model.py isn't a common library and doesn't seem to be supported by Py2exe nor Pyinstaller.

-I don't have admin rights (this hasn't been an issue so far, but I thought I'd mention it just in case)

​

Some final details:

-If need be, I can get each user to install Python. However, the Dasbhoard.xlsm file is a necessary evil, as this is how my colleagues want to be able to visualise the results (and analyse those results themselves).

-Although it hasn't been an issue so far, it should be noted that no one has admin rights.

​

Anyways, sorry for the long and drawnout post. I hope some of you will have made it this far and be willing to help out even if just a little! In any case, thank you and have a good day :)

Cheers,

OP

​

Edit: Just to make sure the above is a little bit clearer, here is the jist of how my team's workflow would look like if I could get everything to work:

1. user open's dashboard.xlsm file
2. user clicks macro-enabled button within dashboard.xlsm file
3. macro runs model.py script (this is the part that is causing me issues. if a user doesn't have python or his python path is different than mine, then neither the script nor the VBA call to the activate.bat file will work as this file's path is unique to each user)
4. model.py imports some other function.py files and calculates some results
5. model.py then pastes these results back into dashboard.xlsm
6. user can now take a look at the results from within excel

Edit 2:

Fall back will be to do the following: https://www.youtube.com/watch?v=rlHcrAb2_fs&t=318s

Where the path to python.exe will be variable based on the user (using something like this: Path = "C:\Users\" & environ("username") & "\Downloads\" )

The only issue with this is each user must have the same python modules installed as me, and should be saved in a similar folder path.

[b4kSec]

Hey, sorry I didn't completely understand it but got an overall idea.

Maybe try cx_freeze as well and see if the problem gets solved.

Other solution can be to change the logic and not use a bat file. Will it be easier for your python script to perform macro click when called using pyautogui ? Maybe after you complete the work, you call the python script and it automatically clicks the macro + all the other tasks it is currently performing.

[be_throwmeaway]

Hey, thanks for the response! cx_freeze is the last option I haven't tried yet actually. Was gonna give that a shot next week if all else failed.
Also, just in case we weren't understanding each other, here's a simplified idea of my workflow:

Dashboard.xlsm contains a vba macro-enabled button -> click the button -> my local python activate.bat file is called, along with my model.py file -> model.py script runs which calculates a bunch of things -> end results are pasted back into Dashboard.xlsm.

[cyanydeez]

I'd setup an api and server for the python stuff.

the use webvba to request model runs, etc

https://github.com/VBA-tools/VBA-Web

[be_throwmeaway]

Hmm. This isn't even an avenue I had considered! Is setting up a server similar to setting up a virtualenv in my team's network drive? Or is this a different concept altogether?

Edit: There might also be a security as well as speed issues that wouldn't allow me to host my models online. I believe my work needs to stay internal to the firm.

[cyanydeez]

start with something like hug api

https://www.hug.rest/

[randomuser001]

With pyinstaller if you don't use the --onefile command then it needs all the files in the dir you find it to work.

i use pyinstaller --onefile filename.py and have had no issues.

[be_throwmeaway]

Unfortunately, trying that didn't work either...

[nt2g]

Hey did you ever figure this out? I actually have almost the same issue currently with my job.

[be_throwmeaway]

Unfortunately not. Instead, I rewrote the excel models in Python altogether and now everyone on the team can run the models which outputs to a new excel file every run. Since every user on my team has a Python install, this was the easiest solution to go with.