none
vs2012 vb vsto Excel open worksheet RRS feed

  • Question

  • Hi

    I am creating an Excel application under VS2012 with VSTO and interop. This may seem like a ridiculous question but how do I open a worksheet either by its name or index if it has been created as below

    Globals.ThisWorkbook.newCandE = New Excel.Worksheet

            Globals.ThisWorkbook.newCandE = DirectCast(Globals.ThisWorkbook.Application.ActiveSheet, Excel.Worksheet)

            Globals.ThisWorkbook.newCandE.Copy(After:=Globals.ThisWorkbook.Sheets(noSheets))
            ans = InputBox("Please enter the name of the new C&E", "New Sheet")

            Globals.ThisWorkbook.newCandE.Name = ans
            Globals.ThisWorkbook.newCandE.Activate()

    The sheet created appears in the workbook and I can iterate around all sheets and get each name but I simply want to activate any of the sheets by its name.

    Any pointers greatly appreciated

    Saturday, July 6, 2013 6:53 PM

Answers

  • Normally, you'd create a new worksheet in a workbook like this:

    Dim newCandE as Excel.Worksheet = Globals.ThisWorkbook.Worksheets.Add('parameters here)

    You shouldn't be using the New keyword with any object that's part of the Excel object model, use the Add method.

    At this point, you have the object newCandE and can always work with the specific sheet by using that object - no need to preface it with anything else.

    Otherwise, to get any particular sheet by its name: Globals.ThisWorkbook.Worksheets("NAme")


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, July 7, 2013 10:14 AM
    Moderator

All replies

  • Normally, you'd create a new worksheet in a workbook like this:

    Dim newCandE as Excel.Worksheet = Globals.ThisWorkbook.Worksheets.Add('parameters here)

    You shouldn't be using the New keyword with any object that's part of the Excel object model, use the Add method.

    At this point, you have the object newCandE and can always work with the specific sheet by using that object - no need to preface it with anything else.

    Otherwise, to get any particular sheet by its name: Globals.ThisWorkbook.Worksheets("NAme")


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, July 7, 2013 10:14 AM
    Moderator
  • Hi Cindy

    Thanks for the reply. Still don't get it really as I want to copy a sheet in run time which has been added at design time. I have just used your suggestion and yes the sheet is created but I don't see how to change its name! I need to create the sheet with the name supplied by the user input from the prompt. 

    Any further pointers appriciated

    Phil

    Its OK I've got it! Trying too hard Doh!
    Sunday, July 7, 2013 10:57 AM
  • newCandE.Name = sUserInput

    Using the .Name property, just as you had in your original code.

    But please note: if you copy a worksheet at run-time it will not contain the VSTO extensions available in the original sheet.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, July 8, 2013 10:40 AM
    Moderator
  • Hi Cindy

    I would like to use the forum posts as a reference within a project report I am currently compiling so I would like to ask your permission to use your name.

    Regards

    Phil

    Friday, September 6, 2013 9:24 AM
  • Is it really necessary to use my name? Wouldn't "forum moderator" do the job?


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, September 6, 2013 3:53 PM
    Moderator
  • Hi Cindy

    Thanks for the reply. I never thought of it that way, I was just looking at the Harvard notation advice and it doesn't require a name but requires that I should ask permission to use the content. So thanks and I will refer as forum moderator.

    Regards

    Phil

    Friday, September 6, 2013 4:40 PM