Discover Office Scripts, the new way to automate Office
Office Scripts is available for some commercial users in Excel but it’ll spread to other Office apps and possibly/hopefully non-Business users. Even if you’re not interested now, they’ll likely become relevant in the future.
According to Microsoft, Office Scripts:
“allow you to record and replay your Excel actions …”
Which doesn’t seem like a big deal because VBA can already do that. The change is in the following phrase:
… on different workbooks and worksheets”
Scripts are stored in the Microsoft cloud store for your organization unlike VBA which is saved with a workbook or file linked to a workbook.
Like VBA, Scripts can be made by recording steps done manually.
The interface is a lot clearer than the VBA ‘Record Macros’.
Record Actions makes an Office Script that you can use or, more likely, adapt.
Office Script code might come as a shock to anyone accustomed to VBA. Once you look past the style differences, a lot of the concepts in VBA continue in Office Script, such as objects, ranges and collections.
Office Scripts are stored in the cloud and then linked to a workbook to allow anyone with edit access to the workbook permission to run the script.
Available scripts appear in the Automate tab
Office Scripts with Power Automate
Because Office Scripts can be saved in the cloud, they can be used independently of workbooks. Users could open a workbook then choose from a library of centralised scripts.
A good example of this is Power Automate, Microsoft’s IFTTT system for commercial users.
For example, Power Automate can be triggered when a new file is added to an online folder. A newly arrived file can be manipulated automatically by a Script. That’s something VBA can’t do.
What about VBA?
VBA isn’t going anywhere. Existing and new VBA code will continue to work for the foreseeable future.
Who gets Office Scripts
Right now, Office Scripts have very limited access, you need:
- Only Excel on the web has Script access for now.
- The workbook saved to OneDrive for Business
- A Microsoft 365 commercial or education licence which includes access to Office 365 desktop apps. That means plans like Business Basic do NOT qualify but Business Standard does.
Microsoft’s plan is to extend Office Scripts to at least Word and PowerPoint plus, of course, the desktop apps.
Hopefully, Office Scripts will be expanded to allow all Microsoft Office 365 users access. If Microsoft wants Scripts to succeed, it needs to be available to all.
Microsoft has published a lot of information about Office Scripts.
- Office Scripting fundamentals
- Record, edit, and create Office Scripts in Excel on the web
- Read workbook data with Office Scripts in Excel on the web
- Sample Excel Scripts
- ExcelScript reference
- Best practices in Office Scripts
- Office Scripts Dev Center
Curious about how Scripts are shared?
Microsoft 365 Admins can control access to Office Scripts.
Being Microsoft documentation, there’s no place for bugs or limitations. There’s sure to be problems with this first public release of Office Scripts plus things that are possible in VBA but not (yet) in Scripts. That’s left for paying customer to
waste time discover on their own.