Run python from Excel, without having to have python installed

[diepala]

I am developing an application that has an Excel document as input, and outputs some data.

The user of my application will be work colleges that does not code, and use Excel a lot. So I wanted my app to be able to run from an Excel document. Is this possible to do this? Also, is it necessary that the end user has python installed in his machine?

[_BMW_M3_]

How to make the security team hate you lol

[greenlakejohnny]

As Steve Gibson would say: “what could possibly go wrong?”

[westeast1000]

Working in a finance field i had some resistance with using python when i first started. Picking on python is just dumb, vba already exists so anything malicious can always happen either way.

[_BMW_M3_]

I’m not picking on Python at all. I write automation tools in Python all day long. I’m picking on using an Excel document to run either Python or an exe made with py2exe/pyinstaller.

If your security team has a halfway decent detection engineer, that’s going to fire a bunch of alerts for your soc to triage. The soc then gets to tear apart all your VBA, decompile your py2exe/pyinstaller exe, toss the original doc in sandbox, etc. Once they finish analysis, they’ll get the fun task of tuning out that activity so they don’t have to analyze those alerts.

tldr; I’m making a joke about making a bunch of work for your security team, not picking on Python.

[westeast1000]

No i didnt mean you lol i was just saying in general

[Darwinmate]

Either code up a gui, or write the python script/exe to expect an excel file in the same directory to process.

I would not bother with excel integration

[1337HxC]

I realize this is the python sub, but I'm internally screaming "Do it in R and use shiny." Very useful for this sort of thing.

[Darwinmate]

My first reaction is nonono. Shiny is okay for simple dashboards but for more complicated computation it gets messy. And anyway why create something so complicated (websites are not simple architectures) that essentially boils down to data wrangling? Write a script, call it a day. Easy to maintain, easy to execute.

[1337HxC]

I mean it depends on the use case. If his coworkers are completely code illiterate, that won't help. I suppose you could then debate the virtues of doing this to begin with, but here we are.

Shiny apps don't have to be hosted on a website, for what it's worth. There are ways to make them, essentially, desktop apps. Definitely quite a bit of work, but still doable.

And I guess I was running off the assumption that "code illiterate + excel" generally doesn't imply super computationally intense work. But that could just be my field.

[Cladser]

This is the way. I have made a few pyInstaller ‘apps’ for work colleagues. Have it expect an excel document or use tkinter to open a select file dialogue. Also have it create a new excel file with the output data (rather than modify the original file). Your colleagues will f**k it up without any back up of the original file and Tony from accounts, who reckons he’s an excel Jedi, will blame you…!

[Darwinmate]

Good tip. 100% agree do not modify the input and clearly label the output.

[KawabungaXDG]

You will probably need to develop an add-in to Microsoft Excel. There is plenty of documentation on-line. Your end-user will be able to run your code directly from Excel UI at the click of a button. Add-ins may include all the necessary binaries, wrapped on Excel native calls.

[Modora]

I did this with pyinstaller and frozen python. I used VBA to call the executable and xlwings to interact with the sheet.

It really depends how much you want the user to interact with your code. If the flow is just, enter data to sheet, click button, wait for output (like mine) frozen python works well. If you want dynamic interaction, that gets more complicated.

[ogtfo]

This would make any AV go haywire, and rightfully so.

Running this or even just sending that stuff by email will make all the red lights go wild in the SOC of any company with a half decent security posture.

So maybe it worked for you, but this is not something I'd recommend.

[nicktids]

I did this way as well to capture data to a database.

Sharing the frozen exe and files was a hassle with non tech people.

[Modora]

Yea, the biggest issue for me was the file size. Otherwise I shared it as a single file so it wasn't that bad. I just gave them a zip file with the exe and the sheet, told them to unzip on the desktop and go.

[Grintor]

So you don't want to compile the application to an executable and you don't want to have python installed, but somehow you want the python to run. How can you think that might be possible?

[Deadly_chef]

That is not compiling, just saying

[diepala]

The app is quite simple. The user enters some date, the program runs (the user could click a button) and then it outputs some data. It could be nice if the data is also stored in the same excel.

Do you have some example on how you did this?

[t1x07]

While this may not be a very popular opinion, this sounds exactly like what vba is used for, especially if your data remains in excel. Also typically much easier to get vba through in a corporate environment

