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.
Applies to: The information in this topic applies to document-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.
Example
Sub AddWorksheet()
' Find the Office project.
Dim vstProject As Project = DTE.Solution.Item(1)
' Find the Excel workbook.
Dim docItem As ProjectItem = _
vstProject.ProjectItems.Item("ExcelWorkbook1.xlsx")
' 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.xlsx")
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.
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