Edit In Excel R2 – Part 2 (out of 2) – the final pieces


It is time to collect the pieces.

The full Edit In Excel R2 solution looks like this

image

Slightly more complicated than the first version – but let me try to explain the pieces

NAVEditInExcel is the COM object, which we use from within NAV. This actually hasn’t changed a lot, the only small change is, that the EditInExcel method now takes a base URL, a company, a page and a view (compared to just a page and a view earlier).
NAVPageDynamicWebReference is the Dynamic Web Reference class and the NAVPageServiceHelper class – described here.
NAVPageFieldInfo contains the NAVFieldInfo class hierarchy for handling type weak pages, described here and used in the Conflict resolution dialog here.
NAVPageMergeForm is the conflict resolution dialog, described here.
NAVTemplate is the actual Excel Add-In which of course now makes use of Dynamic Page References and conflict resolution. It really haven’t changed a lot since the version described here – the major change is the pattern for handling conflict resolution.
EditInExcel Setup is the Client Setup program, this setup program needs to be run on all Clients
EditInExcelDemo is the Server Setup program, this setup program contains the Client Setup msi and places it in the ClientSetup folder for the ComponentHelper (which you can read about here) to autodeploy to clients. This setup also contains the .fob with the EditInExcel objects.

The Client Setup Program

Lets have a closer look at the Client Setup Program

image 

This setup project includes primary output from the COM component and the Excel Add-in and calculated dependencies from that.

Note, that when deploying add-ins you have to add the .vsto and the .manifest files to the setup project yourself, the dependency finder doesn’t discover those. Also note, that all the vsto runtime dll’s etc are excluded from the install list, as we do not want to copy those DLL’s.

Instead I have built in a Launch condition for VSTO runtime 3.0, which is done in 2 steps:

image

First a Search on the Target Machine for component ID {AF68A0DE-C0CD-43E1-96DD-CBD9726079FD} (which is the component installation ID for VSTO 3.0 Runtime) and a launch condition stating that that search needs to return TRUE – else a message will appear with a URL for installing VSTO, which is:

http://www.microsoft.com/downloads/details.aspx?FamilyId=54EB3A5A-0E52-40F9-A2D1-EECD7A092DCB&displaylang=en

One more thing needed in the Client Setup program is to register the COM object. Now the Setup actually has a property you can set, indicating that the object should be registered as COM, but I couldn’t get that to work, so I added custom install actions to the NAVEditInExcel COM object:

image

and the code for the class, which is called by the installer looks like:

[RunInstaller(true)]
public partial class RegasmInstaller : Installer
{
    public RegasmInstaller()
        : base()
    {
    }

    public override void Commit(IDictionary savedState)
    {
        base.Commit(savedState);
        Regasm(false);
    }

    public override void Rollback(IDictionary savedState)
    {
        base.Rollback(savedState);
    }

    public override void Uninstall(IDictionary savedState)
    {
        base.Rollback(savedState);
        Regasm(true);
    }

    private void Regasm(bool unregister)
    {
        string parameters = "/tlb /codebase";
        if (unregister)
            parameters += " /unregister";
        string regasmPath = RuntimeEnvironment.GetRuntimeDirectory() + @"regasm.exe";
        string dllPath = this.GetType().Assembly.Location;
        if (!File.Exists(regasmPath))
            throw new InstallException("Registering assembly failed");
        if (!File.Exists(dllPath))
            return;

        Process process = new Process();
        process.StartInfo.CreateNoWindow = true;
        process.StartInfo.UseShellExecute = false; // Hides console window       
        process.StartInfo.FileName = regasmPath;
        process.StartInfo.Arguments = string.Format("\"{0}\" {1}", dllPath, parameters);
        process.Start();

        // When uninstalling we need to wait for the regasm to finish,
        // before continuing and deleting the file we are unregistering
        if (unregister)
        {
            process.WaitForExit(10000);
            try
            {
                System.IO.File.Delete(System.IO.Path.ChangeExtension(dllPath, "tlb"));
            }
            catch
            {
            }
        }
    }
}

All of the above is captured in the NAVEditInExcelR2.msi – which is the output from the Edit In Excel Setup project. Running this .msi on a client will check pre-requisites, install the right DLL’s, register the COM and you should be good to go.

The Server Setup Program

