Use Python in Excel without add-ins(xlwings.org) |
Use Python in Excel without add-ins(xlwings.org) |
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.
http://msdn.microsoft.com/en-us/library/office/ff838238%28v=...
Close behind are Collections and UDTs. The best thing to level up after you're comfortable is with 'Implements' for interfaces.
I found that search engines are as good as any book for this stuff. It's smeared all over the internet.
The best place I know of to get a good understanding is http://www.cpearson.com/excel/MainPage.aspx, but it's not a tutorial.
edit: that's what I get for keeping this window open too long:) That's 2 votes for cpearson.com.
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.
http://www.cpearson.com/Excel/Topic.aspx
Old websites but in my case I dealt with Office <= 2003
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.
It's a bit of a mess.
(I'm one of the DataNitro founders.)
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.
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,
Private Declare PtrSafe Function [etc...]http://blogs.msdn.com/b/fsharpteam/archive/2013/07/16/combin...
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.
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?
https://github.com/ZoomerAnalytics/xlwings
The magic you're looking for is here:
https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwin...
Surprisingly simple actually.
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.
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).
[1] https://developer.apple.com/library/mac/documentation/Cocoa/...
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.
In particular it's in-process and does UDFs.
Admittedly it isn't open-source but it is free for non-commercial use.
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.
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).
It's a shame, having a simple python scripting system built into Calc would be a major advantage.