You can access the VBA object model from Python once you've got a COM conduit. There are a few Python libraries to allow you to do this; you'll be able to write VBA-like code in Python accessing Excel's internals in a similar manner. Debugging can be a bit painful though. I've done this both from Matlab and from Python, and it's fine for smaller projects, writing structured/formatted Excel workbooks, etc.

I agree that Excel as a GUI can be phenomenal.

Fascinating to think of Excel growing into a modern replacement for Visual Basic. The spreadsheet that becomes so important to the company it’s handed over to the software developers to make it into a real application is almost a meme at this point.

Being able to steadily enhance a spreadsheet into a real application without rewriting from scratch could be a real game changer.

visual basic has been included in excel for decades, its just called VBA not VB. if you thanos snapped VBA out of existence the world would probably stop functioning for several weeks.

I've used Python libraries to generate reports in Excel, frequently enough that at my last job I wrote a custom library to wrap xlsxwriter to simplify for my use case. Tremendously useful.

Would you mind explaining in more detail what your custom library enabled?

It's been long enough that my memories are fuzzy.

I do remember defining a specific format for a cover page: merging a large block of cells and customizing the font information within. I suspect I had other formatting convenience functions to make the reports more consistent.

I created a single data structure to wrap the workbook and worksheet objects and include a "current" row/cell tracker, so that I could invoke an "add row" function to write the next row in the sheet without indicating where in the worksheet the data should be added.

Each write function would then take as arguments the data structure and a string label to indicate which worksheet I wanted (numeric indexing was also an option, but I found strings to be the best way to make the code obvious about what worksheet we were actively modifying).

I also did some work with tagged data (via tuples) so my library could choose which write function to invoke in xlsxwriter. I don't remember the motivation for that.

Super helpful, thank you for taking the time to share this, I appreciate it.

XLWings – the product made by the author of this post – lets you do exactly that. You can make a Python script that connects to a live Microsoft Excel process, iterates over sheets and cells, reads/writes data to them, etc.

I’ve used it at work some years ago, and it’s a great product.

Since it was announced, my assumption has been that microsoft is putting python in excel like this to make using python to manipulate spreadsheets less common since that'd eat a lot of their moat around their program. If it were common enough, it wouldn't matter too much what spreadsheet program you were using.

That's one of the most important goals in my particular "shitty version of Excel", https://github.com/ironcalc/IronCalc

:)