The Server Setup program actually just needs to place the Client Setup Program in a ClientSetup folder and the .fob (NAV Objects) in the ServerSetup folder.

There are no pre-requisites, no actions no nothing – just copy the files.

After Copying the files on the Server – you need to import the .fob, run the setup code unit and you should be good to go.

Note, that this requires ComponentHelper1.03 (which you can read about here and download here) to run.

Wrapping up…

So, what started out as being a small garage project, ended up being somewhat more complicated and way more powerful. It runs with Office 2007 and Office 2010 (even though you cannot modify the project when Office 2010 beta2 is installed) and even though you might not need the actual Edit In Excel functionality – there are pieces of this that can be used for other purposes.

The source for the entire thing can be downloaded here and the EditInExcel Demo msi can be downloaded here.

 

Happy holidays

 

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Comments (6)

  1. dsteiner says:

    In the Code in the page there is a language id. I’m working with a german installation. With language id 1033 the NAV client crashes after closing Excel. With language id 2055 it works without problems. What is the solution if a customer works with different windows languages on their clients?

  2. Francis.malengier says:

    Hi,

    I just installed the editinexcel using the MSI (also using the componenthelper) and it starts Excel, displays the customer data, but when I click the save button that is added to the riboon, it updates the data in excel, but it doesnt change the data in NAV. So it looks like it does an update instead of save.

    Any Ideas ?

  3. FreddyDK says:

    Hmm – strange.

    Which fields are you trying to change?

  4. denbillie says:

    Hello,

    After having seen this functionality in several NAV2009 demos, I tried installing it myself on my local machine. Btw: thanks for supplying all this information to the community!

    I am using a NAV2009 SP1 installation (belgian localization) and installed all components needed as described above.

    Everything went well, but I now seem to have the same problem as mentioned in the comments above: inserting records works like a charm, but modifying (no matter what field) or deleting records seems impossible. Pressing the "save changes" button results in a reload of the data, and I lose the modifications made in Excel.

    Am I doing something wrong? All suggestions are welcome :-).

  5. Kribo says:

    I downloaded the full instaal and went about installing it according the guidlines on

    community.dynamics.com/…/how-to-58-get-edit-in-excel-r2-up-and-running.aspx

    But ran into a snag… I couldn't get the "NAVEditInExcelR2.msi" to run

    kept on getting the error that I needed ::

    – Framework 3.5

    – Office tools runtime 3.0

    This is all logical because : on my laptop i've got

    – windows 7

    – framework 4.0

    – visual studio 10

    So to get around the anoying dependencies issue

    – I downloaded the source code and converted it to a VS 2010 project

    -an then removed the launch condition " VSTO Runtime 3.0"

    Rebuildt the solution and installed …

    voila

  6. Morten says:

    Also installed this, but when extracting a large dataset (i.e. over 5000 items) into excel, then once ina while i receive the following error message (on danish server):

    System.Reflection.TargetInvocationException: Destinationen for en

    aktivering udløste en undtagelse. —> System.Net.WebException: Handlingen

    fik timeout

     ved System.Web.Services.Protocols.WebClientProtocol.GetWebResponse

    (WebRequest request)

     ved System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse

    (WebRequest request)

     ved System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String

    methodName, Object[] parameters)

     ved Item_Service.ReadMultiple(Item_Filter[] filter, String bookmarkKey,

    Int32 setSize)

     — Slut på staksporing af indre undtagelser —

     ved System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[]

    arguments, SignatureStruct& sig, MethodAttributes methodAttributes,

    RuntimeTypeHandle typeOwner)

     ved System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[]

    arguments, Signature sig, MethodAttributes methodAttributes,

    RuntimeTypeHandle typeOwner)

     ved System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags

    invokeAttr, Binder binder, Object[] parameters, CultureInfo culture,

    Boolean skipVisibilityChecks)

     ved System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags

    invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

     ved FreddyK.NAVPageServiceHelper.ReadMultiple(ArrayList filterArrayList)

     ved NAVTemplate.Sheet1.LoadDataTable()

     ved NAVTemplate.Sheet1.Sheet1_Startup(Object sender, EventArgs e)

     ved Microsoft.Office.Tools.Excel.Worksheet.OnStartup()

     ved NAVTemplate.Sheet1.FinishInitialization()

    ………………………….