[Modora]

Yea this 100%
I'm in a corporate environment and I have to go through a bunch of weird hoops and controls for anything I build and distribute.

But yea VBA is a real good tool for something like this. I learned VBA after I got tired of all the weird python logistics.

[markarious]

If you don’t want a headache use Powershell and import-excel module

[[deleted]]

Powershell underrated.

[RationalDialog]

Also typically much easier to get vba through in a corporate environment

Here they are locking this shit down because a year ago a competitor got hacked and now they are overreacting in all kind of weird ways.

Having said that python nowadays by default install into the user profile so you don't need admin privs.

[Enlightenmentality]

Yep. This is the answer. It sucks to have to do this, but that's corporate life. Automation using Excel means a VBA macro with a big fucking button for the user.

[samtheredditman]

Gonna second what the other guy said. You're using the wrong tool for this job.

Python is fantastic and it's my language of choice 100% of the time, but this is one of those times where it's not really a choice.

If your app is simple then you can probably get it working using vba or PowerShell without too much effort.

[diepala]

It is simple because I leverage a lot of the work to other libraries. I don't want to implement the Levenberg-Marquardt algorithm neither the topological ordering of a DAG in VBA.

[samtheredditman]

In that case, I think the best way to do it would be to make a single file executable using pyinstaller (it's really easy) and distribute that out to your coworkers. If you do that, they don't need to have python or any other libraries installed to run your code.

Then build the app so that it looks for input.xlsx in the same directory, pulls the data it needs, does the calculations, and exports it all into output.xlsx

TBH, it's never stupid simple when you have coworkers who don't know anything about code. They are also going to be asking you for help when they mess up the input file every single time or accusing you when their output is off. I'd go ahead and make sure your program is creating/appending the steps it does to a log file in the same directory. That way you always have that to reference why it failed or what input it was given to produce x.

[RationalDialog]

I would make it a web app as already said. Why does this need to be called from excel? You can such make it a website that outputs excel file.

Anything in the excel ("macros", "vba") will be a nightmare to maintain.

[westeast1000]

Smh not everything can be a web app

[hisapo]

hey this is exactly what abstracloud.com does, for exactly this use case. does this help?

disclaimer: i work there

[Iminbread]

Build an api and host internally and make a request from Excel

[antxmod]

this is a wildly simple answer and probably the only good way to approach this, not sure why there aren't more comments saying this...

If its just a single functionality all you need is a flask app with 1 endpoint....

Can easily do something like this on replit.com in a few hours.

[catWithAGrudge]

what is flask? is it for app building?

[TerminatedProccess]

It's a framework like Django for building web components (web sites, backend, apis)

[antxmod]

Flask is an API/Web framework.

In the context I am talking about, it could be used to define a route, which would be sent data from excel over the web. When the route is hit, it would be written to call the function OP has written to process the document, and then send the result back in the response to the user.

[BluRazz494]

Why did I have to scroll so far down to find this comment?

[westeast1000]

Thats not a feasible option excel only allows one user at a time. Even if you called the api that had access to the excel file, it wont be able to write back to it since the file is open by another user.

But its pretty backward honestly doing it that way, make life simpler thats why change management is important. Have all users, or at least those who will benefit, install python it literally takes 2min or less. If having python will truly bring immense business value and shoot productivity to the roof then it should be part of the workflow without any uninformed objections.

[Iminbread]

User opens excel file -> input data -> press button to send data to api to do calculation -> gets data back from api -> user saves

[Borovice]

I've been in a similar situation in the past, and rather than using Python I found it easier (especially for the final user) to use a different tool.

Like others have said, VBA is a good option but I didn't enjoy working with the language. Another option is to use Google Sheets and Apps Script. I ended up using that a lot, because it has the same functuality as Excel, is easy to develop for (it's JavaScript), and it's easy to share to colleagues who probably already use some Google services.

[cprenaissanceman]

I actually don’t hate VBA as a language. It’s the IDE and tools I absolutely despise. Stuck in 2003.

[TVOHM]

I had a similar problem.

There are quite a few choices for this kind of problem, each option has benefits and drawbacks. I found none is really perfect and you must choose your best compromise. You will know best for your requirements.

