Bind to regions in a document or spreadsheet

Use a binding when your add-in needs reliable access to a specific region of an Excel workbook or Word document. A binding associates the region with a unique ID, so your add-in can return to it after the user changes their selection or reopens the document.

With a binding, your add-in can:

• Access common data structures, such as tables, ranges, or text.
• Read and write data without requiring the user to select the region first.
• Monitor data and selection changes within the bound region.
• Maintain the relationship across sessions because the binding is saved with the document.

Choose the right binding type

Office supports three binding types. Choose a type based on the region and the data that your add-in needs to read or write.

Specify the type with the bindingType parameter when you create a binding by using addFromSelectionAsync, addFromPromptAsync, or addFromNamedItemAsync.

Text binding

A text binding represents a document region as text.

In Word, most contiguous selections work. In Excel, only single cell selections can use text binding. Excel supports only plain text, while Word supports three formats: plain text, HTML, and Open XML for Office.

Matrix binding

A matrix binding represents a fixed region of tabular data without headers.

Read or write matrix data as a two-dimensional Array (an array of arrays in JavaScript). For example, two rows of string values in two columns look like [['a', 'b'], ['c', 'd']], and a single column of three rows looks like [['a'], ['b'], ['c']].

In Excel, any contiguous selection of cells works for matrix binding. In Word, only tables support matrix binding.

Table binding

A table binding represents a table with headers.

Data in a table binding is read or written as a TableData object. The TableData object exposes data through the headers and rows properties.

Any Excel or Word table can be the basis for a table binding. After you establish a table binding, new rows or columns that users add to the table are automatically included in the binding.

After creating a binding with one of the three "addFrom" methods, you can work with the binding's data and properties using the corresponding object: MatrixBinding, TableBinding, or TextBinding. All three objects inherit the getDataAsync and setDataAsync methods from the Binding object for interacting with bound data.

Create a binding from the current selection

The following example adds a text binding called myBinding to the current selection using the addFromSelectionAsync method.

Office.context.document.bindings.addFromSelectionAsync(Office.BindingType.Text, { id: 'myBinding' }, function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write('Action failed. Error: ' + asyncResult.error.message);
    } else {
        write('Added new binding with type: ' + asyncResult.value.type + ' and id: ' + asyncResult.value.id);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

In this example, the binding type is text, so a TextBinding is created for the selection. Different binding types expose different data and operations. Office.BindingType is an enumeration of available binding types.

The second optional parameter specifies the ID of the new binding. If you don't specify an ID, one is generated automatically.

The anonymous function passed as the final callback parameter runs when the binding creation is complete. The function receives a single parameter, asyncResult, which provides access to an AsyncResult object with the call's status. The AsyncResult.value property contains a reference to a Binding object of the specified type for the newly created binding. You can use this Binding object to get and set data.

Create a binding from a prompt

The following function adds a text binding called myBinding using the addFromPromptAsync method. This method lets users specify the range for the binding using the application's built-in range selection prompt.

function bindFromPrompt() {
    Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Text, { id: 'myBinding' }, function (asyncResult) {
        if (asyncResult.status == Office.AsyncResultStatus.Failed) {
            write('Action failed. Error: ' + asyncResult.error.message);
        } else {
            write('Added new binding with type: ' + asyncResult.value.type + ' and id: ' + asyncResult.value.id);
        }
    });
}

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

In this example, the binding type is text, so a TextBinding is created for the user's selection in the prompt.

The second parameter contains the ID of the new binding. If you don't specify an ID, one is generated automatically.

The anonymous function passed as the third callback parameter runs when the binding creation is complete. When the callback function runs, the AsyncResult object contains the call's status and the newly created binding.

The following screenshot shows the built-in range selection prompt in Excel.

Add a binding to a named item

The following function adds a binding to the existing myRange named item as a "matrix" binding using the addFromNamedItemAsync method and assigns the binding's id as "myMatrix".

