Find broken reference in Excel

I recently had to find a broken reference in an Excel file, where data was copied from one version of the file to another. In the original version there was a data validation rule with a dropdown to a list of allowed base data from another excel sheet.

Drop down to choose from list of value

In a next step the original file was copied and modified to fit new requirements. Meanwhile the original file was used and data was collected in the old file.

After the new file version was approved, the data from the old version was copied (CTRL+C , CTRL+V) to the new file and the old file was removed. However, when opening the new file excel reported a broken link to the old file.

Broken reference after copying data

While this problem can easily be identified in a small excel, this can be a challenge in a huge file with lot of sheets. One way to identify the problem is to unzip the excel file (e.g. using winrar, 7zip, etc). A excel file contains many XML and other files. The worksheets for examle can be found in the xl/worksheets folder

Excel file opend in Winrar

After extracting the worksheet folder to the file system, you may use a tool like Visual Studio Code and open the folder.

Open extracted excel folder in VS Code

Simple by searching for the name of the broken reference you will find the place where to fix the problem

Broken reference in excel

How to create a Word App for Address Lookup in Dynamics Ax

Office Apps provide additional functionality within an Office document like Excel and Word. This is an example how to create an App for Word 2013 which can be used to lookup an Address of a customer in Dynamics Ax.

Dynamics Ax Word App



Word App Architecture

  1. Dynamics Ax will provide a web service which takes the (partial) name of a customer as input parameter, and returns a list of matching customers and their addresses.
  2. However, apps prefer a more lightweight form of communication.Therefore another web service will provide a simple JSON interface.
  3. Finally the Word App will be used to select a partial text within the document eg.”Contoso”, send it to AX and present the result of matching Customer / Addresses.

Dynamics Ax Service

Create a new data contract class and call it ERPCustomer. These will be the result objects for a customer lookup.

class ERPCustomer
    CustName    custName;
    LogisticsAddressing address;

public LogisticsAddressing parmAddress(LogisticsAddressing _address = 
    address = _address;

    return address;

public CustName parmCustName(CustName _custName = custName)
    custName = _custName;

    return custName;

Create a second class for the lookup logic and call it ERPAppHelper and add this static method:

public static List lookup(Name _name)
    CustTable custTable;
    DirPartyTable dirParty;
    List customers = new List(Types::Class);
    ERPCustomer customer;

    _name = strFmt("*%1*",_name);

    while select dirParty
        exists join custTable
        where dirParty.Name like _name && dirParty.RecId == custTable.Party
        customer = new ERPCustomer();


    return customers;

Create a third class for the Lookup Service and call it ERPAppService

AifCollectionTypeAttribute(‘return’, Types::Class, classStr(ERPCustomer))]
public List lookup(Name _name)
    return ERPAppHelper::lookup(_name);

Create a new Service in the AOT, assign the ERPAppService class to the service and add the lookup method. Next create new privilege or use an existing one. Add the service lookup method to the privileges’ entry points. Right click on the service, from the context menu choose “Register”

Dynamics Ax App Service

Finally, to to System Administration > Setup > Services and AIF > Inbound Ports and create a new service. Use net.tcp port and add the lookup method as service operation.

Dynamics Ax App Service AIF

[Optional] Test the Dynamics Ax Service

Open Visual Studio, create a new Console Project and call it TestAifService. Add the Service reference (URI from the Screenshot above) to your project and call it ERPAppService. Call the lookup method and print the results. Use code below, and replace USR, PW, DMN with a valid User, Password, Domain combination in your environment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TestAifService.ERPAppService;

namespace TestAifService
    class Program
        static void Main(string[] args)
            var cred = new System.Net.NetworkCredential("USR","PW","DMN");
            var client = new ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            var result = client.lookup(context, "Contoso");
            foreach (var customer in result)

If you are using the Contoso Demo Data, the result should look like this:

Test Dynamics Ax AIF Service

JSON Frontend Service

