KomuraSoft LLC
Contact
Menu

Migrating Excel VBA Macros to Power Automate — What to Replace with Office Scripts, and What to Leave as VBA

· · Power Automate, VBA, Excel, Office, Office Scripts, Business Automation, Cloud Flow, Legacy Asset Reuse, Migration, Technical Consulting

“This process is running on an Excel macro, but the person who built it isn’t with the company anymore. Can we migrate it to this Power Automate thing?” — I get asked this kind of question a lot. Daily aggregation jobs and report generation built in VBA are still running in production at plenty of companies, and news of the macro author leaving, or of VBScript being deprecated, has left a lot of people uneasy about how much of a future their VBA assets actually have.

Let me say this up front: “migrating VBA to Power Automate” is, more precisely, a combination of several separate stories — rewriting part of the VBA as an Office Script and calling it from Power Automate, replacing another part with a connector, and leaving the rest as VBA. There is no mechanism that runs a VBA macro as-is inside a cloud flow. This article works through which processing can migrate where, and which parts should stay in VBA, checking against Microsoft Learn’s primary documentation as we go.

1. The Bottom Line First

  • You cannot run a VBA macro directly from a Power Automate cloud flow. The Excel Online (Business) connector can handle .xlsm files, but it cannot execute the macros inside them — only Office Scripts can be run that way.1
  • Office Scripts are not a full replacement for VBA. They are Excel-only, and cannot operate other Office apps, respond to events, use UserForm, do COM/OLE integration, or access local files.23
  • On the other hand, processing that stays entirely inside the workbook — formatting, aggregation, transcription — is well worth converting to an Office Script. Combined with Power Automate, you get scheduled runs or triggers on an incoming email or form, giving you the kind of “runs without ever opening Excel” automation that VBA could never achieve.2
  • For something as simple as adding or reading table rows, you can handle it directly with Excel Online (Business) connector actions, without writing a script at all — just be sure to check the constraints first (a table is required, the default cap is 256 rows, files are capped at 25 MB, and so on).4
  • For processing that absolutely requires VBA, there’s the option of launching the existing VBA as-is with a desktop flow’s Run Excel macro action. This lets you automate the launch and the surrounding steps without rewriting the VBA at all.5
  • Office Scripts require a commercial or education Microsoft 365 license plus OneDrive for Business. VBA needs no additional license, so this is a precondition you need to settle before deciding on a migration.62

2. Sorting Out How VBA, Office Scripts, and Power Automate Relate

Let’s first sort out the cast of characters — there are a lot of similar-sounding names here, and that’s the main source of confusion.

Desktop sideCloud sideRun scriptAdd/get/update rowsRun Excel macroInvokesPower Automatedesktop flowExisting VBA macroPower Automatecloud flowExcel Online BusinessconnectorOffice ScriptExcel automation in TypeScriptExcel tableWorkbook on OneDrive / SharePointWorkbook on local drive / file server
  • Office Scripts is a scripting feature that automates Excel using TypeScript. It runs on Excel on the web, Excel for Windows (version 2210 and later), and Excel for Mac, and can be invoked from a Power Automate cloud flow. The fundamental difference from VBA is that VBA is designed for the desktop, while Office Scripts is designed for the cloud and cross-platform use.26
  • The Excel Online (Business) connector operates Excel files on OneDrive for Business or SharePoint from a cloud flow. It has row-operation actions for tables, plus a “Run script” action that executes an Office Script.7
  • Desktop flows (Power Automate for desktop) is RPA that operates apps on Windows. Its set of Excel actions includes an action that runs a VBA macro.5

What VBA Can Do That Office Scripts Cannot

This is the single most important point for the migration decision. Here it is, organized around Microsoft Learn’s “Differences between Office Scripts and VBA macros.”2

What VBA can do How Office Scripts handles it
Operating other Office apps besides Excel, such as Word, Outlook, or Access Not possible. Office Scripts is Excel-only and cannot be used with other Office apps
COM/OLE integration with other apps or external components, and Win32 API calls (Declare) Not possible. A script can only access the workbook, not the host machine
Event-driven code such as Workbook_Open or Worksheet_Change Not possible. Excel-level events are not supported; the only ways to run a script are manual execution or a call from Power Automate
Custom dialogs or input screens built with UserForm Not possible. Any UI beyond the workbook itself (dialogs, task panes) is Office Add-ins territory3
Local-folder file operations (opening, saving, listing files) Not possible. Anything that reaches outside the workbook has to be done with a Power Automate-side connector (OneDrive, SharePoint, etc.)
The broad range of Excel functionality, including features specific to desktop Excel Partially not possible. VBA covers a wider range of Excel functionality; Office Scripts is positioned to cover roughly the scenarios of Excel on the web

