How to resolve 'Edit in Excel' issues

 Introduction:

As you know that D365 BC offers Edit in Excel functionality. But manipulating or customizing such a 

standard functionality is difficult as there is not much control available.
Hence, solving issues is also a difficult task.

I will be resolving the issue for this specific issue, however, the debugging steps are similar.

Pre-requisites:

• ODATA V4 Connectivity tool for Webservices
• Microsoft Dynamics 365 Business Central

Books & References:

• https://community.dynamics.com/business/f/dynamics-365-business-central-forum/448226/issue-with-excel-add-in/

Demonstration:

The way Edit in Excel works is that for a given page a Webservice is created adding PageID and Page Name. Hence, there are some Webservice connectivity checks in place to validate.

1. Edit in Excel Custom Action on the page:
If you are adding Edit in Excel Action on the page, please ensure that you are providing appropriate PageName and Filter criteria as Webservice is using these parameters

Refer EditworksheetInExcel procedure parameters

2. Try to connect using Excel OData Connector:

I tried using Excel for this as it is easy.
Open Excel > Data > Get Data > From Other online sources > From OData Feed.

Fill in the URL and Authentication details

If you get results in form of data this means that there is no issue with BC Webservices, hence your issue could be limited to your local Excel.

If you get an error of the type 'Service Exits for Webservice Name xxx'. You will need to go to Webservices in Business Central and Delete the respective web service with the web service name provided in the error.

Note: I cannot reproduce some of the errors, I will update the blog if I face the issue once again.

3. Debug the Base Code Excel: 

You can start with debugging the 6710 ODatautility codeunit and 1482 Edit in Excel Impl. codeunit

Ideally, your error should be unable to create web services, if there are any other errors you can debug a  bit more thoroughly.

4. Try a different user login in Excel Add-in:

Log out from Excel Add-in and login with a different Account. If the different account works, this means that your user have missing permissions.

5. Check if your user is given correct permissions:

You can goto Users > Permission Sets > Permissions and add the following Permissions

• Permission set D365 Excel Export Action
• System permission 6110 Allow Action Export To Excel.

Refer https://docs.microsoft.com/en-us/dynamics365/business-central/about-export-data

Conclusion:

Hopefully, by now these solutions should resolve your issue. If there are any other scenarios feel free to ask your questions on Dynamics Communities.

Thanks to replies on https://community.dynamics.com/business/f/dynamics-365-business-central-forum/448226/issue-with-excel-add-in/ to help me understand the issue in more detail.

Cheers :)