none
Excel Addin object binding issues RRS feed

  • Question

  • Unable to add the ListObject to the active sheet object of excel add in.

    In excel workbook we can add the List Object as below

     Microsoft.Office.Tools.Excel.ListObject list2;
                        if (Globals.Sheet1.Controls.Contains("EmployeeDifferentialData"))
                        {
                            list2 = (ListObject)Globals.Sheet1.Controls["EmployeeDifferentialData"];
                        }
                        else
                        {
                            list2 = Globals.Sheet1.Controls.AddListObject(
                            CellReference, "EmployeeDifferentialData");
                        }
    
                        // Bind the list object to the Customers table.
                        list2.AutoSetDataBoundColumnHeaders = true;
                        list2.DataSource = dt;
                        list2.RefreshDataRows();

    But in Excel Add In object we are not able to directly add the list object or datatable.

    Can anyone help in resolving the issues. It will be a great help for me.

    How can we add our data source to the active sheet in our excel addins

    Thanks,

    Fayaz

    Monday, December 8, 2014 4:49 PM

Answers

  • Hi Fayaz,

    To add ListObject to worksheet in application level add-in, you need to achieve that through host item.

    # Add ListObject Controls to Worksheets

    http://msdn.microsoft.com/en-us/library/eyfs6478.aspx

    To get active worksheet, please refer to this code below:

    Excel.Worksheet w = this.Application.ActiveWorkbook.ActiveSheet;

    Best Regards

    Starain


    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.

    Tuesday, December 9, 2014 9:49 AM
    Moderator
  • Hi Fayaz,

    The Controls property is in Worksheet interface that namespace is Microsoft.Office.Tools.Excel, you need to access it through host item.

    Microsoft.Office.Tools.Excel.Worksheet worksheet=Globals.Factory.GetVstoObject(wb.Worksheets[1]);

    If you still have the issue, please provide the detail code.

    Best Regards

    Starain


    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, December 10, 2014 8:41 AM
    Moderator
  • Hi FayazAlam,

    For the original issue “Unable to add the ListObject to the active sheet object of excel add in”, as Starain mentioned, you could use Globals.Factory.GetVstoObject to get the host item in application level add-in.

    For the second issue “HRESULT: 0x80004002 (E_NOINTERFACE))”, I noticed that you were using async programming, I suspect the Globals.Factory.GetVstoObject was called in the background thread. The  Globals.Factory.GetVstoObject method should be called from the UI thread rather than the background thread.

    Hope it will help.

    Regards,

    Jeffrey


    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.

    Friday, December 19, 2014 2:11 AM
    Moderator
  • Hi Starain,

    Thanks a lot for your help.

    I changed the code a little as below and its working fine for now.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using System.Net.Http;
    using System.Xml;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Tools.Excel;
    using System.Data;
    using System.Net.Http.Headers;
    using System.Threading.Tasks;
    
    namespace MyImpact_AddIns
    {
        public partial class MyImpact_Ribbon
        {
    
            Microsoft.Office.Interop.Excel.Workbook workbook;
            DataTable dt = null;
            int i = 0;
    
            private void MyImpact_Ribbon_Load(object sender, RibbonUIEventArgs e)
            {
                ((Excel.AppEvents_Event)Globals.ThisAddIn.Application).NewWorkbook += ThisAddIn_NewWorkbook;
            }
    
            private void ThisAddIn_NewWorkbook(Microsoft.Office.Interop.Excel.Workbook wb)
            {
                workbook = wb;
            }
    
            private void btnDate_Click(object sender, RibbonControlEventArgs e)
            {
                DateTime Reportdate = new DateTime();
                try
                {
                    Reportdate = DateTime.Parse(this.ebDate.Text);
                }
                catch (FormatException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date in correct format as mentioned.");
                }
                catch (ArgumentNullException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date, in order to show the report.");
                }
                catch (Exception)
                {
                    System.Windows.Forms.MessageBox.Show("Oops, Unexpected error occured, Please try again later.");
                }
                try
                {
                    Globals.ThisAddIn.Application.StatusBar = "Please Wait... Getting Data from Servers.";
                    GetReportData(Reportdate).Wait();
                    CreateListObject();
                }
                catch (Exception)
                {
    
                    Globals.ThisAddIn.Application.StatusBar = "Oops, Error has occured while getting Data from Servers.";
                }
            }
    
            async Task GetReportData(DateTime Reportdate)
            {
                using (var client = new HttpClient())
                {
                    // New code:
                    client.BaseAddress = new Uri("http://myimpact-stage.cloudapp.net:81/");
                    client.DefaultRequestHeaders.Accept.Clear();
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));
    
                    HttpResponseMessage response = await client.GetAsync(string.Format("api/products/{0:MM-dd-yyyy}", Reportdate));
                    bool isSuccess = response.IsSuccessStatusCode;
                    try
                    {
                        if (response.IsSuccessStatusCode)
                        {
                            string xmldata = await response.Content.ReadAsStringAsync();
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.LoadXml(xmldata);
                            dt = new System.Data.DataTable();
                            StringReader sr = new StringReader(xdoc.InnerText);
                            dt.ReadXml(sr);
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
    
            private void CreateListObject()
            {
                try
                {
    
                    Microsoft.Office.Interop.Excel.Worksheet activeWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
                   
                    activeWorksheet.Cells.Clear();
    
                    Microsoft.Office.Tools.Excel.ListObject list2 = null;
                    Excel.Worksheet s = workbook.Worksheets[activeWorksheet.Index];
                    Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                    Excel.Range cell = worksheet.Range["$B$2"];
                    if (worksheet.Controls.Contains("list"))
                    {
                        list2 = (ListObject)worksheet.Controls["list"];
                    }
                    else
                    {
                        list2 = worksheet.Controls.AddListObject(cell, "list");
                    }
                    // Bind the list object to the Customers table.
                    list2.AutoSetDataBoundColumnHeaders = true;
                    list2.DataSource = dt;
                    list2.RefreshDataRows();
                    if (dt.Rows.Count <= 0)
                    {
                        Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully. Looks like there is no data for this date.";
                    }
                    else
                    {
                        Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully.";
                    }
                }
                catch (Exception)
                {
    
                    throw;
                }
            }
         
        }
    }
    

    Thanks,

    Fayaz

    Friday, December 19, 2014 7:39 AM

All replies

  • Hi Fayaz,

    To add ListObject to worksheet in application level add-in, you need to achieve that through host item.

    # Add ListObject Controls to Worksheets

    http://msdn.microsoft.com/en-us/library/eyfs6478.aspx

    To get active worksheet, please refer to this code below:

    Excel.Worksheet w = this.Application.ActiveWorkbook.ActiveSheet;

    Best Regards

    Starain


    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.

    Tuesday, December 9, 2014 9:49 AM
    Moderator
  • Hi Starain ,

    Thanks for your response.Could you please help resolving the below issues.

    Controls is not a member in addin (in active sheet,its in workbook)so its throwing error at compile time.is there any other way to bind my object to addin active sheet.

      list1 = worksheet.Controls.AddListObject(cell, "list1");

    It will be a great help if you could add some ideas.

    Thanks,

    Fayaz

    Tuesday, December 9, 2014 6:10 PM
  • Hi Fayaz,

    The Controls property is in Worksheet interface that namespace is Microsoft.Office.Tools.Excel, you need to access it through host item.

    Microsoft.Office.Tools.Excel.Worksheet worksheet=Globals.Factory.GetVstoObject(wb.Worksheets[1]);

    If you still have the issue, please provide the detail code.

    Best Regards

    Starain


    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, December 10, 2014 8:41 AM
    Moderator
  • Hi Starain ,

    I want to add the datatable object to my active sheet in excel add in ,I used the above concept but COM level cast error is coming like unable to cast from interop.Excel to Tool.Excel.Below is my code ,Only I want my data table to add in active sheet.

    string xmldata = await response.Content.ReadAsStringAsync();
                        XmlDocument xdoc = new XmlDocument();
                        xdoc.LoadXml(xmldata);
                        System.Data.DataTable dt = new System.Data.DataTable();
                        StringReader sr = new StringReader(xdoc.InnerText);
                        dt.ReadXml(sr);
    
                        Microsoft.Office.Interop.Excel.Worksheet activeWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
    
                        
      MyExcelTool.ListObject list1 = null;
    
                            MyExcelTool.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
                            Excel.Range cell = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1].Range["B2"];
                            list1 = worksheet.Controls.AddListObject(cell, "list1");

                        
    
                        // Below code is used to add the data into excel.But Its not the way
                        /*
                        activeWorksheet.Name = "Export to Excel";
                        activeWorksheet.Cells.Clear();
    
                        int rowCount1 = 1;
                        int columnCount = 1;
                        activeWorksheet.Cells[rowCount1, dt.Columns.Count].EntireRow.Font.Bold = true;
                        foreach (DataColumn dc in dt.Columns)
                        {
                            activeWorksheet.Cells[rowCount1, columnCount + 1] = dc.ColumnName;
                            columnCount++;
                        }
                        int rowCount = 1;
                        foreach (DataRow dr in dt.Rows)
                        {
                            int columnCount1 = 1;
                            foreach (DataColumn dc in dt.Columns)
                            {
                                activeWorksheet.Cells[rowCount + 1, columnCount1 + 1] = dr[dc.ColumnName].ToString();
                                columnCount1++;
                            }
                            rowCount++;
                        }*/

    Here in the middle code its throwing COM error

    Could you please give some idea to make this code workable.Waiting for your response.

    Thanks a lot

    Fayaz



    • Edited by FayazAlam Wednesday, December 10, 2014 11:16 AM
    Wednesday, December 10, 2014 10:54 AM
  • This below error I am getting

    Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.Tools.Office.Runtime.Interop.IHostItemFactoryNoMAF'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{A0885C0A-33F2-4890-8F29-25C8DE7808F1}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

    Wednesday, December 10, 2014 11:55 AM
  • Hi Fayaz,

    Base on your code, I found that you have this code below:

    string xmldata = await response.Content.ReadAsStringAsync();

    So, I think you do that logical in other thread, it is incorrect, you need to do it in the main UI thread.

    You could get the host item object before call asynchronous method. (e.g. before await response.Content…)

    There are some links about that exception that may benefit you:

    # Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.Tools.Office.Runtime.Interop.IHostItemFactoryNoMAF'

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/407a779f-41ef-4b6c-bfaf-54d09659a076/unable-to-cast-com-object-of-type-systemcomobject-to-interface-type?forum=vsto

    # Why your COMAddIn.Object should derive from StandardOleMarshalObject

    http://blogs.msdn.com/b/andreww/archive/2008/08/11/why-your-comaddin-object-should-derive-from-standardolemarshalobject.aspx

    Best Regards

    Starain


    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.

    Thursday, December 11, 2014 9:03 AM
    Moderator
  • Hi Starain ,

    I changed the code and kept it before the async call still getting the same error.

    Could you please provide a simple solution ,Thanks a lot for your response.

    Thanks,

    Fayaz

    Thursday, December 11, 2014 10:31 AM
  • Please see the below simple app where same error is comming

    private void btnClick_Click(object sender, RibbonControlEventArgs e) { try { List<int> primes = new List<int>(); primes.Add(2); primes.Add(3); primes.Add(5); primes.Add(7); Microsoft.Office.Interop.Excel.Worksheet activeWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); Microsoft.Office.Tools.Excel.Worksheet vstoList = null; MyExcelTool.ListObject list1 = null; Excel.Range cell = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Range["B2"]; // Here COM exception throwing

    var data = (MyExcelTool.Worksheet)activeWorksheet; list1 = vstoList.Controls.AddListObject(cell, "list1"); } catch (Exception) { throw; } }

    Could you please let help in solving this also.

    Thanks,

    Fayaz


    • Edited by FayazAlam Thursday, December 11, 2014 10:58 AM
    Thursday, December 11, 2014 10:58 AM
  • Hi Fayaz,

    To get the host item, please use Globals.Factory.GetVstoObject method.

    If you still have the issue, please share your project on the OneDrive.

    What I did is create ListObject in NewWorkbook event:

     private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                ((Excel.AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;
           }
     void ThisAddIn_NewWorkbook(Excel.Workbook Wb)
            {
                CreateListObject(Wb);
            }
            private void CreateListObject(Excel.Workbook Wb)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("id", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                var row = dt.NewRow();
                row[0] = 1;
                row[1] = "hello";
                dt.Rows.Add(row);
                var row2 = dt.NewRow();
                row2[0] = 2;
                row2[1] = "hello2";
                dt.Rows.Add(row2);
                Microsoft.Office.Tools.Excel.ListObject list2;
                Excel.Worksheet s = Wb.Worksheets[1];
                Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                Excel.Range cell = worksheet.Range["$A$1:$D$4"];
                list2 = worksheet.Controls.AddListObject(cell, "list1");
    
                // Bind the list object to the Customers table.
                list2.AutoSetDataBoundColumnHeaders = true;
                list2.DataSource = dt;
                list2.RefreshDataRows();
            }

    Regards

    Starain


    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.

    Friday, December 12, 2014 1:39 AM
    Moderator
  • Hi Starain,

    Thanks for your response ,Apologies for late response and we had holiday. I think we are close but Can you please help me on the below issues.

    I want to use your  below code in my Button click on Ribbon

    ((Microsoft.Office.Interop.Excel.
    
    AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;
    
    I am using it like below

      private void btnTest_Click(object sender, RibbonControlEventArgs e)
            {
                ((Microsoft.Office.Interop.Excel.AppEvents_Event)Globals.ThisAddIn.Application).NewWorkbook += ThisAddIn_NewWorkbook;
            }

    But here this event is not firing .SO could you please help me in that and if possible could you please guide how to use this Excel Addin in end User machine by taking the dll.

    Thanks,

    fayaz


    Monday, December 15, 2014 2:31 PM
  • Hi fayaz,

    For these issues that are not related to original issue, I suggest that you should create the new threads for them.

    Best Regards

    Starain


    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.

    Tuesday, December 16, 2014 1:40 AM
    Moderator
  • Hi Starain,

    Yes I will create a new thread for deployement,but my existing issues is not resolved.Could you please help a little more to resolve it.

    As per your code If I am keeping this below event inside StartUp then firing but if I am keeping inside a button click its not firing .I have the code above.Could you please help to resolve it.

    ((Excel.AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;

    private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                ((Excel.AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;
           }


    • Edited by FayazAlam Tuesday, December 16, 2014 1:45 PM
    Tuesday, December 16, 2014 1:44 PM
  • Hi FayazAlam,

    I can’t reproduce that issue, you may share a file on the OneDrive, this is my code:

     public partial class Ribbon1
        {
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Globals.ThisAddIn.BindEvent();
            }
        }
     internal void BindEvent()
            {
                ((Excel.AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;
            }
            void ThisAddIn_NewWorkbook(Excel.Workbook Wb)
            {
                CreateListObject(Wb);
            }

    On the other hand, this issue is related to event not firing, your original issue is related to add ListObject, I don’t think they are related.

    Thanks for your understanding

    Best Regards

    Starain


    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, December 17, 2014 1:36 AM
    Moderator
  • Hi Starain,

    FYI : I want to bind the List-object on my button click. So when I am using this code its throwing error.

    I have shared in OneDrive Please have a look on the code , I have just kept your code in my solution.

    https://microsoft-my.sharepoint.com/personal/v-skfab_microsoft_com1/Documents/ExcelAddIn_ListBind_Test.zip

    Thanks,

    Fayaz


    • Edited by FayazAlam Wednesday, December 17, 2014 11:02 AM
    Wednesday, December 17, 2014 11:01 AM
  • Hi Fayaz,

    I don’t have the permission to access that file. Please share it on the OneDrive.

    # Share files and folders and change permissions

    http://windows.microsoft.com/en-us/onedrive/share-file-folder

    Best Regards

    Starain


    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.

    Thursday, December 18, 2014 2:06 AM
    Moderator
  • Hi Starain,

    Never mind. I am not able to share this folder ,could you please give your email id or any id to share.

    Thanks,

    Fayaz


    • Edited by FayazAlam Thursday, December 18, 2014 11:52 AM
    Thursday, December 18, 2014 7:09 AM
  • Hi Fayaz,

    internal void BindEvent()
            {
                // Here this.Application is not recognised
                ((Excel.AppEvents_Event)this.Application).NewWorkbook += ThisAddIn_NewWorkbook;
               
            }
            void ThisAddIn_NewWorkbook(Excel.Workbook Wb)
            {
                CreateListObject(Wb);
            }
    
            //void ThisAddIn_NewWorkbook(Excel.Workbook Wb)
            //{
            //    CreateListObject(Wb);
            //}
            private void CreateListObject(Excel.Workbook Wb)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("id", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                var row = dt.NewRow();
                row[0] = 1;
                row[1] = "hello";
                dt.Rows.Add(row);
                var row2 = dt.NewRow();
                row2[0] = 2;
                row2[1] = "hello2";
                dt.Rows.Add(row2);
                Microsoft.Office.Tools.Excel.ListObject list2;
                Excel.Worksheet s = Wb.Worksheets[1];
                Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                Excel.Range cell = worksheet.Range["$A$1:$D$4"];
                list2 = worksheet.Controls.AddListObject(cell, "list1");
    
                // Bind the list object to the Customers table.
                list2.AutoSetDataBoundColumnHeaders = true;
                list2.DataSource = dt;
                list2.RefreshDataRows();
            }

    This code should be in the ThisAddIn class instead of Ribbon_Test class.

    Best Regards

    Starain


    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.

    Thursday, December 18, 2014 8:31 AM
    Moderator
  • Hi Starain,

    Thanks for your time and helps,I got the problem,I am using Web Api and here we have threading concept so inside this the I am binding this object to sheet and because of this Async thread I think I am not able to bind the list and throwing exception .Below is my code ,Do you have any idea how to deal with thread and do binding.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using System.Net.Http;
    using System.Xml;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Tools.Excel;
    using System.Data;
    using System.Net.Http.Headers;
    
    namespace MyImpact_AddIns
    {
        public partial class MyImpact_Ribbon
        {
    
            Microsoft.Office.Interop.Excel.Workbook workbook;
            DataTable dt = null;
    
            private void MyImpact_Ribbon_Load(object sender, RibbonUIEventArgs e)
            {
                ((Excel.AppEvents_Event)Globals.ThisAddIn.Application).NewWorkbook += ThisAddIn_NewWorkbook;
            }
    
            private void ThisAddIn_NewWorkbook(Microsoft.Office.Interop.Excel.Workbook wb)
            {
                workbook = wb;
            }
    
            private void btnDate_Click(object sender, RibbonControlEventArgs e)
            {
                DateTime Reportdate = new DateTime();
                try
                {
                    Reportdate = DateTime.Parse(this.ebDate.Text);
                }
                catch (FormatException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date in correct format as mentioned.");
                }
                catch (ArgumentNullException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date, in order to show the report.");
                }
                catch (Exception)
                {
                    System.Windows.Forms.MessageBox.Show("Oops, Unexpected error occured, Please try again later.");
                }
                try
                {
                    Globals.ThisAddIn.Application.StatusBar = "Please Wait... Getting Data from Servers.";
                    GetReportData(Reportdate);
                }
                catch (Exception)
                {
    
                    Globals.ThisAddIn.Application.StatusBar = "Oops, Error has occured while getting Data from Servers.";
                }
            }
    
            async void GetReportData(DateTime Reportdate)
            {
                using (var client = new HttpClient())
                {
                    // New code:
                    client.BaseAddress = new Uri("http://myimpact-stage.cloudapp.net:81/");
                    client.DefaultRequestHeaders.Accept.Clear();
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));
    
                    HttpResponseMessage response = await client.GetAsync(string.Format("api/products/{0:MM-dd-yyyy}", Reportdate));
                    bool isSuccess = response.IsSuccessStatusCode;
                    try
                    {
                        if (response.IsSuccessStatusCode)
                        {
                            string xmldata = await response.Content.ReadAsStringAsync();
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.LoadXml(xmldata);
                            dt = new System.Data.DataTable();
                            StringReader sr = new StringReader(xdoc.InnerText);
                            dt.ReadXml(sr);
    
                            Microsoft.Office.Tools.Excel.ListObject list2;
    
                            // Here I am geting error (exception) ,here workbook is comming as null,Do you have any idea how to deal with it.?
                            Excel.Worksheet s = workbook.Worksheets[1];
                            Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                            Excel.Range cell = worksheet.Range["$A$1"];
                            list2 = worksheet.Controls.AddListObject(cell, "list1");
    
                            // Bind the list object to the Customers table.
                            list2.AutoSetDataBoundColumnHeaders = true;
                            list2.DataSource = dt;
                            list2.RefreshDataRows();
    
                            if (dt.Rows.Count <= 0)
                            {
                                Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully. Looks like there is no data for this date.";
                            }
                            else
                            {
                                Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully.";
                            }
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
        }
    }

     I got this link for this threading but unable to understand how to implement it.Could you please help on it.

    http://blogs.msdn.com/b/andreww/archive/2008/11/19/implementing-imessagefilter-in-an-office-add-in.aspx

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/f38edd9b-34dd-4cc0-bad2-71d42b667477/multithreading-in-vsto?forum=vsto

    Thanks,

    Fayaz


    • Edited by FayazAlam Thursday, December 18, 2014 3:30 PM
    Thursday, December 18, 2014 3:13 PM
  • Hi Fayaz,

    Please try to use this code below:

      async void GetReportData(DateTime Reportdate)
            {
                Microsoft.Office.Tools.Excel.ListObject list2;
                            Excel.Worksheet s = workbook.Worksheets[1];
                            Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                            Excel.Range cell = worksheet.Range["$A$1"];
                            list2 = worksheet.Controls.AddListObject(cell, "list1");
    
                using (var client = new HttpClient())
                {
                    // New code:
                    client.BaseAddress = new Uri("http://myimpact-stage.cloudapp.net:81/");
                    client.DefaultRequestHeaders.Accept.Clear();
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));
    
                    HttpResponseMessage response = await client.GetAsync(string.Format("api/products/{0:MM-dd-yyyy}", Reportdate));
                    bool isSuccess = response.IsSuccessStatusCode;
                    try
                    {
                        if (response.IsSuccessStatusCode)
                        {
                            string xmldata = await response.Content.ReadAsStringAsync();
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.LoadXml(xmldata);
                            dt = new System.Data.DataTable();
                            StringReader sr = new StringReader(xdoc.InnerText);
                            dt.ReadXml(sr);
    
                            
                            // Bind the list object to the Customers table.
                            list2.AutoSetDataBoundColumnHeaders = true;
                            list2.DataSource = dt;
                            list2.RefreshDataRows();
    
                            if (dt.Rows.Count <= 0)
                            {
                                Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully. Looks like there is no data for this date.";
                            }
                            else
                            {
                                Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully.";
                            }
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
    

    Best Regards

    Starain


    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.

    Friday, December 19, 2014 1:39 AM
    Moderator
  • Hi FayazAlam,

    For the original issue “Unable to add the ListObject to the active sheet object of excel add in”, as Starain mentioned, you could use Globals.Factory.GetVstoObject to get the host item in application level add-in.

    For the second issue “HRESULT: 0x80004002 (E_NOINTERFACE))”, I noticed that you were using async programming, I suspect the Globals.Factory.GetVstoObject was called in the background thread. The  Globals.Factory.GetVstoObject method should be called from the UI thread rather than the background thread.

    Hope it will help.

    Regards,

    Jeffrey


    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.

    Friday, December 19, 2014 2:11 AM
    Moderator
  • Hi Starain,

    Thanks a lot for your help.

    I changed the code a little as below and its working fine for now.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using System.Net.Http;
    using System.Xml;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Tools.Excel;
    using System.Data;
    using System.Net.Http.Headers;
    using System.Threading.Tasks;
    
    namespace MyImpact_AddIns
    {
        public partial class MyImpact_Ribbon
        {
    
            Microsoft.Office.Interop.Excel.Workbook workbook;
            DataTable dt = null;
            int i = 0;
    
            private void MyImpact_Ribbon_Load(object sender, RibbonUIEventArgs e)
            {
                ((Excel.AppEvents_Event)Globals.ThisAddIn.Application).NewWorkbook += ThisAddIn_NewWorkbook;
            }
    
            private void ThisAddIn_NewWorkbook(Microsoft.Office.Interop.Excel.Workbook wb)
            {
                workbook = wb;
            }
    
            private void btnDate_Click(object sender, RibbonControlEventArgs e)
            {
                DateTime Reportdate = new DateTime();
                try
                {
                    Reportdate = DateTime.Parse(this.ebDate.Text);
                }
                catch (FormatException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date in correct format as mentioned.");
                }
                catch (ArgumentNullException)
                {
                    System.Windows.Forms.MessageBox.Show("Please enter the report date, in order to show the report.");
                }
                catch (Exception)
                {
                    System.Windows.Forms.MessageBox.Show("Oops, Unexpected error occured, Please try again later.");
                }
                try
                {
                    Globals.ThisAddIn.Application.StatusBar = "Please Wait... Getting Data from Servers.";
                    GetReportData(Reportdate).Wait();
                    CreateListObject();
                }
                catch (Exception)
                {
    
                    Globals.ThisAddIn.Application.StatusBar = "Oops, Error has occured while getting Data from Servers.";
                }
            }
    
            async Task GetReportData(DateTime Reportdate)
            {
                using (var client = new HttpClient())
                {
                    // New code:
                    client.BaseAddress = new Uri("http://myimpact-stage.cloudapp.net:81/");
                    client.DefaultRequestHeaders.Accept.Clear();
                    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/xml"));
    
                    HttpResponseMessage response = await client.GetAsync(string.Format("api/products/{0:MM-dd-yyyy}", Reportdate));
                    bool isSuccess = response.IsSuccessStatusCode;
                    try
                    {
                        if (response.IsSuccessStatusCode)
                        {
                            string xmldata = await response.Content.ReadAsStringAsync();
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.LoadXml(xmldata);
                            dt = new System.Data.DataTable();
                            StringReader sr = new StringReader(xdoc.InnerText);
                            dt.ReadXml(sr);
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }
    
            private void CreateListObject()
            {
                try
                {
    
                    Microsoft.Office.Interop.Excel.Worksheet activeWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
                   
                    activeWorksheet.Cells.Clear();
    
                    Microsoft.Office.Tools.Excel.ListObject list2 = null;
                    Excel.Worksheet s = workbook.Worksheets[activeWorksheet.Index];
                    Worksheet worksheet = Globals.Factory.GetVstoObject(s);
                    Excel.Range cell = worksheet.Range["$B$2"];
                    if (worksheet.Controls.Contains("list"))
                    {
                        list2 = (ListObject)worksheet.Controls["list"];
                    }
                    else
                    {
                        list2 = worksheet.Controls.AddListObject(cell, "list");
                    }
                    // Bind the list object to the Customers table.
                    list2.AutoSetDataBoundColumnHeaders = true;
                    list2.DataSource = dt;
                    list2.RefreshDataRows();
                    if (dt.Rows.Count <= 0)
                    {
                        Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully. Looks like there is no data for this date.";
                    }
                    else
                    {
                        Globals.ThisAddIn.Application.StatusBar = "Fetching completed sucessfully.";
                    }
                }
                catch (Exception)
                {
    
                    throw;
                }
            }
         
        }
    }
    

    Thanks,

    Fayaz

    Friday, December 19, 2014 7:39 AM