Edit In Excel – Part 4 (out of 4)

If you haven’t read part 3, part 2 (and part 1), you should do so before continuing here.

We have seen how to put code inside Excel, using VSTO and connect to NAV 2009 Web Services. We have seen how to add this to a table inside Excel and how to write data back to NAV through Web Services. We can delete, add and modify records in Excel and we can even do so with both Customers, Vendors and Items. We have added support for NAV filters, error handling and the only thing we are missing to have a very good experience is integrating the whole thing into NAV.

So we better do that now!


Most of the people reading this are probably superior AL coders compared to me. I really only started coding in AL last year and I am still struggling to understand how things works. So, if I do something stupid in AL – just tell me in a comment how to do it smarter – thanks.

Actions in NAV 2009

What we want to do is


Add an action to the menu, which launches Excel, reads the same data as we have in the list place and allows the user to modify that data.

But we need to start in a different area – we need a COM object, which our action can invoke.

Creating a COM object

First of all we will create a COM object, which contains one function.

public void EditInExcel(string page, string view)

I do think there are a number of tutorials that explains how to do this, so I will run over the steps very quickly.

  1. In the same solution as the NAVTemplate, create a new project – type Class Library and name the project NAVEditInExcel
  2. Rename class1.cs to NAVEditInExcel.cs – and say Yes to the question whether you want to rename the class as well.
  3. Select Properties on the project (not the solution)
    1. On the Build tab, set the output path to ..\NAVTemplate\bin\Debug in order to share the output path the the Excel Spreadsheet
    2. On the Build events tab, we need to register the COM object to make it visible to NAV. Add the following Post Build Event: C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm NAVEditInExcel.dll /codebase /tlb
    3. On the Signing tab, check the Sign the Assembly checkbox and select New key in the combo box, name the key and protect it with a password if you fancy.
  4. Open the AssemblyInfo.cs (under Properties in the Solution Explorer)
    1. Add using system; to the using statements
    2. Add [assembly: CLSCompliant(true)] under the line with [assembly: ComVisible(false)].
  5. Open the source for the NavEditInExcel.cs
    1. Add using System.Runtime.InteropServices; to the using statements
    2. Create an Interface and change the class to be:

