Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET VS2012
I am currently developing a Excel project with VS2012 and VB.Net implementing VSTO. I have a base workbook which has a default sheet which from an ADD command ic copied as per the code below. The code below works fine but I cannot see how to access this added sheet. Can anyone suggest how I reference this new sheet?
 
Dim noSheets As Integer
 
noSheets = Globals.ThisWorkbook.Worksheets.Count
 
Globals.Sheet2.Copy(after:=Globals.ThisWorkbook.Sheets(noSheets))
Globals.Sheet2.Visible = Excel.XlSheetVisibility.xlSheetVisible
 
Any pointers appreciated
 
Phil
Posted 27-Jun-13 0:14am
pjs6557270
Edited 27-Jun-13 2:21am
_duDE_8.1K
v2
Comments
pjs6557 at 27-Jun-13 9:58am
   
Hi Tim and thanks for the reply
 
It's all very different to VBA. The VS2012 excel solution allows you to create sheets within a workbook and you can iterate as per VBA but if you create a sheet during run time as with the code shown (which essentially creates 'sheet 3') I cannot find out how to access it as it is not a class in the project. When you run the project it will be added and can be edited as a sheet in excel and the if the .xls file is saved sheet 3 is saved and appears when you run the project runs again but you still cannot get at it (or thats what I need to know). In design time clearly it does not exist so I wonder if it has to be created as a class somehow?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I haven't used VSTO, but with VBA you can itterate through the list of sheets. I'm sure VSTO must have something similar. Does VSTO have a count of worksheets? Add a quick loop, check the worksheets name, and see what is is called.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can use this to get the worksheet by name:
 
private Excel.Worksheet GetWorksheetByName(string name)
{
  foreach (Excel.Worksheet worksheet in this.Worksheets)
  {
    if (worksheet.Name == name)
    {
      return worksheet;
    }
  }
  throw new ArgumentException();
}
 
private void ActivateWorksheetByName(string name)
{
  GetWorksheetByName(name).Activate();
}
 
Source[^]
 
[Edit]
 
It is odd that the worksheet doesn't have a way to return a reference to the created worksheet with the Copy() function, and it is important to note that the created worksheet is a Microsoft.Office.Interop.Excel.Worksheet and not a Micorosoft.Office.Tools.Excel.Worksheet. I think there should be a way to get it by index, but I can't find it so maybe this answer isn't helpful...
  Permalink  
v2
Comments
pjs6557 at 27-Jun-13 10:44am
   
Hi Ron, that is exactly the dead end I have come to and I have spent a great deal of time on MSDN searching. Something seems to be missing from Interop!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Take this with a grain of salt as I do not have VSTO, but based on my understanding it is an extended wrapper around the standard office interop.
 
After copying a worksheet, the newly created worksheet becomes the activeworksheet; therefore you should be able to use that to create a reference.
 
For standard interop the syntax would be:
Dim newcopy As Excel.Worksheet = DirectCast(app.ActiveSheet, Excel.Worksheet)
where "app" is a reference to the Excel application.
 
I believe the VSTO syntax would be:
Dim newcopy As Excel.Worksheet = DirectCast(Me.Application.ActiveSheet, Excel.Worksheet)
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 325
1 DamithSL 265
2 CPallini 235
3 Sergey Alexandrovich Kryukov 229
4 Maciej Los 190
0 OriginalGriff 5,455
1 DamithSL 4,422
2 Maciej Los 3,860
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,010


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 28 Jun 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100