Automating Excel Applications (How Do I in Visual Studio Tools for Office)

This page provides links to Help for commonly performed tasks that make use of the Microsoft Office Excel object model in Visual Studio Tools for Office. To see other categories of key tasks covered by Visual Studio Tools for Office Help, see How Do I in Visual Studio Tools for Office.

Programming Excel

• Excel Solutions
  Provides links to information about creating Excel solutions in Visual Studio Tools for Office.

• Getting Started Programming Document-Level Customizations for Excel
  Describes what you need to know to start creating Excel applications using Visual Studio Tools for Office.

• Getting Started Programming Application-Level Add-Ins
  Describes what you need to know to get started creating application-level add-ins.

• Programming Application-Level Add-Ins
  Describes the main programming tasks for creating application-level add-ins.

• Programming Document-Level Customizations
  Describes the main programming tasks for creating document-level customizations.

• How to: Create Event Handlers in Visual Studio Tools for Office
  Describes various ways to create event handlers in Visual Basic and C#, whether you are in design view or code view.

Charts

• How to: Add Chart Controls to Worksheets
  Shows the two ways to add Chart controls to your Excel worksheets: from within Excel, and programmatically at run time.

• Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons
  Shows the basics of changing chart styles using radio buttons on an Excel worksheet.

Calculations

• How to: Run Excel Calculations Programmatically
  Demonstrates how to use the Calculate method to run all calculations in a range, as well as in all open workbooks.

Lists

• How to: Add ListObject Controls to Worksheets
  Demonstrates several ways to add ListObject controls to Excel worksheets: from within Excel, from the Visual Studio Toolbox, from the Data Sources window, and programmatically.

• How to: Resize ListObject Controls
  Demonstrates how to change the size of a list in an Excel workbook by redefining its size in the Resize List dialog box, or by resizing it programmatically.

• How to: Map ListObject Columns to Data
  Describes how to map specific columns in a ListObject control to a data source.

• How to: Handle Errors When a New Row is Added to a ListObject Control
  Describes how to use the ErrorAddDataboundRow event to correct an error and attempt to add the row to the data source again.

Protection

• How to: Protect Workbooks
  Shows how you can use the Protect and Unprotect methods to protect a workbook so that users cannot add or delete worksheets, and to unprotect the workbook again.

• How to: Protect Worksheets
  Provides an example of how to use the protection feature in Excel to keep users and/or code from modifying objects within a worksheet.

• How to: Remove Protection from Worksheets
  Demonstrates the use of the Unprotect method to remove protection from an Excel worksheet.

• How to: Set and Clear Workbook Passwords
  Shows how the Password property gets or sets the password associated with an Excel workbook.

Ranges

• How to: Apply Styles to Ranges in Workbooks
  Shows how you can apply named styles to regions in Excel workbooks.

• How to: Clear Styles from Ranges in Workbooks
  Shows how you can clear styles that have been added to a range in Excel by applying the Normal style to the range.

• How to: Refer to Worksheet Ranges in Code
  Provides example code showing how to cause a Range object to refer to a single cell or group of cells.

• How to: Automatically Fill Ranges with Incrementally Changing Data
  Provides an example showing how the AutoFill method of the Range object enables you to fill a range with values automatically.

• How to: Search for Text in Worksheet Ranges
  Demonstrates the use of the Find method of the Range object to search for text within a range.

• How to: Add NamedRange Controls to Worksheets
  Describes several ways to add named ranges to worksheets: from within Excel, from the Visual Studio Toolbox, from the Data Sources window, and programmatically.

• How to: Delete NamedRange Controls at Design Time
  Describes how to use the Excel Define Name dialog box to delete NamedRange controls from an Excel worksheet.

• Walkthrough: Programming Against Events of a NamedRange Control
  Demonstrates how to add a NamedRange control to an Excel worksheet and program against the control's events.

• How to: Resize NamedRange Controls
  Shows how you can resize a NamedRange control by either redefining its size in the Define Name dialog box, or resizing it programmatically.

• How to: Apply Color to Excel Ranges
  Describes how to use the ToOle method to apply a color to text in a range of cells.

• How to: Store and Retrieve Date Values in Excel Ranges
  Describes how to use the FromOADate method to retrieve the value of dates that are stored in OLE Automation format.

Samples

• Data Analysis Sample
  Demonstrates data analysis tasks that are executed using toolbar buttons and menus in Excel.