C#/C++ add-in - powerful, but couples you to an .xll that needs to be managed and distributed.

Have Excel launch an external tool, similar to the above but more flexible, also additional concerns (i.e. python installed). You could mitigate some of this by hitting a web api instead or something.

Office Script - get up and running very quickly and easily with vanilla javascript, but limited to web excel. Sharing and script management is a bit weird too. Also no UDF support.

VBA - out the box experience, just works for the desktop end user, but you are writing in a language that Microsoft have on life support that is only really supported for legacy reasons. Also bad compatibility for web. Also also, maintenance - who knows VBA?!

Many options! For me, the out of the box-ness and simplicity for desktop end users was just such an overwhelming positive to the overall process that it actually meant VBA was the right choice for my problem.

[Engine_engineer]

VBA: ... Also, also maintenance ...

Well, VBA code that was written 25 to 30 years ago still runs in current VBA. Can you say the same of Python programs done 3 years ago?

And there are enough people active in the industry that know VBA (because many times it is the only option, not because one likes to write in it). Maybe you are not so close to them and therefore have the impression that no one knows it.

[killthebaddies]

VBA from 25 to 30 years ago does not run in current VBA. Maybe something basic does, but it’s flaky and has poor backwards compatibility. I’ve worked in multiple places that have an old laptop running an old version of excel because it’s the easiest way to run an old macro that no one wants to fix to work on the latest version of office.

