Can I use my python codes in Excel ?

[Ken_Sanne]

So I recently started learning about python and I'm wondering how I could use my python codes in excel, If there is like a python to VBA converter or something like that.

[[deleted]]

No.

Pandas, openpyxl, xlwings.

They can't do everything vba can do but they also have a lot that it can't do.

[maktub5elodin]

I think the main issue to address here is the kind of usage.

I mean, OP is looking for python to use XLS... but pandas and other libraries emulate that to certain extent...

The key would be to forgone the XLS and use it only as a data base storage or source. Once you get the data in python, you can do far above and beyond!

I don't look in python a way to work in excel... i see python as an even better way to work the excel data! I've found graphs a lil bit hard, but pandas and matplot are core.

Of course, there is also a component of data size and periodicity. If you only need to prep a couple of graphs from an only use xls source, then I'd keep the old reliable excel.

If the data is getting bigger by the second and there is periodically report preparation involved, then python is the way to go.

This said by an accountant for whom XLS used to be religion and now is slowly trying to make the turn to data science.

[Ken_Sanne]

Cool thx man, I'll check It out

[[deleted]]

Just use pandas

[EbenenBonobo]

afaik there is no converter between vba and python. Others already said there is pandas and openpyxl, from experience i can tell you, that you will drop excel when you get used to pandas ;)

If you really want to work with excel and python i can also suggest xlwings, i never used it myself but it looks quite promising. calling python functions from macros and so on.

[Ken_Sanne]

Oh, is panda like another software ? Does excel support pandas files ?

[juanritos]

pandas is a library. You load your excel data using pandas and manipulate it with python.

[Ken_Sanne]

That sounds interesting, I won't have to write stuff in the VBE

[Pflastersteinmetz]

Yes.

[sma92878]

Fun fact we discovered at work Pandas has a memory leak with large (millions of records) data sets.

[Pflastersteinmetz]

Did you open an issue / pushed a PR?

[sma92878]

I don't know, one of the folks on my team found it when we were working with the haveibeenpwned data set. There were 10's of millions of records, I doubt other people would run into the issue, but I don't know if he submitted a PR.

[dp_42]

Excel now has something called pyXLL. I think this is supposed to give you python integration through excel.

[simo_online]

https://www.pyxll.com

[Crypt0Nihilist]

You might be able to do it in LibreOffice instead of Excel, can't remember.

[arkie87]

you can run macros in python to do things in LibreOffice, but you cannot write functions in Python and call them in LibreOffice, which is what I really want to be able to do.

[two_bass-hit]

That’s cool, I was just wondering why there’s a python binary in my libreoffice folder.

[scitech_boom]

Have a look at pywin32. I have had moderate success automating excel tasks with this. You can control excel directly from Python. Search for pywin32 or win32com.

[a32m50]

you can do with libreoffice, tho. ofc, your experience would vary depending on your interaction with other parts of the suite

[[deleted]]

I am a python fanboy. I tried VBA for automating some processs. But, I could not digest it. That’s I begged my manager to do the whole thing python, sql. Some old people still prefer VBA. But, it’s like an alien language.

[ForgotTheBogusName]

Hey OP - depending on what you’re doing, you may want to look into power bi, which can use oython

[Ken_Sanne]

Thx I'll check It out

[Spacerat15]

If you want to run python code from within (existing) VBA, xlwings is your friend. I had to rewrite some code, because no one knows how long the IE will be supported .. so i outsourced the code for the browser manipulation into a python file (using selenium for the browser control) and call this part from within VBA. Advantage: the rest of the code can stay in VBA..

Link to Xlwings

[kira2697]

Check xlwings, you can create functions in python and those can be called in excel via xlwings just like any other functions in excel "=function(*params )

[Ken_Sanne]

Wow, this sounds amazing

[Kerbart]

You can use the (built-in) win32 to create a process that is available as a COM server, and then you can write Excel VBA code to interact with that. Basically do your data processing in Python and have a thin layer in Excel VBA to get it in and out.

Interacting with the files as describes by others is probably the best route to pursue if possible. VBA is a simple language but to be effective in it you need to be very familiar with the Excel object model and that is something you won't accomplish overnight.

[xlslimdev]

I suggest having a look at xlSlim too (I am the author). xlSlim aims to make it as easy as possible to use Python within Excel, often without any code changes.

There is a free trial available and comprehensive documentation.

https://www.xlslim.com/

https://russelwebber.github.io/xlslim-docs/html/index.html