none
How to properly reference another worksheet to make an assignment statement? RRS feed

  • Question

  • Hello,

    I am working on an Excel sheet customization at the document-level in VS 2013. However I can't figure out how to properly write an assignment statement to reference another sheet (Sheet2) than the one I am changing code in (Sheet1).

    Dim sourceSheet As Worksheet
    sourceSheet = Globals.ThisWorkbook.Sheets(2) ' I have tried several versions but all throw an exceptions at run-time

    I am sorry if this is a silly question, I just started with VSTO (Apparently no longer called as such in VS 2013).

    Also can anybody recommend a good book for VSTO, I looked on Amazon, but all the books are at least 5-6 years old with poor review. Are there any new books for VS/Office 2013.

    Thanks a lot.

    Wednesday, January 29, 2014 8:25 PM

Answers

  • In order to address a specific sheet that is in the workbook at Design Time (and thus has a VSTO object associated with it):

    Globals.Sheet2 'Sheet2 is the name of the OBJECT, as you see it in VSTO

    If you want to work with the Worksheet as an Excel worksheet:

    sourceSheet = Globals.Sheet2.InnerObject

    <<6 years old with poor review. Are there any new books for VS/Office 2013. >>

    As far as I know, no new books have been written for VSTO in the last years. You will find a list of "recommended" books in "Please Read Next" pinned at the top of the forum

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/852a9e55-192d-475f-b0e7-02f08962c1eb/please-read-next-vsto-resource-list?forum=vsto

    The VSTO technology hasn't really changed in the past few years, so much of the information in books written for VSTO 2005/2008 still pertains. What has changed is "publication" of VSTO solutions, but that's never really been covered in any book on VSTO as the topic is extremely complex.

    The absolutely best book, unless you're coming from VBA, is the one by "the Erics": Visual Studio Tools for Office 2007. Nothing else can compare to it for the depth and understanding how the Tools function. But you do have to be able to read C#...


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by PC_Meister Thursday, January 30, 2014 7:51 PM
    Thursday, January 30, 2014 6:29 PM
    Moderator

All replies

  • Globals.ThisWorkbook.Sheets(2) is not always "Sheet 2".

    You can try this way to active a specified worksheet by sheet name.

    private Excel.Worksheet GetWorksheetByName(string name)
    {
      foreach (Excel.Worksheet worksheet in this.Worksheets)
      {
        if (worksheet.Name == name)
        {
          return worksheet;
        }
      }
    }
    
    private void ActivateWorksheetByName(string name)
    {
      GetWorksheetByName(name).Activate();
    }

    Then get this actived worksheet reference:

    Excel.Worksheet ws = (Excel.Worksheet)Globals.ThisAddin.Application.ActiveSheet;
    In addition, the code is by C#, you need to change to VB.

    Thursday, January 30, 2014 4:43 AM
  • In order to address a specific sheet that is in the workbook at Design Time (and thus has a VSTO object associated with it):

    Globals.Sheet2 'Sheet2 is the name of the OBJECT, as you see it in VSTO

    If you want to work with the Worksheet as an Excel worksheet:

    sourceSheet = Globals.Sheet2.InnerObject

    <<6 years old with poor review. Are there any new books for VS/Office 2013. >>

    As far as I know, no new books have been written for VSTO in the last years. You will find a list of "recommended" books in "Please Read Next" pinned at the top of the forum

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/852a9e55-192d-475f-b0e7-02f08962c1eb/please-read-next-vsto-resource-list?forum=vsto

    The VSTO technology hasn't really changed in the past few years, so much of the information in books written for VSTO 2005/2008 still pertains. What has changed is "publication" of VSTO solutions, but that's never really been covered in any book on VSTO as the topic is extremely complex.

    The absolutely best book, unless you're coming from VBA, is the one by "the Erics": Visual Studio Tools for Office 2007. Nothing else can compare to it for the depth and understanding how the Tools function. But you do have to be able to read C#...


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by PC_Meister Thursday, January 30, 2014 7:51 PM
    Thursday, January 30, 2014 6:29 PM
    Moderator