9 ms·

Call Python functions from VBA in all Microsoft Office apps

75 points by fzumstein 8y ago

fzumstein 8y ago
16 seconds demo: https://www.youtube.com/watch?v=KXzG3LBZEWI https://www.youtube.com/watch?v=KXzG3LBZEWI
montenegrohugo 8y ago
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.
- cm2187 8y ago
  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.
  - mohammedbin 8y ago
    Do you remember how you came by this knowledge?
    - cm2187 8y ago
      I don't but it was around the announcement of Office 2019 which introduced javascript as an alternative scripting language to VBA.
    - tenryuu 8y ago
      When one developer said they rewrote office into JavaScript. The assumption is probably not accurate still
- MagnumOpus 8y ago
  > 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.
  - setquk 8y ago
    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.
- shakna 8y ago
  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-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language https://excel.uservoice.com/forums/304921-excel-for-windows-...
alphadelphi 8y ago
Cool, but I find even cooler the native support in LibreOffice, IIRC. https://wiki.documentfoundation.org/Macros/Python_Guide https://wiki.documentfoundation.org/Macros/Python_Guide
deleted 8y ago
[deleted]
yread 8y ago
You can also call C# functions using https://excel-dna.net https://excel-dna.net
- auxym 8y ago
  or F#!
  - cm2187 8y ago
    or VB.net (relevant to VBA users if they want to move 20 years forward at little learning cost)!
- nicwilson 8y ago
  Or D: https://github.com/kaleidicassociates/excel-d https://github.com/kaleidicassociates/excel-d
AdmiralAsshat 8y ago
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.
- auxym 8y ago
  > 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 http://georgemauer.net/2017/10/07/csv-injection.html) but excel isn't exactly the only software with occasional findings of execution vulnerabilities.
  - pletnes 8y ago
    Printing requires enabling macros. Most people knee-jerk-click «enable editing» anyway.
sonofgod 8y ago
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 https://github.com/sensiblecodeio/xypath )
- montenegrohugo 8y ago
  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.
  - pletnes 8y ago
    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.
    - mutagen 8y ago
      Props to openpyxl, just used that module to do some quick and dirty spreadsheet manipulation to save some colleagues a bunch of time.
  - sonofgod 8y ago
    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.
Bishonen88 8y ago
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?
- fzumstein 8y ago
  Our [video course](https://training.zoomeranalytics.com/p/xlwings 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 https://github.com/ZoomerAnalytics/xlwings/issues) and give us some more background of how exactly you want to use this.
nuclx 8y ago
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.

75 points by fzumstein 8y ago

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

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.

cm2187 8y ago
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.
- mohammedbin 8y ago
  Do you remember how you came by this knowledge?
  - cm2187 8y ago
    I don't but it was around the announcement of Office 2019 which introduced javascript as an alternative scripting language to VBA.
  - tenryuu 8y ago
    When one developer said they rewrote office into JavaScript. The assumption is probably not accurate still
MagnumOpus 8y ago
> 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.
- setquk 8y ago
  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.
shakna 8y ago
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-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language https://excel.uservoice.com/forums/304921-excel-for-windows-...

alphadelphi 8y ago

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

deleted 8y ago

[deleted]

yread 8y ago

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

auxym 8y ago
or F#!
- cm2187 8y ago
  or VB.net (relevant to VBA users if they want to move 20 years forward at little learning cost)!
nicwilson 8y ago
Or D: https://github.com/kaleidicassociates/excel-d https://github.com/kaleidicassociates/excel-d

AdmiralAsshat 8y ago

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.

auxym 8y ago
> 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 http://georgemauer.net/2017/10/07/csv-injection.html) but excel isn't exactly the only software with occasional findings of execution vulnerabilities.
- pletnes 8y ago
  Printing requires enabling macros. Most people knee-jerk-click «enable editing» anyway.

sonofgod 8y ago

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 https://github.com/sensiblecodeio/xypath )

montenegrohugo 8y ago
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.
- pletnes 8y ago
  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.
  - mutagen 8y ago
    Props to openpyxl, just used that module to do some quick and dirty spreadsheet manipulation to save some colleagues a bunch of time.
- sonofgod 8y ago
  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.

Bishonen88 8y ago

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?

fzumstein 8y ago
Our [video course](https://training.zoomeranalytics.com/p/xlwings 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 https://github.com/ZoomerAnalytics/xlwings/issues) and give us some more background of how exactly you want to use this.

nuclx 8y ago

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.