VS2012 excel application

Question

0.00/5 (No votes)

See more:

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?

VB

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 26-Jun-13 23:14pm

pjs6557

Updated 27-Jun-13 1:21am

Leo Chapiro

v2

Add a Solution

Comments

pjs6557 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?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tim Carmichael · Answer 1 · 2013-06-27T03:49:00

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.

Posted 27-Jun-13 3:49am

Tim Carmichael

Ron Beyer · Answer 2 · 2013-06-27T04:24:00

You can use this to get the worksheet by name:

C#

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...

TnTinMn · Answer 3 · 2013-06-27T17:59:00

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)

VS2012 excel application

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0