Quick Enquiry

Edit-in-Excel function not allowing you to import data in Business Central?

March 30th, 2019

Intech Systems

Dynamics NAV, Microsoft Dynamics, Office 365, Troubleshoot

‘Edit-in-excel’ is only available on some pages and disallows importing data in Business central. To use the same functionality of Dynamics Navision for exporting and importing data on any page we have discovered the solution in Business Central.
If Importing the transactional data from Business Central to excel is not available then it may cause an obstacle to many organizations as it

Increases manual work
Time Consumption

1.) How to Export Data

a. Open Purchase Order List page.

b. Click on “Edit in Excel”

If you are not able to find this action on Ribbon, then you have to follow another method which I will explain at the end of this blog.

c. You can see that a separate excel file is created. Open that file & click on “Enable Editing”.
If you are taking data first time then the system will ask you to add “Add-In”. Accept it & then the system will ask for Sign In. Enter your Business Central user id & password.
Now you can see all the data of the table exported in excel. Please note that what data you have filtered on the page that will not applicable in excel. In excel you have to add filters separately by clicking on ‘Filter’.d. In Excel, if you don’t find any required field or want to remove any field then click on “Design”

e. Now Click on “Edit” symbol.

f. You can see Available Field. Select a field that you want to add, and then click on “+Add”

g. You can see that a particular field is removed from the ‘Available Fields’ box. Now click on “Update”.
Then select ‘Yes’ on message.

h. You can see in below screen capture that “Pay to” field is added in excel.
Same way if you want to remove any field then you have to select a particular field from ‘Selected Field’ box and click on the “Remove” button. You can adjust field sequence by clicking on “Up” and “Down” button.

2) How to Import Data

a. Assuming that you have exported data as explained in topic 1, now if you select any data field in excel, the system will show its column name in the right side, and if particular column’s data is not supported to Import than it will be highlighted with red color & non editable symbol will display there.
So simply this kind of data we can not Import from excel.

b. Now I want to update Purchaser Code for two orders, so I have updated the Purchaser Code in particular lines. You can see below that in ‘Excel Add-in’ box, the field name is with normal color & no additional symbol is there.

c. Now Click on Publish after updating Data.

d. You will get below message if Data is successfully imported in Business Central.

3) How to Import or Export Data if “Edit in Excel” button not available.

If you want to import or export Purchase Order lines then you can not find “Edit in Excel” action button on Purchase Order Card page.
For this type of page where you can’t find the option of ‘Edit in Excel’, you have to follow below steps.a. Open New Excel Worksheet.b. Then click on “Insert” & then on “Get Add-ins”

c. Find ‘Microsoft Dynamics Add-in’ and click on “Add”

d. Click on the Setting symbol

e. Select Company & Click on OK

f. Now click on “Design”

g. Click on “Add Table”

h. Click on Drill down button on ‘Select entity data source’ and then select particular data. I have selected ‘Purchase Order line”

i. Click on “Next”

j. Now Add all that field which you want, as explained in Topic 1.

k. Now Click on “Done”

l. Now click on “Refresh”. You will get that all data of Purchase Order Lines. Now you can Import data as per Topic 2. You can add here new lines and Publish it. This function of importing Purchase Order Line is requested by the client if they have so many lines in one order. So by this function, they can easily add new lines & publish it.

One of our Project Lead, in his recent blog, described- ‘How anyone can perform the export/import of data when ‘Edit-in-excel’ is not available.’ Read Here.
If you want to know more about the features of Business Central call us at 079-66112690 or drop in the mail with your query at sales@intech-systems.com