locked
Excel API - call to binding.getRange() on a deleted binding range corrupts the workbook file and needs repair when re-opened RRS feed

  • Question

  • If binding range is deleted by deleting all columns or rows of the range and then binding.getRange() is called it corrupts the workbook file and when re-opened the workbook needs repair (which will delete all the corrupt binding AND all other binding ranges in the same sheet that was corrupted).

    This might not seem like a big deal at first but, in addition to this occurring when the user is in session with your add-in loaded, a lot can happen when your add-in is not loaded and when it is re-loaded you have to deal with these situations if you have binding ranges that were deleted.

    Full example code below...just paste into new Excel 2016 Task Pane app.

    Debug info: each call to binding.getRange() on a deleted binding adds an invalid binding reference for the webExtension (add-in)  in the sheet(1).xml of the workbook file.  It is invalid because the appRef="<id>" is not a valid binding id for that webExtension in webextenstion(1).xml.

    <x15:webExtension appRef="{46372DB8-AE2B-48F9-A1B7-427A6BDC773C}">

    <xm:f>Sheet1!$A</xm:f>

    </x15:webExtension>

    <x15:webExtension appRef="{06387EC2-A71F-4999-AB1D-FA3A443D089F}">

    <xm:f>Sheet1!$A</xm:f>

    </x15:webExtension>

    <x15:webExtension appRef="{E5AA4809-BC2E-40B6-9F28-6B32D07C037E}">

    <xm:f>Sheet1!$A</xm:f>

    </x15:webExtension>

    Full Code

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8" />
        <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
       <title></title>
       <script src="../../Scripts/jquery-1.9.1.js" type="text/javascript"></script>
     
       <link href="../../Content/Office.css" rel="stylesheet" type="text/css" />
     
       <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
     
     
       <script>
          (function () {
     
             "use strict";
     
             var msg = "";
     
             // The initialize function must be run each time a new page is loaded
             Office.initialize = function (reason) {
                $(document).ready(function () {
                   $('#get-all-bindings').click(getAllBindings);
     
                   $('#bind-data-from-selection').click(bindDataFromSelection);
     
                   $('#get-data-from-binding').click(getBindingDataById);
     
                   $('#get-range-address-from-binding').click(getRangeAddressFromBinding);
     
                   $('#clear-msg').click(clearMsg);
                });
             };
            
             function getRangeAddressFromBinding() {
                var bindingId = $("#binding-id-to-get").val();
     
                Excel.run(function (ctx) {
                   var binding = ctx.workbook.bindings.getItem(bindingId);
                   var range = binding.getRange();
     
                   range.load('address');
     
                   return ctx.sync().then(function () {
                      write("Range address is " + range.address);
                   });
                })
                .catch(function (error) {
                   console.log("Error: " + error);
                   if (error instanceof OfficeExtension.Error) {
                      write("Debug info: " + JSON.stringify(error.stack) + "*** CORRUPTS THE WORKBOOK ON NEXT OPEN");
                      console.log("Debug info: " + JSON.stringify(error.debugInfo));
                   }
                });
             }
     
     
             function bindDataFromSelection() {
                Office.context.document.bindings.addFromSelectionAsync(Office.BindingType.Matrix, function (asyncResult) {
                   if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                      write('Action failed. Error: ' + asyncResult.error.message);
                   }
                   else {
                      write('New ' + asyncResult.value.type + ' binding, id = ' + asyncResult.value.id);
     
                      $("#binding-id-to-get").val(asyncResult.value.id);
                   }
                });
             }
     
     
     
     
     
             function getAllBindings() {
                Office.context.document.bindings.getAllAsync(function (result) {
                   if (result.status === Office.AsyncResultStatus.Succeeded) {
                      write("bindings.getAllAsync() returned: ");
                      var cnt = 1;
                      var bindingId = "";
                      result.value.forEach(function (binding) {
                         write("binding " + cnt + ": " + binding.id);
                         bindingId = binding.id;
                      });
     
                      $("#binding-id-to-get").val(bindingId);
                   }
                   else {
                      write("bindings.getAllAsync() failed ");
                   }
                });
             }
     
             function getBindingDataById() {
     
                var bindingId = $("#binding-id-to-get").val();
     
                var options = {
                   coercionType: "matrix",    // Office.CoercionType.Matrix,
                   valueFormat: "unformatted"  //Office.ValueFormat 
                };
     
                Office.select("bindings#" + bindingId,
     
                   function onError(result) {
                      write("Failed to get binding data for = " + bindingId + "; Error=" + result.error.message);
     
                   }
                   ).getDataAsync(options, function (result) {
                      if (result.status === Office.AsyncResultStatus.Succeeded) {
                         write("Binding data returned = " + result.value);
                      }
                      else {
                         write("Failed to get binding data for = " + bindingId);
                      }
                   });
             }
     
     
             function write(message) {
                msg += message + "<br/><br/>";
                $('#message').html(msg);
             }
     
             function clearMsg() {
                msg = "";
                $('#message').html("");
             }
     
          })();
     
       </script>
    </head>
    <body>
       <div id="content-header">
          <div class="padding">
             <h1>getRange() corrupts</h1>
          </div>
       </div>
       <div id="content-main">
          <div class="padding">
     
             repo:
             <br/>
             <p>Create a range and "bind from selection"</p>
             <p>call Get range address from binding - works fine </p>
             <p>delete all columns of the binding range </p>
             <p>call Get range address from binding - ERROR</p>
             <p>save sheet and re-open workbook which is corrupt and needs repair</p>
     
             <br />
             <button id="bind-data-from-selection">Bind data from selection</button>
     
             <br />
             Binding ID:
             <input id="binding-id-to-get" type="text" />
     
             <br />
             <button id="get-range-address-from-binding">Get range address from binding</button>
     
     
             <hr/>
     
             <br />
             <button id="get-all-bindings">Get all bindings</button>
     
     
             <br />
             <button id="get-data-from-binding">Get data for binding</button>
     
     
             <br />
             <button id="clear-msg">Clear Message</button>
     
             <br/><br/>
             Status
             <div id="message" style="background#e0e0e0padding15px;"></div>
          </div>
       </div>
    </body>
    </html>
    



    • Edited by Jim - Strive Wednesday, March 9, 2016 4:30 PM more descrip
    Wednesday, March 9, 2016 4:21 PM