[Guid("A2C51FC8-671E-4135-AD27-48EDC491E76E"), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVEditInExcel
    void EditInExcel(string page, string view);

[Guid("233E0C7F-2276-4142-929C-D6BA8725D7B4"), ClassInterface(ClassInterfaceType.None)]
public class NAVEditInExcel : INAVEditInExcel
    public void EditInExcel(string page, string view)

        // Code goes here…

Now you should be able to build the COM object and see it inside NAV when adding a variable of type automation.

Adding the Action in NAV

Open up the Classic Client and design the Customer List Place.

Insert an Action on the Customer List Place called Edit In Excel and edit the code for that (btw. the Image Name for the Excel icon is Excel)

In the code for that Action – create a local variable called NAVEditInExcel of type Automation and select the NAVEditInExcel.NAVEditInExcel COM object to use and add the following code:


That’s it on the NAV side, but of course we didn’t make all the code necessary in the COM object yet.

If you try to launch the Action you will be met by the security dialog


Which you of course want to hit always allow to – else you will get this dialog every time you say Edit In Excel.

BTW – If you hit Never allow – you will never be allowed to Edit in Excel – not until you have deleted your PersonalizationStore.xml at least.

Completing the COM object

Having that hooked up we really just need to launch that damn spreadsheet with the right parameters.

We need to add 3 .NET references to the COM object:

  • System.Windows.Forms
  • Microsoft.Office.Interop.Excel
  • Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0

and the following 3 using statements:

using Microsoft.VisualStudio.Tools.Applications;
using System.Windows.Forms;
using System.Reflection;

and last but not least, add the following EditInExcel method:

public void EditInExcel(string page, string view)
        // Copy the original template to a new template using the page name as name!
        string originalTemplate = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "NAVTemplate.xltx");
        if (!System.IO.File.Exists(originalTemplate))
            MessageBox.Show(string.Format("The template: ‘{0}’ cannot be found!", originalTemplate), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        string template = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), page + ".xltx");
        while (System.IO.File.Exists(template))
            catch (System.IO.IOException)
                if (MessageBox.Show(string.Format("The template: ‘{0}’ is locked, cannot open spreadsheet", template), "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) != DialogResult.Retry)
        System.IO.File.Copy(originalTemplate, template);

        // Open the new template and set parameters
        ServerDocument serverDoc = new ServerDocument(template);
        CachedDataHostItem host = serverDoc.CachedData.HostItems[0];

        // Create a new spreadsheet based on the new template
        Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        excelApp.Visible = true;

        // Erase template
    catch (Exception e)
        System.Windows.Forms.MessageBox.Show(e.Message, "Critical error", MessageBoxButtons.OK, MessageBoxIcon.Error);

This method really does 4 things:

  1. Copy the NAVTemplate.xltx to a new template called Customer.xltx (that is if the page name is customer) which is a temporary template
  2. Open the template as a ServerDocument and set the parameters
  3. Ask Excel to create a new spreadsheet based on this template
  4. Erase the template

That was easy!

Oh – there is one things I forgot to say, you need to specify in the Excel Spreadsheet that the page and view variables are cached data (meaning their value are saved with Excel) – this is done by adding an attribute to the variables:

public string page;

public string view;

Having done this, you can open the spreadsheet as a Serverdocument, get and set the value of these parameters and save the document again, pretty sweet way of communicating parameters to Excel or Word – and this will definitely come in handy later.

Adding the action other pages

Having verified that we can edit customers in Excel we can now add the same action as above to the Vendor and the Item List Places.

You can either follow the same steps as above – or you can copy the action and paste it on the other List Places.

Note that you cannot build the Visual Studio solution while you have NAV 2009 open. When NAV loads the COM object, it keeps a reference to it until you close NAV.

Last but not least – this should work from the classic client as well – if you want to add the functionality there – I haven’t tried it.

That’s it folks

That completes the Edit In Excel in Part 1 through 4

As always, there is absolutely no warranty that this code works for the purpose you need it to, but these posts show how to do some things and feel free to use pieces of this or use it as a base to build your own solution using Excel – the code is free – a referral to my blog is always a good way of acknowledgement.

I hope you can make it work, that it is useful and you can download the final solution here: http://www.freddy.dk/NAVTemplate_Final.zip


Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Comments (19)

  1. waldo1001 says:

    Hi Freddy.  Thanks for your effort in explaining this.  The functionality looks simple, but it really isn’t when diving into it.  Or may be it’s just me ;-).

    Anyway, one thing I came accross.  I changed the language settings on my VPC to Dutch(Belgium).  This causes an issue when using the excel template solution from within the RTC.  You can either install the language packs for office (in my case Dutch (Belgium)) or switch back to the original language (English (US)) to solve it… .

    The way to see the error is to remove the try..catch in the NAVEditInExcel class.

  2. waldo1001 says:

    I added one extra line in the OnPush of the Action to make it like this:




    This way, you’re able to push "Edit in Excel" more than one without leaving the customer list.

  3. btronholm@noray.com says:

    Hi, I’ve been trying some alternative ways w/o web services, using standard table 370 Excel Buffer. It works fine in 2009 Classic Client, but I get an error "Excel not found" (when running CREATE(XlApp,TRUE,TRUE). ¿Any hint?

  4. FreddyDK says:

    I haven’t tried playing around with that – you probably should post a question like this on Mibuso

  5. btronholm@noray.com says:

    Actually I was troubled and mibuso was the answer. This might be useful for other people:



  6. rtaranti says:

    Greetings Freddie.  I know deployment is not part of your dicussison, but I was wondering if anyone ws having problems using VS 2008 on a Vista machine to package the solution up and deploy it to other PC’s?  I am getting the message, "WARNING: Unable to create registration information for file named ‘NAVEditInExcel.tlb’" when building the deplyment project.  It seems to complete without any issue, and it deploys fine, but the com is not visible in NAV globals on the deployed to machine (PC).  I suspect the message might be the reason?  If anyone knows the reason why the com is not visible please let me know.

    Best Regards,


  7. FreddyDK says:

    You need to run the regasm command on any machine wanting to use the COM automation object.

    You can see the command as post build event on the NAVEditInExcel project.

    This is always required to make a COM object visible on a machine.

  8. rtaranti says:

    Interesting, and yes thats works :).  I never had to this problem when compiling my projects on an XP box with VS 2005 or 2008…strange.  

    Do recommend we create a deployment action to make this call in our msi?  Would that work since the VS command prompt which intalls regasm, I believe, would not be on my client machines?  

    I have scene quite a few posts out there on this, but know has a clean solution.  Bummer.  Well, thank you once again.  

    Best Regards,


  9. FreddyDK says:

    regasm is part of .net – not VS – run

    C:WindowsMicrosoft.NETFrameworkv2.0.50727regasm <dll> /codebase /tlb

    in your msi.

  10. rtaranti says:

    Lets just say I am feeling sheepish from my last post.  Thanks for taking it easy one me, I should have known that. đŸ™‚



  11. frzJalali says:

    Is there any solution if I like to show some data which are master-detail in excel from navision ?

    because most of time my data are master-details and I don’t like to show them in two diffrent form ( or excel sheet).

  12. FreddyDK says:

    I don’t know of any samples like that – sorry.


  13. AngelaV says:

    Hi, Using Edit In Excel, we are trying to add new records to NAV table called 'Purchase Price'. New records are created in Dynamics NAV but some fields such as ‘starting date’ and ‘Unit Cost’ have no data even if it was entered in Excel. Those ‘starting date’ and ‘unit cost’ fields have NAV default values: null date and 0.00. Any ideas? Thanks,

  14. Neil says:

    Hi Freddy

    Really good work.

    I was wondering you are trying to use only Master tables in you example so far. Is there any restriction to use transaction tables like General Journal. e.g. to create journal from Excel itself. I used to write Automation based function to import from excel but Edit in Excel would be great feature.


  15. Thiyagu says:


           good post

  16. Thiyagu says:


            A very good blog freddy.  This blog suits for single company and  data displays as per the company.  How to implement this same functionality for multi companies?  Pl reply.


  17. FreddyDK says:

    If you have data to share between companies – you would have to mark it with the property Data Per Company = No on the table – then it would work – and this would be the right direction to take.

    If you want to change Edit In Excel to work on multiple companies – you can of course do that (although I wouldn't recommend it), but Web Services always uses the company as point of entry – so you will have to read data from each company (one after the other) – then mark each record with a pointer to which company it came from and lastly – write the changes to the right company.

    Also You would need to be able to specify which company to use when creating records. I think this would be the wrong direction to take.

  18. Tulasi says:

    Hello freddy,

    I am related to NAV not to Visual Studio. I got the solution very cool while i run it with VS 2008 and office2007.

    But while trying to open this solution Visual Studio 2008 with office 2010 installed pc I got version compatability issue. How should i solve it???

  19. Dan Eckhoff says:

    Greetings Freddy,

    We've utilized your solution several times over the past couple years and I've really appreciated the tool.  We currently have a NAV 2009 R2 client getting an error in the item table:

    Requested value '_x002C_Quantity_on_Hand' was not found.

    Vendor and Customer work as expected.  Any Ideas?

    Thank you.