none
Using GetVstoObject and HasVstoObject RRS feed

  • Question

  • I have a console app that creates an excel worksheet using the Interop Library, and then tries to get the vstoObject using the GetVstoObject to use the Microsoft.Office.Tools.Excel Library. When running the code below the HasVstoObject always returns false, not sure if it is not created yet when executing since this is being runned from a simple console app main program. From the documentation of Extensions it seems plausible that this can be done

        Microsoft.Office.Interop.Excel.Application excelApp = null;
        Microsoft.Office.Interop.Excel.Workbook workbook = null;
        Microsoft.Office.Interop.Excel.Sheets sheets = null;
        Microsoft.Office.Interop.Excel.Worksheet newSheet = null;
        Microsoft.Office.Tools.Excel.Worksheet XLsWorkSheet = null;
    
        excelApp = new Interop.Application();
        excelApp.Visible = true;
        workbook = excelApp.Workbooks.Add(Type.Missing);
        sheets = workbook.Sheets;
    
        newSheet = (Interop.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
        newSheet.Name = "My New Sheet";
        newSheet.Cells[1, 1] = "BOO!";
    
        if (Excel.Extensions.WorksheetExtensions.HasVstoObject(newSheet)) {
           XLsWorkSheet = Excel.Extensions.WorksheetExtensions.GetVstoObject(newSheet);
           Microsoft.Office.Tools.Excel.Controls.ComboBox combobox1 = XLsWorkSheet.Controls.AddComboBox(XLsWorkSheet.Range["A1", "A1"], "combobox1");
           combobox1.Items.Add("1 Item");
            }

    I also created a Excel 2007 addin to try this and it dosent work when using Globals.Factory.GetvstoObject Thanks


    Tuesday, September 10, 2013 5:50 PM

Answers

  • Hi,
    Since the parameter number of the method HasVstoObject is one, I think you are developing with VSTO 2008. Isn’t it?

    In fact, return Value of WorksheetExtensions.HasVstoObject Method is true if a Microsoft.Office.Tools.Excel.Worksheet host item has been created for the current native Worksheet object.

    In your code, no Microsoft.Office.Tools.Excel.Worksheet has been created before calling the HasVstoObject method. So method HasVstoObject will always return false.

    In addition, I will suggest you to use an Excel add-in instead of a console app to add a control in the worksheet.

    Here is the code for your reference:

    private void CreateWorkbookWithControl()
    {
        Microsoft.Office.Interop.Excel.Workbook workbook = null;
        Microsoft.Office.Interop.Excel.Sheets sheets = null;
        Microsoft.Office.Interop.Excel.Worksheet newSheet = null;
        Microsoft.Office.Tools.Excel.Worksheet XLsWorkSheet = null;
    
        workbook = Globals.ThisAddIn.Application.Workbooks.Add(Type.Missing);
        sheets = workbook.Sheets;
    
        newSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
        newSheet.Name = "My New Sheet";
        newSheet.Cells[1, 1] = "BOO!";
    
        XLsWorkSheet = Microsoft.Office.Tools.Excel.Extensions.WorksheetExtensions.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
        Microsoft.Office.Tools.Excel.Controls.ComboBox combobox1 = XLsWorkSheet.Controls.AddComboBox(XLsWorkSheet.Range["A1", "A1"], "combobox1");
        combobox1.Items.Add("1 Item");
    }
    


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 12, 2013 11:36 AM
    Moderator

All replies

  • One thing is certain: you can't use the "Tools" namespace outside a VSTO solution. Any code that works with that would have to be in an Add-in or a Workbook customization. And it won't be something that can be "co-ordinated" with a console app.

    What, more exactly, are you trying to achieve?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, September 12, 2013 8:15 AM
    Moderator
  • Hi,
    Since the parameter number of the method HasVstoObject is one, I think you are developing with VSTO 2008. Isn’t it?

    In fact, return Value of WorksheetExtensions.HasVstoObject Method is true if a Microsoft.Office.Tools.Excel.Worksheet host item has been created for the current native Worksheet object.

    In your code, no Microsoft.Office.Tools.Excel.Worksheet has been created before calling the HasVstoObject method. So method HasVstoObject will always return false.

    In addition, I will suggest you to use an Excel add-in instead of a console app to add a control in the worksheet.

    Here is the code for your reference:

    private void CreateWorkbookWithControl()
    {
        Microsoft.Office.Interop.Excel.Workbook workbook = null;
        Microsoft.Office.Interop.Excel.Sheets sheets = null;
        Microsoft.Office.Interop.Excel.Worksheet newSheet = null;
        Microsoft.Office.Tools.Excel.Worksheet XLsWorkSheet = null;
    
        workbook = Globals.ThisAddIn.Application.Workbooks.Add(Type.Missing);
        sheets = workbook.Sheets;
    
        newSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
        newSheet.Name = "My New Sheet";
        newSheet.Cells[1, 1] = "BOO!";
    
        XLsWorkSheet = Microsoft.Office.Tools.Excel.Extensions.WorksheetExtensions.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
        Microsoft.Office.Tools.Excel.Controls.ComboBox combobox1 = XLsWorkSheet.Controls.AddComboBox(XLsWorkSheet.Range["A1", "A1"], "combobox1");
        combobox1.Items.Add("1 Item");
    }
    


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 12, 2013 11:36 AM
    Moderator