function bindNamedItem() {
    Office.context.document.bindings.addFromNamedItemAsync("myRange", "matrix", {id:'myMatrix'}, function (result) {
        if (result.status == 'succeeded'){
            write('Added new binding with type: ' + result.value.type + ' and id: ' + result.value.id);
            }
        else
            write('Error: ' + result.error.message);
    });
}

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

For Excel, the itemName parameter of addFromNamedItemAsync refers to an existing named range, a range specified with A1 reference style ("A1:A3"), or a table. By default, Excel assigns the names "Table1" for the first table, "Table2" for the second table, and so on. To assign a meaningful name to a table in the Excel UI, use the Table Name property on the Table Tools | Design tab.

The following function creates a binding in Excel to the first three cells in column A ("A1:A3"), assigns the ID "MyCities", and then writes three city names to that binding.

 function bindingFromA1Range() {
    Office.context.document.bindings.addFromNamedItemAsync("A1:A3", "matrix", { id: "MyCities" },
        function (asyncResult) {
            if (asyncResult.status == "failed") {
                write('Error: ' + asyncResult.error.message);
            } else {
                // Write data to the new binding.
                Office.select("bindings#MyCities").setDataAsync([['Berlin'], ['Munich'], ['Duisburg']], { coercionType: "matrix" },
                    function (asyncResult) {
                        if (asyncResult.status == "failed") {
                            write('Error: ' + asyncResult.error.message);
                        }
                    });
            }
        });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

For Word, the itemName parameter of addFromNamedItemAsync refers to the Title property of a Rich Text content control. (You can't bind to content controls other than the Rich Text content control.)

By default, a content control has no Title value assigned. To assign a meaningful name in the Word UI, after inserting a Rich Text content control from the Controls group on the Developer tab, use the Properties command in the Controls group to display the Content Control Properties dialog. Then set the Title property of the content control to the name you want to reference from your code.

The following function creates a text binding in Word to a rich text content control named "FirstName", assigns the id "firstName", and then displays that information.

function bindContentControl() {
    Office.context.document.bindings.addFromNamedItemAsync('FirstName',
        Office.BindingType.Text, {id:'firstName'},
        function (result) {
            if (result.status === Office.AsyncResultStatus.Succeeded) {
                write('Control bound. Binding.id: '
                    + result.value.id + ' Binding.type: ' + result.value.type);
            } else {
                write('Error:', result.error.message);
            }
    });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

Get all bindings

The following example gets all bindings in a document using the getAllAsync method.

Office.context.document.bindings.getAllAsync(function (asyncResult) {
    let bindingString = '';
    for (let i in asyncResult.value) {
        bindingString += asyncResult.value[i].id + '\n';
    }
    write('Existing bindings: ' + bindingString);
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

The anonymous function passed as the callback parameter runs when the operation is complete. The function is called with a single parameter, asyncResult, which contains an array of the bindings in the document. The array is iterated to build a string that contains the IDs of the bindings. The string is then displayed in a message box.

Get a binding by ID using getByIdAsync

The following example uses the getByIdAsync method to get a binding in a document by specifying its ID. This example assumes that a binding named 'myBinding' was added to the document using one of the methods described earlier in this article.

Office.context.document.bindings.getByIdAsync('myBinding', function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write('Action failed. Error: ' + asyncResult.error.message);
    }
    else {
        write('Retrieved binding with type: ' + asyncResult.value.type + ' and id: ' + asyncResult.value.id);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

In this example, the first id parameter is the ID of the binding to retrieve.

The anonymous function passed as the second callback parameter runs when the operation is completed. The function is called with a single parameter, asyncResult, which contains the call's status and the binding with the ID "myBinding".

Get a binding by ID using Office.select

The following example uses the Office.select function to get a Binding object promise in a document by specifying its ID in a selector string. It then calls the getDataAsync method to get data from the specified binding. This example assumes that a binding named 'myBinding' was added to the document using one of the methods described earlier in this article.

Office.select("bindings#myBinding", function onError(){}).getDataAsync(function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write('Action failed. Error: ' + asyncResult.error.message);
    } else {
        write(asyncResult.value);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

If the select function promise successfully returns a Binding object, that object exposes only the following four methods: getDataAsync, setDataAsync, addHandlerAsync, and removeHandlerAsync. If the promise can't return a Binding object, the onError callback can be used to access an asyncResult.error object to get more information. If you need to call a member of the Binding object other than the four methods exposed by the Binding object promise returned by the select function, instead use the getByIdAsync method by using the Document.bindings property and getByIdAsync method to retrieve the Binding object.

Release a binding by ID

The following example uses the releaseByIdAsync method to release a binding in a document by specifying its ID.

Office.context.document.bindings.releaseByIdAsync('myBinding', function (asyncResult) {
    write('Released myBinding!');
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

In this example, the first id parameter is the ID of the binding to release.

The anonymous function passed as the second parameter is a callback that runs when the operation is complete. The function is called with a single parameter, asyncResult, which contains the call's status.

Read data from a binding

The following example uses the getDataAsync method to get data from an existing binding.

myBinding.getDataAsync(function (asyncResult) {
    if (asyncResult.status == Office.AsyncResultStatus.Failed) {
        write('Action failed. Error: ' + asyncResult.error.message);
    } else {
        write(asyncResult.value);
    }
});

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

myBinding is a variable that contains an existing text binding in the document. Alternatively, you could use Office.select to access the binding by its ID, and start your call to the getDataAsync method, like this:

Office.select("bindings#myBindingID").getDataAsync

The anonymous function passed into the method is a callback that runs when the operation is complete. The AsyncResult.value property contains the data within myBinding. The type of the value depends on the binding type. The binding in this example is a text binding, so the value will contain a string. For additional examples of working with matrix and table bindings, see the getDataAsync method topic.

Write data to a binding

The following example uses the setDataAsync method to set data in an existing binding.

myBinding.setDataAsync('Hello World!', function (asyncResult) { });

myBinding is a variable that contains an existing text binding in the document.

In this example, the first parameter is the value to set on myBinding. Because this is a text binding, the value is a string. Different binding types accept different types of data.

The anonymous function passed into the method is a callback that runs when the operation is complete. The function is called with a single parameter, asyncResult, which contains the result's status.

Detect changes to data or selection in a binding

The following function attaches an event handler to the DataChanged event of a binding with an ID of "MyBinding".

function addHandler() {
Office.select("bindings#MyBinding").addHandlerAsync(
    Office.EventType.BindingDataChanged, dataChanged);
}
function dataChanged(eventArgs) {
    write('Bound data changed in binding: ' + eventArgs.binding.id);
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message;
}

The myBinding is a variable that contains an existing text binding in the document.

The first eventType parameter of addHandlerAsync specifies the name of the event to subscribe to. Office.EventType is an enumeration of available event type values. Office.EventType.BindingDataChanged evaluates to the string "bindingDataChanged".

The dataChanged function passed as the second handler parameter is an event handler that runs when the data in the binding is changed. The function is called with a single parameter, eventArgs, which contains a reference to the binding. This binding can be used to retrieve the updated data.

Similarly, you can detect when a user changes selection in a binding by attaching an event handler to the SelectionChanged event of a binding. To do that, specify the eventType parameter of addHandlerAsync as Office.EventType.BindingSelectionChanged or "bindingSelectionChanged".

You can add multiple event handlers for a given event by calling addHandlerAsync again and passing in an additional event handler function for the handler parameter. The name of each event handler function must be unique.

Remove an event handler

To remove an event handler for an event, call removeHandlerAsync passing in the event type as the first eventType parameter, and the name of the event handler function to remove as the second handler parameter. For example, the following function removes the dataChanged event handler function added in the previous section's example.

function removeEventHandlerFromBinding() {
    Office.select("bindings#MyBinding").removeHandlerAsync(
        Office.EventType.BindingDataChanged, {handler:dataChanged});
}

See also

• Understanding the Office JavaScript API
• Asynchronous programming in Office Add-ins
• Read and write data to the active selection in a document or spreadsheet