Answers

  • Hi Jim,

    I made a test with your description, and I could reproduce your issue with your steps. To resolve this issue, I suggest you check the binding object whether it has been removed. As my test, it seems we could not check it directly, so, I suggest you check the rowCount property. Here is a simple code:

    function getRangeAddressFromBinding() {
            var bindingId = $("#binding-id-to-get").val();
            
            var rowcount; //count of row
            var rangeAddress;
            var bind = Office.context.document.bindings.getByIdAsync(bindingId, function (asyncResult) {
                rowcount = asyncResult.value.rowCount;
                if (rowcount == null)
                { }
                else if (rowcount == 0) {
                    write("bind has been removed ");                
                }
                else {
                    Excel.run(function (ctx) {
                        var binding = ctx.workbook.bindings.getItem(bindingId);
                        var range = binding.getRange();
    
                        range.load('address');
                        return ctx.sync().then(function () {
                            write("Range address is " + range.address);
                        })
                    }).catch(function (error) {
                        console.log("Error: " + error);
                        if (error instanceof OfficeExtension.Error) {
                            write("Debug info: " + JSON.stringify(error.stack) + "*** CORRUPTS THE WORKBOOK ON NEXT OPEN");
                            console.log("Debug info: " + JSON.stringify(error.debugInfo));
                        };
                    });
                }           
            })
            
        }
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jim - Strive Friday, March 11, 2016 2:55 PM
    Friday, March 11, 2016 2:33 AM

All replies

  • Hi Jim,

    I made a test with your description, and I could reproduce your issue with your steps. To resolve this issue, I suggest you check the binding object whether it has been removed. As my test, it seems we could not check it directly, so, I suggest you check the rowCount property. Here is a simple code:

    function getRangeAddressFromBinding() {
            var bindingId = $("#binding-id-to-get").val();
            
            var rowcount; //count of row
            var rangeAddress;
            var bind = Office.context.document.bindings.getByIdAsync(bindingId, function (asyncResult) {
                rowcount = asyncResult.value.rowCount;
                if (rowcount == null)
                { }
                else if (rowcount == 0) {
                    write("bind has been removed ");                
                }
                else {
                    Excel.run(function (ctx) {
                        var binding = ctx.workbook.bindings.getItem(bindingId);
                        var range = binding.getRange();
    
                        range.load('address');
                        return ctx.sync().then(function () {
                            write("Range address is " + range.address);
                        })
                    }).catch(function (error) {
                        console.log("Error: " + error);
                        if (error instanceof OfficeExtension.Error) {
                            write("Debug info: " + JSON.stringify(error.stack) + "*** CORRUPTS THE WORKBOOK ON NEXT OPEN");
                            console.log("Debug info: " + JSON.stringify(error.debugInfo));
                        };
                    });
                }           
            })
            
        }
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jim - Strive Friday, March 11, 2016 2:55 PM
    Friday, March 11, 2016 2:33 AM
  • Thanks Edward.

    That's a good workaround, I'll mark it as the answer. I ended up doing something similar which just gets rid of orphaned bindings.  I am not quite sure why Excel leaves these around.

    Don't you think this is still a bug that should be fixed?  Corrupting workbooks is not a good thing.

    Jim

    Friday, March 11, 2016 2:55 PM
  • Hi Jim,

    I agree with your, corrupting workbooks is not a good thing, if you want this is changed in the future, I suggest you submit a feedback in the link below:

    http://officespdev.uservoice.com

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 14, 2016 1:47 AM
  • Thanks Edward.

    I have posted a suggestion on user voice.  Others landing here...please vote it up!.

    https://officespdev.uservoice.com/forums/224641-general/suggestions/12923559-excel-api-call-to-binding-getrange-on-a-delete

    Jim

    Monday, March 14, 2016 4:07 AM