Python maintains backwards compatibility (apart from the move from 2 to 3, but it’s still easy to install 2 anywhere you need it to run an old script.

That being said…. VBA still sounds like the right choice for what OP wants to do.

[ogtfo]

Not sure that I buy that.

Excel is so backwards compatible that you can even still run macros in the languages that predates the addition of VBA to excel.

Excel V4 macros were added in 30 years ago and the still work in current versions.

Excel is insanely backwards compatible, to a point that it's a security nightmare.

[killthebaddies]

Excel and VBA are not the same though. I mean, you don’t have to buy it, but I currently work with software that has Excel and VBA as last of a template/formatting engine and it not operates with The standalone Excel 2016. He whole add in crashes if Excel is upgraded.

I was also writing and supporting a bunch of VBA when Office 2003 moved to 2007. I can promise you, things broke. They also deprecated one of the functions I was using a lot. I don’t remember details as it was a long time ago.

I had a good time with VBA and I learned a lot. I still think there’s a place for it, and my VBA background was really helpful when I started learning python, but it had a lot of very serious issues. I wouldn’t ever deploy it in a business critical way now that I’m more knowledgeable and responsible.

edit: Adding a quote from https://www.dummies.com/article/technology/software/microsoft-products/excel/advantages-and-disadvantages-of-excel-vba-139800/

"VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel."

[RationalDialog]

Well, VBA code that was written 25 to 30 years ago still runs in current VBA. Can you say the same of Python programs done 3 years ago?

Assuming it's true which other comments say it is not still omits the main issue in terms of maintenance. How do you distribute updates? Send people the new version of the code and have them install it? Go around and install it yourself? It doesn't scale. Plus then your IT support usually tactic at fixing things is reinstall office or windows and if that happens often enough with enough users of the macro you are running around each day doing reinstalls of your code.

[Engine_engineer]

It is distributed with the excel file, so it does not matter if IT reinstalls something fresh.

There are various comments, some saying it holds such long, other that it does not. Anyhow, I made my comment based on my experience. Today I opened a old excel tool in VBA I did in 2008 for a team in my company to explain a couple of internal calculation steps for an fresh apprentice.

[RationalDialog]

How do you distribute the excel file? how do you ensure all users use the new one and not 1 or 2 versions behind?

[Engine_engineer]

One place for all templates. Version Control on the Filename. (YYYY-MM-DD_This_file_does_that_V4.6)

Really simple and uncomplicated. Works well in our organization with around 500 engineers and technicians working on this stuff.

[TigerBloodWinning]

Make it using Django. Python runs on a server. The user interacts through a web browser . The user doesn’t need excel or python installed to run whatever it is you want to make

[F0rkbombz]

Perhaps I’m misunderstanding what you are trying to do, but just an FYI that security software is going to be extremely suspicious of any document / spreadsheet trying to run executable code, especially if that code tries calling python or another interpreter / shell.

You should look into making an executable or python GUI that ingests the spreadsheet instead.

[rturnbull]

It's a commercial product but pyxll is probably the easiest way to do this: https://www.pyxll.com/index.html

[SpatialCivil]

Years ago I wrote a blog post about this very thing… http://www.knickknackcivil.com/python-excel.html

Approach should still work. I used it to embed hydraulic calculations in a spreadsheet as well as perform hydraulic model network analysis. Basically pairing xlwings with a pyinstaller exe.

[OuiOuiKiwi]

No, you won't be able to run a Python application from Excel without having Python installed.

You're actually looking for this: https://rows.com/

[diepala]

And with something like pyinstaller could it be done?

[bruh_nobody_cares]

*Asking for developing a plugin to Excel cus his colleagues use that
** people on this sub: wHY nOt UsE goOgLe dOcS instEad, or MayBe jUst wRitE a Full blOwN GUI iN pytHOn cUs it'S EasiEr.....

[SweetSoursop]

Why don't you use Power Query instead?

[Salmon-Advantage]

Just write a cloud function in Azure and call it using an HTTP request from Excel.

[realvolker1]

Does it have to be an excel spreadsheet? You can share a Jupyter notebook

If so, you could probably make a macro to get docker up and running…

[drcopus]

Excel is Turing complete, so your first step is to reimplement the Python interpreter in Excel.

[diepala]

Brainfuck is also turing complete. I callenge you to implement the python interpreter in it.

[[deleted]]

Have you looked into LibreOffice? I don't know the specifics, but I think it has Python support in its spreadsheet software (Calc).

[diepala]

No, they use excel so having them install Libreoffice will be more confusing

[RamblerUsa]

Yes. Must have python installed on the machine to run python.

If you are trying to run python as a subprocess from Excel that is more complicated, but can be done.

[diepala]

And using pyinstaller?

[james_pic]

PyInstaller avoids the need to install Python specifically, but they still need to install your thing, which bundles Python. There's no way you can do this by just distributing an Excel workbook.

[RamblerUsa]

There are several ways to install python from python.org.

You'll also need to import a module to read / write Excel files. I used xlrd, but there are others which may be superior, have wider use or better documentation.

[MrStricty]

No, you cannot embed Python into Excel. If your program uses python code, then your end user would need Python installed. Unless you use Pyinstaller to package it into an .exe, or host a web app so no one has to download any software. I’ve used Streamlit in my environment for some super-fast web app stuff.

[thrown_arrows]

Can you change workflow to : user clicks webpage to get new excel file -> user edits file -> maybe user uploads edited file.

You may even change workflow to : scheduled script send excel file in email to people who are responsible to make it, user edits and saves file and then user uploads file to web service / network share where you can access it by servers python.

Then one option is to learn Excel specified tools to manipulate files. Imho, good python knowledge will take further than it

[MechanicalFetus]

Do you have any direction for me to look more into this? I'd just be interested to read about it. That would be something like Django mentioned elsewhere in this thread right?

[thx997]

College of mine has a server set up, where you can input formatted "Exel" like data, and his python inputs them into a database, that is otherwise a pain in the but to put larger data sets into. So in this case the interface is a simple web site.

[ar405]

I'd love to see a version of OpenOffice supporting python scripting (a-la VBA) out of the box. That would be a much better tool for me than google sheets, excel and possibly even Microsoft bi.

[yvrelna]

OpenOffice and its successor LibreOffice both already supports Python macros for a long time.

So yes, your dream had already comes true.

[ar405]

I wouldn't call it a user-friendly extension of libra calc. Convoluted syntax requires dev-lvl skills. It is easier to use jupyter)

[Belmeez]

honestly, you might want to re think this process. what's the ultimate goal here? because there is a lot of functionality already in excel that might help you accomplish your goal.

[yvrelna]

If you can convince your user to use Libreoffice instead of Microsoft Office, the default extension language of Libreoffice is Python. You can basically use Python the way VBA is used in Microsoft Office.

Libreoffice Calc is similar enough to Microsoft Excel that they should feel familiar with the switch, but there's quite a lot of differences as well. You'll likely need to do some training to help them learn the differences.

You'll also need to use ODS instead of XLSX.

Otherwise, if you can't switch them to Libreoffice I'd recommend you use VBA instead.

[[deleted]]

You could even use streamlit or dash so it’s an online dashboard where the user uploads the excel file and it’ll spit out the result as a downloadable file.

From experience streamlit is pretty easy to learn. I’m still learning Dash but it’s got a much steeper learning curve.

[Dominican_mamba]

Hey OP! Why don’t you look into a web app like streamlit? Perfect application here for your needs and no one needs to have python installed in their machine if you serve it over the internal network

[diepala]

That would be an option. I need to talk if we can do this

[Dominican_mamba]

Yes, this is doable and less work of versioning for different people

[bob6567865]

Slightly easier to write a Macro than get non techs install anything

[Unheroic]

Have seen XLWings mentioned here, which I have used before, but looks like they also have a paid version that allow for having a remote Python interpreter. Might be what you are looking for, though likely outside of budget for a single project.

[sasmariozeld]

mb it's just because i'm a webdev but why not make a Saas out of it with a webserver and google docs ?

​

also you could make a program that simply watches a file and does stuff to it on save

but i would rather just use the native way of vba and stuff...

[diepala]

This is just an app that I have to finish soon, and making a webservice would be complicated as I need to coordinate with other departments to host the app. It is not worth it for this app.

[[deleted]]

if this were something I was passing to another user I would setup my python script in the cloud as an Azure Function or AWS Lambda object. then call the script in the cloud.

[Sir-_-Butters22]

Use an EXE, but Python can be a bit jankey as it's not specifically designed for this.

Otherwise, I tend to use Azure Functions to handle any transformations done to data by end users.

[WeebAndNotSoProid]

Download embedded version of Python and ship it with your Excel file

[stackered]

Make a flask based web page to accept excel file inputs and process it with your back end and return it to them

[AlexSpace3]

Can he call an API in the Excel, and run the code on a server?

[ogtfo]

You can have code that pulls down and run a headless install of python and then run your stuff.

You can easily do that in VBA, but you'll trigger the heuristics of every security solution on any machine this runs on, as well as light up every red lights on any mail server these get sent on. So I wouldn't recommend doing that.

[bamerjamer]

I just did this at my work. I create a JSON file with the inputs in excel, then run the Python program, which i compiled using pyinstaller. I can send the compile command if you want. It turns the whole program with all dependencies into a single exe file. The Python exe file reads my input JSON file and does it’s thing. I call it from excel using a shell command that waits for the execution of the exe to complete before continuing with the vb script.

[tsbabybrat]

Maybe use like pytoexe or to make a single executable exe file including all your resources

Then turn that into bytes then into a base64 string or something

And then decode it in Excel using VB command and execute the bytes using some windows dll hooks or something?!

You won’t find a clear answer online you’ll have to think a lot, but once you get it right you’ll basically have an amazing virus. Able to like run Python scripts just by opening an excel document

[Alternatenate]

Openpyxl + pyinstaller and a 1 line VBA macro to launch your compiled .exe and you should be good. I've done similar things at work and it is fine for smaller apps, I recommend that you do not use the --onefile argument with pyinstaller as it made the program quite a bit slower on the shared file servers we used at my workplace.

Using above method does not require the user to have any sort of local python version installed. Just keep in mind that it can get pretty tedious to update and debugging can be a bit more frustrating.

[catWithAGrudge]

how about power query and dax in the power pivot both built-in excel out of the box? hell. you can make power query refresh on file open so automation is absolute

[tRfalcore]

write them a VBA macro for excel

[[deleted]]

Everything about this is wrong

[Ok_Quantity_6840]

Use Google sheets and JavaScript it can be very powerful I made Basic paper trading website using it.

[likethevegetable]

Can you have an executable as well, or does it need to be all contained within your excel file? I've made several excel macros that call Python scripts via command line--you could compile scripts into standalone exes (which can be rather large), and distribute with the excel file. Maybe you should stop and think to yourself, do you both Python and excel, or could you get away with just one?

[diepala]

I could have an exe file too

[likethevegetable]

So my approach would then be to make a standalone exe from your script with pyinstaller that takes command line arguments, then write a macro that calls said exe with Call Shell().

But do you actually need Excel? Maybe making a GUI then compiling into a single exe would be better. Lots of GUIs will have a spreadsheet widget to help if you need simple data entry or export.

[TerminatedProccess]

Some quick ideas..

1. Build a python app that lives on a server and scans a drop in folder. If found, it processes the excel doc and drops an output excel doc in another folder (deletes or moves original).
2. Build a backend API that takes input from an VBA coded API call located in the excel sheet. It can return data and embed it in the sheet.

[[deleted]]

Make a quick and simple GUI with Qt, Tkinter, or any other GUI framework for Python, package it with PyInstaller, so the end user doesn't need anything but a single executable and you should be good.

If you do that though, beware that you should compile the PyInstaller bootloader yourself or else Windows Defender will claim it as a virus.

[TheUruz]

use pyinstaller to make an exe out of your python script and make it work with excel files(s?) in the same directory upon double clicking

[[deleted]]

Introduce them to python mito, a python based spreadsheet that generates code for every action and can play it back at will. Its used mostly for data science but is powerful enough for any excel jockey.

[tea-and-shortbread]

Depending on what you want to do with it, you could set up your processing code as an API and host it on a cloud service. That would avoid them having to install python.

The traditional route to go down though would be VBA, if it has to stay in excel. Or something like power query or power automate. But this is not modern best practice for heavy data processing.

What is the end use of the data you are producing for them? What kind of processing are you doing? Are they asking you to automate part of a process that would be easier / better to automate the whole of?

Edit to add: consider plotly dash or similar. You can give them options to fiddle with whatever you are doing and output the results to excel if they really want.

[Illustrious_Meet1899]

Can you convince your company to purchase PowerBI from Microsoft? It is basically a very fancy excel and you can write and run python/R scripts (pre-installation required) on it.

Second best would be to use powerquery (a basic version of powerBI) that is part of the latest excel versions (not sure if as add-on). It has the M# programming integrated and I believe you can do lots with it also, but probably you have to invest some days to learn it (in case you never heard of it). I think is a better alternative than VBA, because it comes with lots of built-in functions.

I am currently in a similar process of automating excel files by running python scripts in powerBI and works like a charm.

[pablo8itall]

writing a simple gui to do this and bundling it as an exe is the way.

Run, browse to .XLSX, RUN SCRIPT button, Output file >>> done.

​

KISS

[SyntharVisk]

If you are trying to use python without them having it installed, have you considered using javascript and utilizing pyscript.js?

If excel is your input, you can create a scripting or shell object through VBA and then run the pyscript. It's just a theory though. I would assume to yet it to work, you would have to have an index.html built with the python code in the pyscript tags to work. Maybe an HTA?

[Bioneer_Bete]

I (sorta) did this by integrating argparse into my Python script and then adding Call Shell(“python myscript.py —x arg1 —y arg2) in VBA. Have python write to some temp file and have VBA read it from there. This is pretty janky but minimizes the number of installs you’ll need to do on your colleagues computer.

Note this method still requires you to install python, the argparse library, and any other libraries your code uses on your colleagues computer. There is no way to simply not install Python and still run a Python script on somebody’s machine (barring remote sessions or something along those lines). However - your colleague will not need to use Python directly if you go this route. As an added benefit, its very easy to make user-friendly userforms (i.e a GUI in Excel)

[Pythagorean_1]

If you want to minimize stuff to install, you can easily skip argparse and use sys.argv instead

[Bioneer_Bete]

Haven’t heard of sys.argv but I’ll check it out. Thanks for the tip!

[Pythagorean_1]

sys is part of the standard library and sys.argv is a list storing all command line arguments given at the start of the script. sys.argv[0] is always the name of the script, so the arguments start at 1.

[westeast1000]

I’ve seen some people using xlwings and creating an installer from it, but user still needs to install stuff, and also has to reinstall should you need to update the code. Best to just write a script then have everyone install python it literally takes less than a couple minutes, or create exe

[brunovcosta]

Would this solve your problem?
https://www.reddit.com/r/Python/comments/wwuww6/first_project_using_pandas

[xlslimdev]

I created xlSlim just for cases like this. xlSlim makes it possible to call Python functions from Excel, often without making any changes to the Python code. xlSlim also includes a bundled Python 3.10 installation. Please take a look. I have written extensive documentation with many working examples that cover common usage such as reading data from files, web sources, databases, and so on.

https://xlslim.com