This is fantastic - I am going to start testing it now. I write a fair amount of VB macros, and would much prefer to be able to use Python.
And, if they could create a plugin that allowed Libre Office to do the same, they could solve one of the last remaining major interoperability issues with LO and Excel: the inability to run VB macros in LO. That would be huge.
If you are trying to automate some actions in the UI, use the record macro functionality and then edit the code. You can usually remove or combine a lot of the code it recorded.
If you want to use dictionaries/hashtable there is a reference you can add called scripting.dictionary.
When you search google for how to do stuff in VBA, most of the best answers are in Ozgrid and MrExcel sites, and of course StackOverflow.
My favorite reference is the VBA Developer's Handbook by Getz & Gilbert from 2001.
You can probably learn everything you need from Googling for solutions to problems, but that book was great when I was committed to working with Excel and Access for a while. It covers a lot of things you're not going to think to search about.
If you have any specific questions about VBA feel free to contact me. I spent a couple of years on macros in various contexts and still have loads of links. (I second the links already posted)
In LO you cannot define format strings that automagically scale the number, e.g. "d = "0"mm"%%% to use meters for further calculations yet display in mm.
Not the same thing, but this made me think about it and readers might be interested to know that the xlrd, xlwt and xlutils python modules (http://www.python-excel.org/) allow you to manipulate Excel files in Python, without even having Excel installed (it's pure Python, so it also works on Linux, for example).
Looks like xlrd may have been updated to read newer formats, but xlwt is still limited and cannot write .xlsx format spreadsheets, for example. openpyxl _can_ handle the newer formats, but only the newer formats.
Working with Excel spreadsheets in general is a mess. There really isn't a single library that does everything well and if you want of be able to get all the information like formatting, formulas, etc... the only really complete solution is to use pywin32 and com which requires Excel and isn't particularly fast or easy either.
It is a bit of a mess; documentation often amounting to a few example calls. However, I've written a fairly well received data analysis package for a government department wherein the input and output had to be in excel spreadsheets (-sigh-), using only xlrd and xlwt, alongside numpy and scipy. It's a bit Rubegoldbergian (because.. excel), but I owe a lot to the authors of xlrd and xlwt (they've even supported named regions). So i'll be curious to see how xlwings implements the numerous methods available from xlrd/xlwt.
Not at all, it's great to see more products helping people get away from VBA. xlwings is well-done and has some features we don't (e.g. Mac interop), and likewise we have some features they don't (e.g. UDF's).
Ben at DataNitro[1], a paid solution that's been around for a while, has been awesome in my occasional correspondence with him as a user. Anyone looking for xlwing's functionality with commercial support etc should give them a look.
The documentation doesn't seem very mature, but Python is supposedly supported as a scripting/macro language, and it is also possible to interact with LO at the API level.
Very cool, just this week I was looking for something exactly like this to help automate reporting that absolutely has to be Excel. I wonder how well it plays with PivotTables and how it handles lots of data. I tried all the other python Excel libraries, and they either had memory errors with our data (It's not even that big, but it's much larger than we should be using Excel for), or were fine on memory, but would destroy PivotTables in the sheets they wrote.
For anyone running 64-bit Office and getting a compile error, to get it to run, just add the PtrSafe keyword to the Declare line like this,
To run a python function you do need to write one line of VBA code and you also need a vba file that comes with the package so although its not very much to add on the VBA side the title here seems a bit misleading.
From what I gather watching the video, you only need the xlwing.bas module and the supporting VBA code if you plan to invoke python from within the Excel file. If you're only manipulating an Excel file with Python externally, which was covered in the beginning of the video, it doesn't appear you need the module and VBA code.
I use it since today, and I'm very impressed. I work for a financial startups, with old fashioned non-tech guys who works only on excel. Xlwings sove me from VBA programmation for my algorithms ...
This may not be an issue where you work, but how do you get away from those who "want to see the formulas" and "want to be able to modify the spreadsheets"?
So this talks via IPC to Excel? How does it perform when working with huge data sets? Is the copying back and forth between the two processes a bigger bottleneck than the Python interpreter itself?
Very nice indeed. My team do statistical work, usually delivered in Excel, and we're going to be focused on automation / productivity quite heavily in the next few months. I need to put this through its paces a bit more than putting 'Hello World' in A1 - but it raises the tantalising prospect of a bunch of R and VBA work turning into a bunch of R and Python work.
Interesting usage. Personally I try to use CSV and code in R. R has libraries where I can pull information in BUT I am always afraid of issues with a non-open file format.
In the Universe of everything is done in Word and I am hard pressed to get people to even use Excel these approaches might make my job easier.
This has been available for over a decade with the win32com client using COM. You can easily register python classes so that you can call them from Excel and easily call Excel from Python. There is nothing new in this video. I put a simple tutorial on how to do some of this last year. http://detroitcoder.me/2013/11/26/video-create-python-object...
However, I still haven't looked how the reverse is done (by xlwings.bas in Excel). Is it that XLWings also implments a COM server from which VBA can run Python?
Xlwings is something I did not know before writing the post, but it does look great and I would love to try it as soon as possible. It basically has all the advantages of Datanitro but it is free.
Can anyone compare this with PyXLL[1]? It does require add-ins, but xlwings requires a VBA module. From the instructions, importing the two seem about equally complicated.
I am more curious about technical differences than the difference in license (I know that PyXLL is closed source, while XLWings is F/OSS, BSD-licensed).
PyXLL embeds python, and so runs python in-process (that is, in the same process as Excel, without spawning a child process).
PyXLL fully supports UDFs (user defined functions) in a fast, efficient way. Calling a pyxll function (UDF written in python) calls into the in-process python interpreter and so there is no cost of starting up a new python process or calling into an out of process COM server.
Excel and Python types are converted to and from each other in optimized C code, depending on the argument and return types specified for the function (UDF). The type conversion is user extensible and so can handle automatically converting Excel ranges to and from pandas DataFrames (just one example).
The full Excel API is accessible from pyxll using the well known and established win32com package.
PyXLL works with Python 2 and 3, 32 bit and 64 bit (all versions from python 2.3 and 3.4).
PyXLL supports newer features of recent Excel releases like multi-threading and asynchronous functions, while maintaining compatibility with older versions.
PyXLL can expose python functions as Excel macros and menus as well as UDFs.
If you want to script Excel from a process outside of excel (eg IPython notebook) you can use win32com very easily without the need for any other package. But, if you want to have a python interpreter running inside Excel to be able to write performant UDFs as an alternative to writing VBA or C addins as far as I'm aware PyXLL is still the only package capable of doing that[1].
PyXLL has commercial support and is partnered with a major python training and consultancy company, Enthought[2].
[1]DataNitro claims to be able to do with, but according to their docs you are limited to only being able to run one Excel instance at a time, which for most real world users is too limiting in my experience.
It doesn't really make much sense to compare xlwings with PyXLL, as they perform a slightly different function and actually could be used together.
PyXLL is more comparable to ExcelPython[1], which is open source does a similar thing (enables you to write UDFs in Python). ExcelPython hosts the Python process outside the Excel process but it
a) does not restart the Python process with every UDF therefore has good performance
b) does not require any registration of COM servers - therefore doesn't require admin rights
c) does not require an add-in (there is an add-in, but it just facilitates development, it's not actually required to use the resulting workbook).
Very nice although the real pain of programming against Office APIs isn't the language but the Office object models themselves, which seem to have been invented in hell by multiple demons that weren't communicating with each other.
Office apps on the Mac are automated using Scripting Bridge [1]. Applescript is one way to drive a scriptable app, but Python and other languages can also use the bridge. I built a substantial Word addin on the Mac in C# on Mono, using the Scripting Bridge.
Would you rather edit a messy dataset in pandas or in Excel? What about automatically generating reports? Going to give managers your ipython printout?
In the past, I've used Python to automate Excel, Outlook, and some non-MS products like Catia.
Honestly, I don't see the advantage here. FOSS is nice, and OSX support is nice, but if you're interacting with MS products, FOSS is not a concern anyway, and it's probably running on Windows...
What would be really nice is if it supported multiple backends, so I could run the same Python code with Excel, Apple's Numbers, Google spreadsheet or Gnumeric, and have it work the same on all of them.
Cool. The problem with all these big "VBA replacements" (and don't get me wrong, I've done both VBA and VSTO and the latter is more pleasant to work with) is that they never seem to provide replacements for UDFs, which is probably the #1 reason you'd want to use VBA in the first place.
And, if they could create a plugin that allowed Libre Office to do the same, they could solve one of the last remaining major interoperability issues with LO and Excel: the inability to run VB macros in LO. That would be huge.