Calling external web services also needs care. Office Scripts itself supports limited external calls, but when run through Power Automate, any external API call (fetch) from inside the script fails. External integration has to be designed into the flow’s own connectors or HTTP action, not the script.26 Note, though, that the general-purpose HTTP action for calling an arbitrary API is treated as a premium connector, so a Microsoft 365 license alone doesn’t unlock it.8 Prefer a standard connector wherever one exists for the service you’re integrating with, and if calling a proprietary API is unavoidable, fold the extra cost of a premium license into your migration decision.

Many readers looking at this list will feel like “basically our entire macro is on here somewhere.” That’s a fair reaction — a VBA macro that’s grown over the years routinely includes things like sending mail through Outlook, walking a folder tree, and taking input through a UserForm. But before giving up, try breaking the process apart. For a macro that “collects files from a folder, reformats a workbook, and sends it by email,” file collection and email sending are exactly the kind of thing Power Automate connectors are good at, and if you convert only the workbook-formatting part into an Office Script, the whole thing can migrate — that pattern comes up more often than you’d expect.

3. A Decision Table — Choosing Among Five Options by Use Case

The migration target isn’t limited to “convert it into an Office Script.” In practice, think in terms of these five options.

Option Fits cases where Preconditions / constraints
(1) Leave it as VBA Individual or small-team, hands-on work. UserForm, events, or integration with other apps are genuinely essential, and someone is on hand to run it manually without issue No extra license needed.2 But address the risk of it becoming a one-person, unmanaged macro through an inventory
(2) Convert to an Office Script and run it from a cloud flow Formatting, aggregation, or transcription that stays entirely inside the workbook. You want scheduled runs, or a trigger on incoming email or a form. The workbook can live on OneDrive / SharePoint Requires a commercial/education Microsoft 365 license.6 Events, UserForm, and the like need to be redesigned
(3) Handle it directly with the Excel Online (Business) connector Simple processing limited to adding, getting, updating, or deleting table rows — e.g. “append form responses to Excel” Assumes the target range is already a table. Limits include a default of 256 rows and a 25 MB file size cap4
(4) Launch the existing VBA as-is via a desktop flow No room to rewrite the VBA, but you want to automate just launching it and the surrounding steps (placing files, sending notifications). The workbook lives on an internal file server Requires a Windows machine. Triggering from a cloud flow needs a Power Automate Premium user license for the connection owner for attended runs, and a Process license for unattended runs910
(5) Rebuild it in .NET Large-volume data processing, complex business logic, where automated tests or Git-based version control are essential, or where report generation is the main goal Highest development cost, but the best long-term maintainability and performance. Reports can be made Excel-independent using Open XML and similar approaches

The flow below roughly decides which one to pick.

NoYesNoother-app UI / COM automationYesYesYesFormatting/multiple sheets/logic involvedNointernal file server onlyNoYes/performance or tests neededPick one VBA macroDoes the processstay entirely inside the workbook?Can the part outside Excelbe replaced with a connector?e.g. sending email, collecting filesSplit the process:Excel part -> Office Scriptrest -> flow connectorsCan you affordto rewrite the VBA?Can the workbooklive on OneDrive/SharePoint?Is it justrow operations on a table?Handle it directly with theExcel Online Business connectorConvert to an Office Scriptand run it with Run scriptLaunch the existing VBAas-is via a desktop flowRebuild in .NET

Worth noting: option (4) is not a “final destination” for the migration — it’s life support. The underlying VBA’s dependence on a single person and the lack of version control both remain exactly as they were. Even so, it’s practical enough as a stopgap: automate the launch first to eliminate manual execution mistakes, and use that time to gradually move the VBA’s internals over to Office Scripts or .NET.

4. Implementation Patterns — the Run Script Action and Connector Limits

Running the Script (Run Script)

To run an Office Script from a cloud flow, you use two actions on the Excel Online (Business) connector. Run script is for scripts stored in OneDrive (the default location), and Run script from SharePoint library is for scripts saved in a team’s SharePoint library.7

A typical setup looks like this.

  1. Trigger: schedule (Recurrence), incoming email, form submission, etc.
  2. Identify the target workbook via the OneDrive / SharePoint connector
  3. Run the Office Script with Run script and capture its return value
  4. Use the return value to send a Teams notification or an email

