Best automation solution across all Excel platforms: Web, Windows, Mac, both offline and online?

Hi Rick Win1, 

Thank you for replying, and the information that you have given to me I really appreciate it.    

According to your details, You've hit upon one of the key challenges with modern Excel automation in a hybrid M365 environment. The shift from purely desktop-based VBA to cloud-first solutions like Office Scripts introduces new constraints, particularly around offline access and sharing outside your tenant. 

Here's a breakdown of why you're facing this challenge and the closest you can get to a "single solution" given your requirements: 

The Core Problem: Cloud vs. Local Execution: 

• VBA: Designed for local execution. The code lives within the .xlsm file. This is why it works offline and can be easily shared. Its limitation is that it doesn't run in Excel for the web, iOS, or Android, and has limited (or no) support on Excel for Mac in some complex scenarios.
• Office Scripts (JavaScript/TypeScript): Designed for cloud execution. The scripts are stored in your OneDrive/SharePoint and are linked to the workbook. This enables them to run in Excel for the web, Windows desktop, and Mac desktop. However, they explicitly require an internet connection to run because they are executed in the cloud and need to access the script from your cloud storage. This is the critical blocker for your offline requirement.
• Power Automate (formerly Microsoft Flow): A cloud-based automation service. It can trigger Office Scripts or interact with Excel files in SharePoint/OneDrive. It's excellent for connecting Excel with other services (emails, databases, etc.) and for scheduled automation. But it's fundamentally an online service. Power Automate Desktop exists for Windows-only desktop automation, but it doesn't solve the cross-platform or Mac/iOS/Web problem for direct Excel file interaction.

*There is no single, perfect solution that meets all your criteria (Web, Windows, Mac, iPad, Online, Offline, External Sharing) for embedded automation within the Excel file itself, due to the offline requirement for non-desktop versions. 

However, you can achieve the closest approximation by leveraging a hybrid approach, accepting that some scenarios will have limitations. 

First of all, I recommended Hybrid Approach: Office Scripts + VBA (for critical offline functionality) 

This approach aims to maximize cross-platform compatibility while ensuring critical offline functions still work. 

1. Primary Automation: Office Scripts (for most M365 users and online scenarios) 

• Use Case: This should be your go-to for most new automation. It works great for users with the desktop Excel app (Windows/Mac) when online, and for all users on Excel for the web.
• Pros:  
  • Cross-Platform (Online): Runs on Excel for the Web, Excel for Windows (desktop), Excel for Mac (desktop).
  • Modern Language: Uses TypeScript/JavaScript, a more modern and widely used language than VBA.
  • Collaboration: Scripts are stored in the cloud (OneDrive/SharePoint) and can be easily shared within your M365 tenant.
  • Integration: Can be triggered by Power Automate for more complex workflows or scheduled tasks (e.g., nightly report generation).
  • Security: Office Scripts have a more constrained security model than VBA, making them generally safer (cannot access local file system, etc.).
• Cons:  
  • Requires Internet Connection: This is the biggest hurdle for your "offline in the air" and "poor internet" scenarios. Office Scripts cannot run offline.
  • No iPad/Mobile App Support (Directly): While Office Scripts run on Excel for the Web, they don't directly run within the native Excel mobile apps (iOS/Android). Users would need to access the workbook via a web browser on their iPad, which might not be ideal for the mobile experience.
  • External Sharing: When sharing outside your M365 tenant, the recipient needs to have their own M365 license that includes Office Scripts capabilities, and the script would need to be explicitly shared with them or copied to their environment, which can be cumbersome.

2. Offline/Legacy Automation: VBA (for critical offline desktop scenarios) 

• Use Case: For those specific users who must work offline on desktop versions (Windows/Mac) and require the automation, you will need to retain VBA macros.
• Pros:  
  • Offline Functionality: VBA code lives within the .xlsm file, so it works perfectly offline.
  • Broad Desktop Compatibility: Works on Excel for Windows and Mac desktops.
  • Easy Sharing (File-based): Just send the .xlsm file.
• Cons:  
  • No Web/Mobile Support: VBA does not run in Excel for the web, iOS, or Android.
  • Security Concerns: VBA macros can be a security risk if not managed properly (macro-enabled files can contain malicious code). This might require specific Trust Center settings for your users.
  • Maintenance Overhead: You'll be maintaining two different codebases (VBA and Office Scripts).

How to implement this Hybrid Approach: 