In order to host the JSON Frontend Service, you need to install the Application Server Role and HTTP Activation. You will also need the Webserver role installed, including Application Development with .NET and ASP.NET.

In Visual Studio 2013 create a new Website Project and choose a new WCF Service. Use .NET Version 4.5 and select the file system as host. Select a C:\inetpub\wwwroot\appservice as target directory. Name it ERPAppService. This will create a WCF service project in Visual Studio including a working Hello World Service. Add the service reference to the Dynamics Ax net.tcp service to your project and call it DynamicsAx. Your Visual Studio Project should look like this:

Add Dynamics Ax AIF Service as Service Reference

Next, modify the IService1.cs Interface:

namespace ERPAppService
    public interface IService1
        ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name);

Go to the Service1.svc.cs object and implement the lookup method. Replace USR, PW and DOMAIN with a valid user to access Dynamics Ax.

namespace ERPAppService
    public class Service1 : IService1

        public ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name)
            var cred = new System.Net.NetworkCredential("USR","PW","DOMAIN");
            var client = new DynamicsAx.ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new DynamicsAx.CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            return client.lookup(context, name);

Open the web.config and go to the bindings element and add the webHttpBinding element. Within the webHttpBinding element add a binding element and set the security element to mode=Transport. This is required to host the service with SSL.

    <binding name="NetTcpBinding_ERPAppService" />
    <!– SSL !!!  –>
      <security mode="Transport"></security>


Save, Compile and publish the service to the local directory. In the IIS Manager, right click on the Default Website and from the context menu create a new application and name it AppService. Select as directory the directory where your service is deployed.

Host JSON App Service

Go to IIS Manager, select the server running IIS and from the feature pane open certificates. Create a new, self-signed SSL certificate. Next go to the Default Web Site, from the right pane open the Binding Dialog. Select the HTTPS:443 Binding and assign the newly created SSL certificate.

SSL Certificate for JSON App Service

Go to the AppService application and from the feature pane open SSL Settings. Set the “Requires SSL” active.

[Optional] Test the JSON Service

Open a web browser and navigate to the service address. In this example the server name is SRV-Demo within the Domain Demo.Local. Therefore the serivce URL is https://srv-demo.demo.local/AppService/Service1.svc. The result should look like this.

Test JSON App Service

Next call the service lookup method with parameter “Contoso”. The call should look like this https://srv-demo.demo.local/AppService/Service1.svc/lookup/Contoso. The result should like this:

Test JSON App Service

Develop a Word App

In order to create Office Apps, you need at least the professional edition. Community Edition and Express edition do not include the Office / SharePoint App Development projects:

Create new App Project in Visual Studio

Create a new Office App Project. In the wizard choose to develop a side pane app for Word. This will create a new project which already contains an app that can be used to process the selected text within word. The Home.js contains the getDataFromSelection function, which will be used to call the JSON Service.

JSON Call from Word App

Modify the code in the getDataFromSelection() method. Change the URL of the JSON Service to the URL of your service. Use HTTPS and the server name for which the SSL certificate was created. The following code will send the selected text to the JSON service, parse the result display the result as list of links within the word app. By clicking on the link, the text will be pasted into word.

