How to: Add Worksheets to Workbooks Using Visual Studio Project Automation

This example adds a new worksheet to an existing Microsoft Office Excel workbook that is open in the Visual Studio designer.

Example

Sub AddWorksheet()
    ' Find the Visual Studio Tools for Office project.
    Dim vstProject As Project = DTE.Solution.Item(1)

    ' Find the Excel workbook.
    Dim docItem As ProjectItem = _
        vstProject.ProjectItems.Item("ExcelWorkbook1.xls")

    ' Activate the Solution Explorer window.
    Dim solExpl As Window = _
        DTE.Windows.Item(Constants.vsWindowKindSolutionExplorer)
    solExpl.Activate()

    ' Find and select the Excel workbook.
    Dim uiHier As UIHierarchy = solExpl.Object

    ' The path follows the pattern
    ' SolutionName\ProjectName\WorkbookName.
    Dim item As UIHierarchyItem = _
        uiHier.GetItem("ExcelWorkbook1\ExcelWorkbook1.xls")
    item.Select(vsUISelectionType.vsUISelectionTypeSelect)

    ' Execute the shortcut menu command to add a new worksheet.
    Dim commandGuid As String = _
        "{AE97F291-448D-4F9F-93A8-B0D2B78475A4}"

    ' Set commandID to 1 to add a new worksheet, 2 to add a new chart.
    Dim commandID As Integer = 1 
    Dim customIn As Object
    Dim customOut As Object
    DTE.Commands.Raise(commandGuid, commandID, customIn, customOut)
End Sub

Compiling the Code

This example has the following requirements:

• An Excel workbook named ExcelWorkbook1 in a project named ExcelWorkbook1 open in the Visual Studio designer. There must be no other projects in the solution.

• The format of the workbook in the project must be Excel 97-2003 workbook (.xls).

• When you create the ExcelWorkbook1 project, ensure that you do not select Create directory for solution in the New Project dialog box.

Robust Programming

Add the code to a module in the Visual Studio Macros integrated development environment (IDE).

Clear the Always show solution option in the Options dialog box. To access this dialog box, on the Tools menu, click Options, and then select the Projects and Solutions folder and click General.

See Also

Tasks

Walkthrough: Creating a New Office Project Using Visual Studio Project Automation

How to: Change Excel Properties Using Visual Studio Project Automation

Concepts

Visual Studio Tools for Office Project Extensibility Overview

Other Resources

Project Extensibility in Visual Studio Tools for Office