A script can accept parameters and return a value, which lets you pass data both ways — for instance, “pass a search key from the flow, have it return the matching data from inside the workbook, and turn that into an email on the flow side.” Note that the “schedule scripts to run” feature that used to live in Excel itself is currently disabled, and scheduled execution is now guided toward being built as a Power Automate flow instead.11

Check the Limits First

The single biggest pitfall when migrating from VBA is the set of limits specific to cloud execution — ceilings that effectively didn’t exist for desktop VBA are firmly in place here.

Limit Value Source
Office Scripts request/response size per call Up to 5 MB 6
Cells in a single Range Up to 5 million cells 6
Run script call frequency 1,600 calls per user per day, and no more than 3 per 10 seconds 64
Synchronous processing timeout 120 seconds 6
Parameter size passed to Run script Up to 30,000,000 bytes (about 28.6 MB) 6
Excel file size the connector can handle Up to 25 MB 4
Connector request size Up to 5 MB per request 4
Connector API calls 100 calls per 60 seconds per connection 4

If you’re operating directly on a table via the Excel Online (Business) connector, check these additional points.4

  • Row operations assume a table. Actions like “Add a row into a table” and “Get a row” are designed around specifying a table (a ListObject) — they cannot target data that’s just written into a plain cell range. A sheet from the VBA era that “starts writing data somewhere around cell A1” needs to be converted into a table before migration.
  • List rows present in a table returns only up to 256 rows by default. Enable pagination to retrieve all rows. The columns returned are also capped at the first 500 by default.
  • Writes can take up to roughly 30 seconds to be reflected. A file may also be locked for up to 6 minutes after the connector uses it.
  • Simultaneous writes from multiple clients are not supported. Having a flow write into a workbook that’s left open in desktop Excel, for example, is a recipe for conflicts and inconsistency.
  • The supported formats are .xlsx and .xlsb (binary workbook). .xlsm can only be selected via the file browser in the Run script action; using it in other actions requires specifying the file ID directly. But as noted above, the VBA macros inside it will not run. A .xlsm containing ActiveX controls or form controls may not work with the connector at all, so verify it beforehand.1

If you try to convert a “macro that aggregates hundreds of thousands of rows every night” into an Office Script as-is, you’ll run straight into the 5 MB limit and the 120-second timeout. It’s healthier to route processing at this scale to .NET (option 5) from the start.

Launching VBA from a Desktop Flow

If you’re keeping the VBA as-is, open the workbook with a desktop flow’s Launch Excel, then run the macro with the Run Excel macro action, specifying the macro name (with arguments separated by semicolons). To use a macro from the Personal Macro Workbook (PERSONAL.XLSB), you need to enable “Nest under a new Excel process” and “Load add-ins and macros” in Launch Excel’s advanced settings.5

Triggering a desktop flow from a cloud flow requires a registered machine and a desktop flow connection; attended runs require a Power Automate Premium user license for the connection owner, and unattended runs require a Power Automate Process license on the machine (or the legacy Unattended RPA add-on).910 The overall design of desktop flows — error handling, the prerequisites for unattended execution, and so on — is covered in more detail in a separate article, “Automating Business Processes with Power Automate — Choosing Between Cloud Flows and Desktop Flows, and Designing Error Handling.”

5. Licensing and Prerequisites

The point that Office Scripts is not “a free feature that comes with Excel” is something you should confirm early in the migration decision.

  • Using or authoring Office Scripts requires a commercial or education Microsoft 365 subscription license (Office 365 Business / Business Premium / ProPlus / ProPlus for Devices / A3 / A5 / E1 / E3 / E5 / F3) and OneDrive for Business. Organization-wide sharing links being enabled, and an internet connection with connected experiences enabled, are also prerequisites.6
  • Using Office Scripts from Power Automate also requires a Microsoft 365 business license. E1 and F3 can run scripts via Power Automate but cannot use Excel’s own Power Automate integration (features like Excel’s “Automate my work”).7
  • Office Scripts on personal/home Microsoft 365 is still in preview, requiring enrollment in the Microsoft 365 Insider program — it cannot be assumed as a base for business use.6
  • On the client side, it runs on Excel on the web, Excel for Windows (version 2210 and later), and Excel for Mac.6
  • VBA, by contrast, is built into desktop Excel and requires no special license.2

In other words, in an environment where “our in-house Excel is a perpetual (one-time-purchase) license and we don’t have a Microsoft 365 subscription,” the Office Scripts option doesn’t exist in the first place. In that case, the migration target narrows down to launch automation via a desktop flow, or a rebuild in .NET.