function getDataFromSelection() {

    function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
            var lookupUrl = https://srv-demo.demo.local/AppService/
+ result.value;

            $.getJSON(lookupUrl, function (data) {
                var result = data.lookupResult;
                $.each(result, function (key, val) {
                    var custname = val.parmCustName;
                    var address = val.parmAddress;
                    address = address.replace(/\n/g, ",");

                    $(‘#result_list’).append("<h3>" + custname + "</h3>");
                    $(‘#result_list’).append("<a onclick=
                                              + address + "</a>");


             } else {
                app.showNotification(‘Fehler:’, result.error.message);


Run and test your app Smiley

Stacked Bar chart in Access

Recently a colleague asked how to display a stacked bar chart in Access.

Create a table where one column is used to group data e.g. Year, and additional fields containing values to be stacked e.g Revenue. For example, the columns Revenue by Item Group will be stacked by Year.

Access bar chart data

Next, create a new form and add a diagram object to the form

Add a chart to access

In the diagram wizard select your table with data. Next move the Year and the Revenue fields from the table to the diagram data box.

Add data fields to diagram

Next choose a bar chart. There is no stacked bar chart in the wizard.

Choose bar chart

Drag and drop the year to the axis drop box. Drag and drop the Revenue fields to the data box on the left. By double click on field in the left box you can choose the aggregation function e.g. Count, Sum, None

Assign fields to data and axis

Preview the diagram, it should look like this

Preview bar chart in access wizard

Finish the wizard. A diagram is placed on the form.

Bar chart in access form

Double click the diagram will open the designer view. Here right click on the diagram an open change the diagram type to stacked bar chart

Change chart type to stacked bar chart

Format the diagram as you like. Save your design and open the form

Stacked bar chart in access form

How to create a custom ribbon Addin for Excel 2010

Creating a custom ribbon addin for Excel can be done in a few steps

  1. Develop your VBA code in a new Excel sheet, save it as Excel addin .xlam
  2. Open the addin with Custom UI Editor to create Buttons, Groups, etc. and link it with your VBA code
  3. Add the addin directory to the secure folders in excel
  4. Configure Excel to load your addin at startup

      Here are the required steps in detail

    Start Excel and develop your VBA code as you did before. If you do not see a development tab in excel, activate it via the options menu. File > Options > Ribbon > On the right side check “Development Tools”

    Activate the VBA Development Tab in Excel 2010
    You need to create an event handler to make your functions callable from the UI. Here is an example for a function and an event handler. The event handler foo_eventhandler calls the function foo which displays a message box with “Hello World from Excel”.

Sub foo_eventhandler(control As IRibbonControl)
End Sub

Sub foo()
    MsgBox ("Hello World from Excel")
End Sub

Save your Excel as Excel Addin (.xlam) to your C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins directory. You can save it wherever you want, but the predefined addins directory might be good idea.

Yet the addin does not have any UI elements. You have to define these elements by yourself. Download and install the Custom UI Editor from . Start the editor and open your previous saved .xlam file. The editor shows a blank text field. From the Menu > Insert > Sample XML choose Excel Custom Tab. Use the onAction property at the button to call an eventhandler. Here is an example code to call the foo_eventhandler

<customUI xmlns="">
            <tab id="customTab" label="MyTab" insertAfterMso="TabHome">
                <group id="customGroup" label="MyGroup">
                    <button id="customButton1" label="Say Hello" size="large"
                     onAction="foo_eventhandler" imageMso="HappyFace" />

Start Excel from the program menu. Go to File > Options > Security Center > Settings > Secure Locations > Add Location, and add your addins directory e.g. C:\Users\YOURNAME\AppData\Roaming\Microsoft\Addins as secure directory to load stuff from. Close Excel.

Add the Addins directory to the secure locations in Excel 2010

When you open the .xlam file using Excel, you can already use your addin. However, it is not available when you start Excel from the programs menu. To load the addin when excel starts, go to the development tab  > Add-Ins and check your addin. (The form displays addins from the Appdata\Roaming\Microsoft\Addins directory)

Load your Addin when Excel starts

Now the Addin is loaded when Excel starts. Whenever you change something and the Addin does not appear anymore, go and check your xml UI code. Make sure that all IDs are unique and do not have blanks
(e.g. id=”My Button2” is a bad idea)

Custom Hello World Addin for Excel 2010

More Icons

There are many icons available you can use for your addins. For example download the icon gallery addin from . To change the icon on button modify the imageMso property in your xml file.

<button id="customButton1" label="Say Hello" size="large"
 onAction="foo_eventhandler" imageMso="HappyFace" />

Have fun!

Many icons in Excel 2010