none
How to set an Excel Table name for a table and then get the table associated with a Table Binding? RRS feed

  • Question

  • Hi,

    The below User Voice is marked as completed but I cannot figure out how it implements #1 and #2 as stated in the user voice.

    https://officespdev.uservoice.com/forums/224641-general/suggestions/5391435-add-additional-features-to-the-office-javascript-a

    "1. The ability to name an Excel Table that is assigned a Table Binding."

    "2. The ability to read an Excel Table name for a table that is assigned a Table Binding"

    My requirements are similar to as #1 and #2.  I need to name a table that is created by my code and  I need to get the table name of the table associated with a (Table) binding id that I created.  This allows me to know if it is the table that my code created.  Excel randomly names tables "table1, table2,." and unfortunately it reuses names once they are deleted.  So if my add-in creates "Table2" and then the user closes my add-in, deletes the table, creates a new "Table2", and reloads my add-in...I could unintentionally do something bad to a table that I did not create.

    Is there any sample code that implements #1 and #2 that support the User Voice being completed?

    Thanks,

    Jim

    Monday, May 2, 2016 8:49 PM

Answers

  • Hi Jim,

    Thanks for the detail information for this issue.

    At present, we are not able to assign the table name when we create it via the Office.js. You can try to submit the feedback from here, if you want the Office add-in to support this feature.

    >>One other question I had is related to the renaming code below which works great:

                var table = binding.getTable();
                table.load('name');
                return ctx.sync().then(function () {
                    table.name = "customTableName";
                });

    Why don't you have to call ctx.sync() again after setting the new table name?  It happens immediately without calling sync() and this seems contrary to the way the API is supposed to work.

    It seems that the table's name could save it automatically when we using the sync before. To make the code more reliable, we can change the name and using sync() method to save the changing. Here is the code for your reference:

      function renameTableName() {
            Excel.run(function (ctx) {
                var binding = ctx.workbook.bindings.getItem("tableBinding1");
    
                var table = binding.getTable();
                table.load('name');
                table.name = "customTableName";
                return ctx.sync().then(function () {
                 
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
    Regards & Fei


    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 Thursday, May 5, 2016 1:20 PM
    Thursday, May 5, 2016 9:41 AM
    Moderator

All replies

  • Hi Jim,

    Here is the code sample for your reference:

     Office.initialize = function (reason) {
            $(document).ready(function () {
                app.initialize();
    
                $('#get-data-from-selection').click(getDataFromSelection);
                $('#btnCreateTableBinding').click(createTableBinding)
                $('#btnPrintTableName').click(printTableName);
                $('#btnRenameTableName').click(renameTableName);
            });
        };
    
        function renameTableName() {
            Excel.run(function (ctx) {
                var binding = ctx.workbook.bindings.getItem("tableBinding1");
    
                var table = binding.getTable();
                table.load('name');
                return ctx.sync().then(function () {
                    table.name = "customTableName";
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
        function printTableName() {
    
            Excel.run(function (ctx) { 
                var binding = ctx.workbook.bindings.getItem("tableBinding1");
    
                var table = binding.getTable();
                table.load('name');
                return ctx.sync().then(function () {
                    app.showNotification(table.name);
                });
            }).catch(function(error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
          
        }
    
        function createTableBinding() {
            Office.context.document.bindings.addFromNamedItemAsync("table1", Office.BindingType.Table, { id: "tableBinding1" }, function (asyncResult) {
                app.showNotification(asyncResult.status);
            })
        }

    HTML:

     <button id="btnCreateTableBinding">Create Table Binding</button>
    <button id="btnPrintTableName">Print Table Name</button>
    <button id="btnRenameTableName">Rename Table Name</button>

    Here are the steps to test this sample:
    1. Run the Office add-in project 

    2. Create a table in Excel named "table1"

    3. Click the button "Create Table Binding" to create table binding object

    4. Click the button "Print Table Name" to print the table name through the table binding object

    5. Click the button "Rename Table Name" to rename the table

    6. Click the button "Print Table Name" to print the new table name

    Please feel free to let me know if you still have the problem.

    Regards & Fei


    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.

    Wednesday, May 4, 2016 8:17 AM
    Moderator
  • Thanks Fei,

    I can use your code sample to solve my case. However, it still assumes that you know the table name to start with since in your step #2 I am creating a table manually and naming it "table1". In my case I am creating that table via code so I don't know that it is going to be named "table1".  That's what I wanted to be able to do...create a table and give it a name when I create it.

    The way I am getting it to work in code is:

    1) select target range

    2) document.setSelectedDataAsync( ...coercionType: table ...)

    3) document.bindings.addFromSelectionAsync(Office.BindingType.Table...  giving it a binding id

    4) use your rename code above

    At no point in the above do I know the original table name. (I had code to search for it but it was fragile.) What is connecting the binding to the table is that the binding is created on the same selection as the table. I can't use your bindings.addFromNamedItemAsync(...) because I don't know the name of the table that I added.

    In any case your code was very helpful in finding a solution.

    One other question I had is related to the renaming code below which works great:

                var table = binding.getTable();
                table
    .load('name');
               
    return ctx.sync().then(function () {
                    table
    .name = "customTableName";
               
    });

    Why don't you have to call ctx.sync() again after setting the new table name?  It happens immediately without calling sync() and this seems contrary to the way the API is supposed to work.

    Thanks a lot for you help.

    Jim

    Wednesday, May 4, 2016 1:42 PM
  • Hi Jim,

    Thanks for the detail information for this issue.

    At present, we are not able to assign the table name when we create it via the Office.js. You can try to submit the feedback from here, if you want the Office add-in to support this feature.

    >>One other question I had is related to the renaming code below which works great:

                var table = binding.getTable();
                table.load('name');
                return ctx.sync().then(function () {
                    table.name = "customTableName";
                });

    Why don't you have to call ctx.sync() again after setting the new table name?  It happens immediately without calling sync() and this seems contrary to the way the API is supposed to work.

    It seems that the table's name could save it automatically when we using the sync before. To make the code more reliable, we can change the name and using sync() method to save the changing. Here is the code for your reference:

      function renameTableName() {
            Excel.run(function (ctx) {
                var binding = ctx.workbook.bindings.getItem("tableBinding1");
    
                var table = binding.getTable();
                table.load('name');
                table.name = "customTableName";
                return ctx.sync().then(function () {
                 
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
    Regards & Fei


    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 Thursday, May 5, 2016 1:20 PM
    Thursday, May 5, 2016 9:41 AM
    Moderator
  • Thanks Fei. That also worked.  I'll use that instead of setting it after the sync() since I don't know why that would work. I'll mark your last post as the answer.

    Jim

    Thursday, May 5, 2016 1:20 PM