The security differences are also worth understanding. A VBA macro runs with the same privileges as Excel, so it has access to the entire desktop; an Office Script can only ever access the workbook, and the signed-in user’s authentication token is never handed to the script. Administrators can control, at the tenant or group level, whether Office Scripts can be used at all and whether they can be used from Power Automate. For IT departments that have struggled to keep macro security under control, this manageability is one of the real benefits of migrating.2

6. How to Approach the Migration — Inventory, Classify, Migrate in Stages

I’d recommend running the actual migration in these three stages.

Stage 1: Inventory. List out which workbooks have which macros, running under whose ownership. The same procedure used for VBScript-deprecation readiness applies here, so see the separate article “A VBA and Internal-Tools Audit Guide for VBScript Deprecation” for the concrete steps. It’s typical to find that 20-30% of macros turn out to already be unused by the time the inventory is done, and simply excluding those from the migration cuts the workload right away.

Stage 2: Classify. Map the remaining macros onto the decision table in Chapter 3. The trick here is to break each macro apart by unit of processing rather than treating it as a single indivisible thing. A macro that does “collect files -> reformat -> send email” can be decomposed into a combination of (3) the connector plus (2) an Office Script plus an email connector. Only the parts that still depend on VBA even after decomposition — interactive input via UserForm, COM operations on other apps, and the like — become candidates for (1) leaving in place or (4) going through a desktop flow.

Stage 3: Migrate in stages. Don’t rewrite every macro at once — set priorities.

  1. Start with macros that run frequently and stay entirely inside the workbook. The migration payoff is large here, and it’s also a good way to get comfortable with Office Scripts. VBA and TypeScript are different languages, but Office Scripts also has an Action Recorder, which lets you build a first draft with much the same feel as VBA’s macro recorder.2
  2. Run the migrated flow in parallel with the original, cross-checking results against the VBA version for a while. This is where you surface discrepancies caused by things like the switch to a table or differences in date formatting.
  3. For anything you decide to keep as VBA, “keep it under management.” Document a clear procedure and owner, and where possible, shift it toward being launched via a desktop flow so an execution log is retained.

The broader question of VBA’s future, and how to think about the cases where it’s still fine to keep using VBA, is covered in “What Is VBA? Its Limits, Its Future, and When and How to Realistically Replace It”; a comparison of approaches for moving report generation over to .NET is covered in “How to Build Excel Report Output — COM / Open XML / Templates.” And if you’re considering operating Excel from .NET via COM, there’s a classic problem where the process lingers afterward, so also check “The EXCEL.EXE-Left-Running Problem in C# Excel Automation — COM Reference-Release Patterns and When to Replace It.”

7. Summary

The answer to “can we migrate from VBA to Power Automate” is: “you can’t port an entire macro wholesale, but if you break the processing apart, most of it has somewhere to go.”

Processing that stays entirely inside the workbook becomes an Office Script called from a cloud flow; simple row operations go straight to a connector; anything involving integration with other apps or a UserForm either stays as VBA or gets life support via a desktop flow; and large-volume data or complex logic gets routed to .NET. Do this sorting up front, and the migration doesn’t need to happen all at once — you can work through it gradually, starting from the most frequently run macros.

Conversely, if you skip the sorting and just start with “let’s rewrite everything as an Office Script,” you’ll run into the event and UserForm wall, the 5 MB / 120-second limits, and the table requirement, one after another, and the effort will stall. Whether a migration succeeds comes down far more to the inventory and classification done before you start than to skill at rewriting code. Treating VBA assets as something to be “sorted and put to good use,” rather than “something to throw away eventually,” ends up being the cheapest approach in the end.

Komura Software LLC handles everything from taking inventory of existing Excel/VBA-driven processes to designing a phased migration to Power Automate, Office Scripts, and .NET that builds on what you already have.