• Identify Critical Offline Tasks: Determine which automation tasks are absolutely essential for offline work. These are the ones you might keep in VBA.
• Migrate Online Tasks to Office Scripts: For everything else, start migrating your VBA code to Office Scripts.  
  • Action Recorder: For simple, repetitive steps, use the Action Recorder in Excel for the web to generate initial Office Scripts code.
  • Code Editor: For more complex logic, use the built-in Code Editor (TypeScript/JavaScript).
• User Interface:  
  • For Office Scripts, you can create buttons in Excel for the Web/Desktop to run the scripts.
  • For VBA, you'll continue to use existing macro buttons or forms.
• Communication & Training: Educate your team on which automation works where and under what conditions. "If you're online, use the buttons on the 'Automate' tab. If you're offline on your laptop, use the old buttons."

Alternative/Less Ideal Options (and why they might not fit): 

• Excel Add-ins (Office Add-ins using JavaScript/TypeScript): 
  • Pros: Can run cross-platform (Web, Windows, Mac, potentially iOS/Android, though mobile support can be limited depending on the add-in). They are essentially web applications running within Excel.
  • Cons:  
    • Offline Limitations: While the add-in itself might launch offline, if its logic requires external web services or data, it won't work. For purely local Excel manipulation, it might work offline, but it's not guaranteed without careful design and testing.
    • Deployment & Management: Requires users to acquire and install the add-in from the Office Store or deploy it centrally via Admin controls, which adds an overhead.
    • External Sharing: Partners outside your tenant would also need to acquire and install the add-in.
    • Development Complexity: Building robust Office Add-ins can be more complex than simple Office Scripts.
• Power Automate Desktop (RPA): 
  • Pros: Excellent for automating desktop tasks, even across different applications. Can interact with desktop Excel files.
  • Cons:  
    • Windows Only: Strictly a Windows desktop application. Does not run on Mac, Web, or mobile.
    • Not Embedded: The automation is external to the Excel file itself. Users would need Power Automate Desktop installed and configured.
    • Offline: The "attended" (user-triggered) flows can run offline on a Windows machine, but it's not the same as embedding the automation within the file for seamless cross-platform use.

So summary advise for your issue is: 

• Given your constraints, the hybrid approach of using Office Scripts for online/M365 collaboration and retaining VBA for critical offline desktop scenarios is the most pragmatic path.
• You cannot get a single automation solution that works "offline" on all desired platforms (Web, Windows, Mac, iPad) because the web and mobile versions of Excel fundamentally rely on cloud connectivity for features like Office Scripts. The iPad (iOS) version, in particular, will be limited without an internet connection for any form of advanced automation beyond basic formulas.
• For external partners not in your M365 tenant, the easiest solution for sharing automated spreadsheets will likely remain VBA-enabled .xlsm files (if they have desktop Excel and are comfortable with macros) or using cloud sharing links (like OneDrive/SharePoint links) for the raw data, allowing them to download and work offline. If they need automation, they would need to either build their own, or you would provide them with a VBA version.

We truly appreciate your patience as we look into this matter. Thank you for choosing Microsoft and we value your support. 

If my answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster.   

Thanks and Have a good day!!! 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist.

Hi Rick Win1, 

Thank you for posting on Microsoft Community. 

I sent you a Private Message that provide a workaround. Could you kindly check your Private Message and check if my solution provided help you resolve your issue. 

Here are the steps to open View Private Messages:  

To view private email options, sign in to Microsoft Community> use the email account that posted this thread information to Microsoft Community> > click Profile in the upper-right corner, and then select My Profile.  

Thank you for patience and understanding. 

Sincerely,

HiHi Rick Win1,

If my answer helps, please mark it as an answer, it will definitely help others in the community with similar queries to find a solution to their problem faster. On top of that, it helps me have a good survey and makes me do my best. 

 
 
I appreciate it. Thank you for your support 

Sting-NG - Microsoft Community Support Specialist

Thank you for the very rapid and comprehensive reply. However, it's unfortunate that there isn't a solution from MS for this. I can't maintain two sets of automation scripts/macros for the same spreadsheet(s), one for Web access and one for desktop/offline access. Most of the automations are tied to a button on the screen for the users to click. I can't have two sets of buttons, one for macros (desktop) and one for scripts (online). It would help somewhat to have two sets of automations if the same language was used for both. But, macros use VBA and scripts use Typescript. too bad.

I may explore the Add-ins option, but that is also a lot of work on my part to develop and test.

We will explore other solutions rather than MS Office/Excel.

Thanks for the information.

Rick