• Excel Controls Sample
  Demonstrates how to access the events of managed controls on an Excel worksheet from a Visual Studio Tools for Office project assembly.

Smart Tags

• How to: Enable Smart Tags in Word and Excel
  Demonstrates how to make smart tags visible.

• How to: Add Smart Tags to Excel Workbooks
  Shows how to add a simple smart tag to an Excel document-level customization.

• How to: Create Smart Tags With Custom Recognizers in Excel
  Shows how to create a smart tag with a regular expression.

Walkthroughs

• Walkthrough: Displaying Text in a Text Box in a Worksheet Using a Button
  Shows the basics of using buttons and text boxes on Excel worksheets.

• Walkthrough: Changing Worksheet Formatting Using CheckBox Controls
  Demonstrates the basics of using check boxes on an Excel worksheet to change formatting.

• Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons
  Shows the basics of changing chart styles using radio buttons on an Excel worksheet.

• Walkthrough: Collecting Data Using a Windows Form
  Shows how to open a Windows Form from an Excel worksheet, collect information from the user, and write that information into a worksheet cell.

• Walkthrough: Simple Data Binding in a Document-Level Project
  Steps through the basics of binding a single data field in a SQL Server database to a NamedRange control in Excel.

Workbooks

• How to: Create New Workbooks
  Shows how to create a new workbook using the Add method of the Workbooks collection.

• How to: Open Workbooks
  Shows how to use the Open method of the Workbooks collection, passing in the path to the workbook.

• How to: Open Text Files as Workbooks
  Provides code that loads a text file as a workbook, using commas as delimiters, starting on the third row in the text file.

• How to: Activate Workbooks
  Shows how to use the Activate method to activate an Excel workbook and select the first sheet in the workbook, as well as how to refer to individual workbooks in code.

• How to: Save Workbooks
  Demonstrates how to save all open workbooks, save the current workbook in XML format, and save a backup copy of a workbook.

• How to: Close Workbooks
  Shows how to use the Close method to close a workbook and discard changes.

• How to: Add Worksheets to Workbooks Using Visual Studio Project Automation
  Provides an example that adds a new worksheet to an existing Excel workbook that is open in the Visual Studio designer.

Worksheets

• How to: Select Worksheets
  Demonstrates how to select the first sheet in a workbook using the Select method.

• How to: Add New Worksheets to Workbooks
  Shows how to add a new worksheet and a copy of an existing worksheet to an Excel workbook using the Add method of the Sheets collection.

• How to: Delete Worksheets from Workbooks
  Demonstrates how to use the Delete method of the Sheets collection to delete a specified worksheet.

• How to: Hide Worksheets
  Demonstrates how to show or hide an existing worksheet without having to delete and recreate the sheet.

• How to: Move Worksheets Within Workbooks
  Provides code that shows how to move the first worksheet in a workbook so that it is the last one in the collection.

• How to: Print Worksheets
  Provides code that prints two copies of the first page of the specified worksheet, and displays a preview of the document before printing.

• How to: Check Spelling in Worksheets
  Shows how to use the CheckSpelling method to check spelling in a worksheet and then display the results.

• How to: Copy Data and Formatting across Worksheets
  Demonstrates how to use the FillAcrossSheets method to copy data from a range on one sheet to all the other sheets in the workbook.

• How to: Sort Data in Worksheets Programmatically
  Shows how to use the Sort method of the Range object to sort data in a worksheet.

• How to: Add Worksheets to Workbooks Using Visual Studio Project Automation
  Provides an example that adds a new worksheet to an existing Excel workbook that is open in the Visual Studio designer.

• How to: Display Worksheet Comments
  Shows how to use the Comment property of the NamedRange control to display comments in Excel worksheets.

• How to: Send Values to Worksheet Cells
  Describes how to use the Value2 property of a named range to assign a value to the range.

• How to: Add and Delete Worksheet Comments
  Describes how to programmatically add comments to cells in a worksheet and how to delete the comments.

XML and Schemas

• How to: Map Schemas to Worksheets Inside Visual Studio
  Describes how to map an XML schema to a worksheet while the worksheet is open in a Visual Studio Tools for Office project, using the same Excel tools that you use when the workbook is open outside of Visual Studio.

• How to: Open XML Files as Workbooks
  Demonstrates how you can use the OpenXML method to open an XML file as a workbook.

See Also

Concepts

How Do I in Visual Studio Tools for Office