Today I would like to talk about how to use Edit in Excel in the browser/web.
The Edit in Excel action is available on most lists. With the Edit in Excel action, you make changes to records in Excel and then publish the changes back to Business Central. More details: Viewing and Editing in Excel From Business Central
For example, choose Edit in Excel, and then you will be asked to save the file.
After opening this file, you can modify the records and publish them to BC.
But there will be two small problems here.
First, the Edit in Excel action requires that the Business Central add-in is installed in Excel.
Microsoft Dynamics Office Add-in:
If your organization prohibits this permission, you cannot use the Edit in Excel feature with local Excel. For example,
Microsoft 365 has been configured to prevent individual acquisition of Office Store Add-ins.
PS: In some cases, your administrator may have set up the add-in to install automatically for you. In this case, you just have to sign in to Business Central in Excel Add-in pane with your user name and password. Otherwise, the New Office Add-in pane opens. To install the add-in, choose Trust this add-in, which will install the add-in directly from the Office Store. If the add-in doesn’t install, either contact your admin or try to install it manually. For more information, see Install the add-in manually for your own use.
Another small problem is that if you use local Excel and are not in the same domain as BC, you may need to switch accounts.
The Open in Excel function does not require this Add-in, and does not require a account, so there is no inconvenience.
Generally, we use the browser to use Business Central, so can we also use the browser to use the Edit in Excel function?
Yes, it’s not difficult, but it requires you to have an OneDrive license. Let’s see more details.
Choose Tell Me icon, enter OneDrive Setup, and then choose the related link.
Or choose Connect your files to the cloud in Assisted Setup page.
In the OneDrive Setup assisted setup guide for setting up OneDrive integration, you can turn on or off features for handling Business Central files in OneDrive. The features are divided between two options:
|Use OneDrive for app features||If you turn on this option, the Open in OneDrive and Share actions are made available on files in Business Central, like files attached to documents or in the report inbox. These actions let users copy, open, and share files in OneDrive. For more information, see Opening and Sharing Business Central Files in OneDrive.|
|Use OneDrive for system features||Turning on this option activates the following features:The Open in Excel and Edit in Excel actions on list pages will automatically copy the Excel file to OneDrive, then open it in Excel Online. For more information, see Viewing and Editing in Excel.Sending a report to an Excel or Word file will automatically copy the file to OneDrive, then open it in the Excel or Word online. For more information, see Saving a report to a file.|
Turn on the second option.
OK, let’s test it again.
Choose Edit in Excel.
At this time, the Excel file will not be downloaded, but will be copied to OneDrive.
If there is a file with the same name, you need to decide whether to replace it, etc., which is the same as in OneDrive.
Then you can use the Edit in Excel function in the browser. Enjoy it😁.
The add-in works with Excel for the web (online) from any device as long as as use a supported browser. It also works with the Excel app for Windows (PC); but not for macOS.
Limits when using Excel for the web
When Edit in Excel is used on list pages for tables with many columns, the resulting workbook may have too many columns for the file to be viewed in Excel for the web. Business Central automatically limits the exported workbook to 100 columns when OneDrive is configured for system features.
Hope this will help.
Thanks for reading.