Call Python functions from VBA in all Microsoft Office apps | Hacker News Reader

Call Python functions from VBA in all Microsoft Office apps

docs.xlwings.org

75 pointsfzumstein25 comments

A couple of years ago I played with xlwings, but found it too slow for our usecase (a few cells modified per second) plus we found the Windows requirement quite onerous.

I wound up reading/writing our spreadsheets in pure Python -- we used xlwt which doesn't support xlsx, so I'd probably consider using openpyxl these days.

(Specifically we were selecting parts of spreadsheets based on row/column headers inside the spreadsheet even if they weren't in the first row/column or there were multiple tables per tab; if this sounds useful to you check out https://github.com/sensiblecodeio/xypath )

Why not just use Pandas? Whenever I want to do any slightly complex modification to an excel table I just load it into Pandas instead. It's a complete breeze and very fast.

Pandas uses the openpyxl module, which can both read and write. It’s quite nice and pure python, too. Pandas is more high level and gives less control but also less code.

Props to openpyxl, just used that module to do some quick and dirty spreadsheet manipulation to save some colleagues a bunch of time.

I don't think it came up in our review of Excel/Python technologies -- perhaps because that's not the primary focus of the library.

Does anyone have any current info on native Python support in Excel? I recall Microsoft announcing they would offer this at some point in the future. I would really really prefer it to VBA.

I believe they canned it and went for javascript instead.

Which if they also enable webassembly in the future is cool, because it means you will be able to run pretty much any language.

Do you remember how you came by this knowledge?

I don't but it was around the announcement of Office 2019 which introduced javascript as an alternative scripting language to VBA.

When one developer said they rewrote office into JavaScript. The assumption is probably not accurate still

> Microsoft announcing they would offer this at some point in the future

No, the ran a consultation asking whether there is interest, got overwhelming support for it, but have not commented on it since.

That's because it's not in line with their strategy of telling everyone what they're having and they'll have to lump it.

In February this year, they said:

> Please know this survey is used to help influence various topics – both on Python as well as other related topics that the comments started to bleed into. Given the passion, I want to be clear this remains an area of exploration for us, without any specific timeline.

However, we haven't heard anything since, but that kind of timeline wouldn't be unexpected for a feature of this scale - they'd be unlikely to announce anything until the project is at least near-finished, if they're working on it.

[0] https://excel.uservoice.com/forums/304921-excel-for-windows-...

Cool, but I find even cooler the native support in LibreOffice, IIRC. https://wiki.documentfoundation.org/Macros/Python_Guide

It's getting to the point where simply opening a Microsoft Office document is roughly the same as opening a random .exe file.

I think we should treat them with the same kind of paranoia.

> t's getting to the point where simply opening a Microsoft Office document is roughly the same as opening a random .exe file.

This isn't exactly new. In fact it was much worse the late 90s / early 00s, the heyday of windows viruses. There was absolutely no security, opening an excel document automatically meant running arbitrary code that had full system access (via COM, etc). Many viruses spread over email by word and excel documents.

At least now, macros execution is disabled by default. Unless you explicitly enable automatic macro execution on all documents, I wouldn't be too worried about opening a documents. Of course, there's the occasional vuln (http://georgemauer.net/2017/10/07/csv-injection.html) but excel isn't exactly the only software with occasional findings of execution vulnerabilities.

Printing requires enabling macros. Most people knee-jerk-click «enable editing» anyway.

16 seconds demo: https://www.youtube.com/watch?v=KXzG3LBZEWI

The other direction is more interesting: Generating/templating Word or Excel documents using the COM interface using the comtypes/win32com python packages. That's what I currently use to generate release documentation as part of a delivery pipeline.

You can also call C# functions using https://excel-dna.net

or F#!

or VB.net (relevant to VBA users if they want to move 20 years forward at little learning cost)!

Or D: https://github.com/kaleidicassociates/excel-d

cool, was wondering if it's still maintained (last big update from May).

Something I was wondering: If xlwings reads a big chunck of data in a dataframe, is there a way to keep it in memory without the need to re-load it every time one wants to do something with it?

Our [video course](https://training.zoomeranalytics.com/p/xlwings) allows us to spend more time again on developing xlwings. With regards to your question: Please open a [GitHub issue](https://github.com/ZoomerAnalytics/xlwings/issues) and give us some more background of how exactly you want to use this.

Reply on news.ycombinator.com