References

  1. Microsoft Learn, How to use macro-enabled files in Power Automate flows. On macros inside .xlsm files not being runnable from Power Automate (only Office Scripts are), .xlsm only being selectable via the file browser in the Run script action, and files containing ActiveX/form controls sometimes not working. ↩ ↩2

  2. Microsoft Learn, Differences between Office Scripts and VBA macros. On Office Scripts being Excel-only and workbook-scoped only, the lack of event support, COM/OLE being VBA-only, Office Scripts requiring a commercial/education license versus VBA shipping standard in desktop Excel, and differences in macro recording and security controls. ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10

  3. Microsoft Learn, Differences between Office Scripts and Office Add-ins. On Office Scripts only being able to interact with the workbook, and Office Add-ins being required whenever dialogs or custom UI controls are needed. ↩ ↩2

  4. Microsoft Learn, Excel Online (Business) - Connectors reference. On the 25 MB maximum file size, 5 MB per-request limit, the default 256-row cap on List rows with pagination, the default 500-column cap on retrieved columns, the Run script limits of 3 calls per 10 seconds and 1,600 per day, up to 6 minutes of file locking after last use, up to 30 seconds of delay for writes to be reflected, throttling of 100 calls per 60 seconds per connection, lack of support for simultaneous editing, and supported file formats. ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7

  5. Microsoft Learn, Run macros on an Excel workbook. On running a VBA macro via the desktop flow’s Run Excel macro action, and the “Nest under a new Excel process” / “Load add-ins and macros” options in Launch Excel being required to run a macro from PERSONAL.XLSB. ↩ ↩2 ↩3

  6. Microsoft Learn, Platform limits and requirements with Office Scripts. On the list of required licenses and the OneDrive for Business requirement, supported platforms (Excel on the web / Windows version 2210+ / Mac), the 5 MB request/response limit, the 5-million-cell range limit, the 1,600-calls-per-day limit on Run script, the 120-second timeout, the 30,000,000-byte parameter limit, external API calls (fetch) failing when run via Power Automate, and personal/home being in preview. ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10 ↩11 ↩12

  7. Microsoft Learn, Run Office Scripts with Power Automate. On the Run script / Run script from SharePoint library actions, the Microsoft 365 business license required to use Office Scripts in Power Automate, and E1/F3 being able to run scripts via Power Automate but not use Excel’s own integration features. ↩ ↩2 ↩3

  8. Microsoft Learn, Guidance: Migrate from classic workflows to Power Automate flows in SharePoint. On Power Automate’s general-purpose HTTP action being a premium connector. ↩

  9. Microsoft Learn, Trigger desktop flows from cloud flows. On the prerequisites for triggering a desktop flow from a cloud flow (a registered machine, a desktop flow connection, and licensing appropriate to the execution mode). ↩ ↩2

  10. Microsoft Learn, A failed license check on a desktop flow run. On attended desktop flow runs requiring a Power Automate Premium user license for the connection owner, and unattended runs requiring the Unattended RPA add-on or a Power Automate Process license. ↩ ↩2

  11. Microsoft Learn, Office Scripts in Excel. On Excel’s own script-scheduling feature being temporarily disabled, with guidance to build scheduled execution as a Power Automate flow instead. ↩

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Frequently Asked Questions

Common questions about the topic of this article.

Can an existing VBA macro be run directly from Power Automate?
Not from a cloud flow. The Excel Online (Business) connector can handle the .xlsm file itself, but it cannot run the macros inside it — only Office Scripts can be executed that way. A desktop flow (Power Automate for desktop), on the other hand, has a Run Excel macro action, which can run the VBA macro in a workbook opened with Launch Excel exactly as it is. If you just want to automate launching the macro without changing its VBA code, going through a desktop flow is the realistic option.
Is an Office Script a full replacement for VBA?
No. Office Scripts are Excel-only and cannot operate other Office apps such as Word or Outlook. Because a script can only access the workbook, it cannot do local file access or COM/OLE integration either, and there is no event-driven model like Workbook_Open or custom dialogs built with UserForm. On the other hand, processing that stays entirely inside the workbook — formatting, aggregation, transcription — can be converted to an Office Script, which then lets you trigger it on a schedule or on an incoming email or form response through Power Automate. The accurate way to think about it is that the two don't substitute for each other; they simply cover different territory.
What license do I need to use Office Scripts?
A commercial or education Microsoft 365 license (Office 365 Business / Business Premium / ProPlus / E1 / E3 / E5 / F3 / A3 / A5) plus OneDrive for Business. Running an Office Script from Power Automate also requires a Microsoft 365 business license; E1 and F3 can run scripts via Power Automate but cannot use the Power Automate integration features inside Excel itself. This is a contrast with VBA, which ships standard with desktop Excel and needs no additional license, so you should check your organization's licensing before deciding on a migration.
Can Excel Online (Business) connector actions alone complete the automation?
For simple processing, yes, but there are quite a few limits to watch out for. Adding, getting, updating, or deleting rows assumes the range is already an Excel table (a ListObject); List rows present in a table returns only up to 256 rows by default and requires pagination to be enabled for more. File size is capped at 25 MB, a single request at 5 MB, and writes can take up to about 30 seconds to be reflected. A practical split is to use the connector directly for reading and writing a handful of rows in a table, and reach for an Office Script for cell formatting or anything spanning multiple sheets.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.

Contact
Back to the Blog