Which online courses and books would you recommend to build competence in automation within the MS Office ecosystem?
I'm not a coder but I would like to automate some of the more mechanical/repetitive aspects of my work, e.g. generating reports from csv files, pulling comments from Word files into a separate document, dashboard creation of live data in Power BI etc.
I haven't read it in detail, and I don't know how difficult it is, but there's a free book on how to (among other things) automate Office applications with Python:
I have read it, and it has become my most recommended book for someone trying to get into programming or technology in general. It won't tell you how to do every single thign in the universe, but it can give a great starting point for automation and programming in general.
The documentation may look daunting, but VBA isn't that difficult to do useful things with. You'll be mostly copying/pasting from google like most professional coders :)
You can start with a macro and turn it into VBA, which is sometimes useful.
I would highly recommend learning PowerShell. It can handle all of those usage cases, and if you're using Windows, is almost certainly already installed on your system.
I've been using PowerShell to automate various tasks with differing levels of complexity, and I've had a lot of success so far.
I think that a good python library with some UI elements would be the best choice. The issue is that with any other UI tool (MS Access, Power BI, Alteryx, etc.) you eventually end up with edge cases or business requests that you can't fulfill. Me and my team are writing our own python library for automation, etl, scheduling and such and so far we have been successful in converting 1 alteryx user to our approach. It's not much but I think it's the right way to go about automation and data analytics.
I do most of my of work in this realm, using Python. I pull data from multiple databases each day with selenium and bs4, clean and transform using Pandas then generate pretty nice reports with ReportLab.
It's given me a lot of opportunities to flex my creativity on what was previously a fairly mundane Excel-centric role.
Having a supportive manager is really valuable for this stuff, though.
If you're not a coder, I highly recommend Flow (https://flow.microsoft.com/en-us/) Which is an automation platform designed to be used with MS office products.
There are also a lot of options in Azure , but they require a little bit more of coding (They are incredible powerful nonetheless).
I'm not a coder but I would like to automate some of the more mechanical/repetitive aspects of my work, e.g. generating reports from csv files, pulling comments from Word files into a separate document, dashboard creation of live data in Power BI etc.