Log In
Register
Home
November 2, 2021
Offline
Hi all,
I have a .xlsm file which is stored in a shared Sharepoint library, and accessed by many people. The form controls and associated macros in the file all work fine, except of course if the file is opened in Excel Online. I have changed the settings of the library so that files open in the desktop app by default, which works well.
However, there are still some scenarios (eg if the file is accessed via a shared link, or through Teams) where it will open in Excel Online despite the default setting. I would like to find some way of showing a message to indicate to the user that that has happened, and that they should not use the file as it has opened, but rather open it in the desktop app.
Is there any way to do this? I considered having a textbox with a warning message in it, and a macro that makes that visible (which triggers on the workbook close event) and another macro that makes it hidden (which triggers on the workbook open event), with the idea that anytime someone opens the file, the message will be visible, unless they open it in desktop, which will allow the macro to run, so it will be hidden. But I think that will not work if there are multiple people accessing the file at the same time.
Is there a more elegant way to achieve this? Any thoughts greatly appreciated!
Thanks,
Ryan
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ryan,
To notify users if a file is opened in Excel Online using Office Scripts, you can create an Office Script that checks the platform and displays a message accordingly. Here’s how you can set it up:
Steps to Create an Office Script for Notification
Open Excel Online:
Open your Excel workbook in Excel Online.
Access Office Scripts:
Go to the Automate tab in the Excel ribbon.
Click on New Script to open the Code Editor.
Write the Office Script:
In the Code Editor, write a script to check the platform and display a notification message. Here’s an example script:
typescript
Copy code
function main(workbook: ExcelScript.Workbook) {
// Get the platform information
const platform = workbook.getApplication().getPlatform();
// Check if the platform is Excel Online
if (platform === ExcelScript.PlatformType.OfficeOnline) {
// Display a notification
const sheet = workbook.getActiveWorksheet();
sheet.getRange("A1").setValue("This workbook is opened in Excel Online.");
sheet.getRange("A1").getFormat().setFill({
color: "yellow"
});
sheet.getRange("A1").getFormat().getFont().setBold(true);
} else {
// Optionally, clear the notification if opened in Excel Desktop
const sheet = workbook.getActiveWorksheet();
if (sheet.getRange("A1").getValue() === "This workbook is opened in Excel Online.") {
sheet.getRange("A1").clear();
}
}
}
Explanation of the Script
getApplication().getPlatform(): This method retrieves the platform type (e.g., Excel Desktop, Excel Online).
if (platform === ExcelScript.PlatformType.OfficeOnline): Checks if the workbook is opened in Excel Online.
sheet.getRange("A1").setValue("This workbook is opened in Excel Online.");: Sets a notification message in cell A1 of the active worksheet.
sheet.getRange("A1").getFormat().setFill({ color: "yellow" });: Highlights the cell with a yellow background color.
sheet.getRange("A1").getFormat().getFont().setBold(true);: Sets the font of the notification message to bold.
Clearing the notification: Optionally, you can add a condition to clear the notification message if the workbook is opened in Excel Desktop.
Save and Run the Script:
Click on the Save Script button.
Run the script by clicking on the Run button in the Code Editor.
Testing the Script
Open in Excel Online: Open the workbook in Excel Online and run the script. You should see the notification message displayed in cell A1.
Open in Excel Desktop: Open the workbook in Excel Desktop and run the script. The notification message should not appear, or it should be cleared if previously set.
This Office Script checks if the workbook is opened in Excel Online and displays a notification message accordingly. This solution leverages Office Scripts to automate the detection and notification process.
To have the script run automatically when the file opens in Excel Online, you can use Power Automate along with Office Scripts.
Steps to Create a Power Automate Flow to Run Office Script on File Open
Create the Office Script:
Follow the steps mentioned above to create the Office Script in Excel Online.
Save the script with a name like NotifyIfExcelOnline.
Set Up Power Automate:
Go to Power Automate and sign in with your Microsoft account.
Click on Create and select Automated cloud flow.
Configure the Automated Flow:
Name your flow something like Run Office Script on File Open.
For the trigger, select When a file is created or modified (properties only) from SharePoint or OneDrive (depending on where your file is stored).
Specify the Trigger Settings:
Choose the site address and library name where your Excel file is stored.
Set the condition to monitor the specific file or folder where your workbook is located.
Add an Action to Run the Script:
Click on New step, search for Run script and select the Excel Online (Business) connector.
Configure the Run script action:
Location: OneDrive or SharePoint (depending on where your file is stored).
Document Library: The library where your file is stored.
File: The path to your Excel file.
Script: Select the script you created earlier (e.g., NotifyIfExcelOnline).
Save and Test the Flow:
Save the flow.
Modify the Excel file or upload a new version to trigger the flow.
1 Guest(s)
