Use VBScript actions to speed up Excel automations
💡 Sometimes, it can be tedious or difficult to create Microsoft Excel automations using Power Automate Desktop’s built-in actions or even macros. Using Copilot and the “Run VBScript” action, we can ease and speed up the process.
Problem Description
As an example, take the use case where an Excel sheet must be duplicated, renamed, and then filtered based on certain criteria.
Power Automate Desktop currently does not have built-in Excel actions for duplicating a sheet, hence developing the automation would be slightly complex and/or slower.
Using UI actions such as right clicking the sheet tab, clicking “Move and Copy”, checking “Create a Copy”, etc. can be unreliable and time consuming.
Using Excel actions to add a new worksheet and copy the data sometimes leads to data formatting issues.
What can we do?
Solution
One way to solve this problem is to leverage the new “Run VBScript” action together with the new Copilot feature. Conveniently, the citizen developer does not need to have extensive knowledge on how to write code in VBScript.
We will add and use the action “Run VBScript” to the desktop flow. (Note that we do not need to have the “Launch Excel” before this, and the input Excel file should be closed.)
We will click on “Generate script with Copilot”.
We will then describe what we want our script to do, and then click on “Generate”. For example:
After a few moments, the script will be ready. Click “Use this script”, and “Save”.
Our script is now ready to be run.
Additional Information
Last updated on: 9 Dec 2024
Tested version(s): 11.2411.209.0
Prerequisites: Excel
Dependencies: None
Known issues:
Copilot is currently a “preview” feature. Sometimes, manual modification of generated code may be necessary.