locked
Delete Unused Excel worksheets using VB.NET and Interop

    Question

  • Hello,

     

    How do I delete unused/unwanted excel worksheets, such as the sheet1, sheet2, sheet3 that are created automatically? I'm using VB.NET 2005 with SQL Server 2005, and office 2007.

     

    Thanks!

     

    Tuesday, September 25, 2007 7:25 PM

Answers

  • e_com1,

     

    According to your question on deleting Excel Worksheets, I would like to provide you the suggestions as follows:

     

    1. In your VB.NET application, please make sure you have the Excel 12.0 Object Library reference for Excel 2007. Then imports Microsoft.Office.Interop namespace at the beginning of your code.

     

    2. Please use Excel.Worksheets.Delete method to delete a specified worksheet. If the worksheet is created at run time, you must access it through the Microsoft Office Excel Sheets collection; otherwise you can access the Worksheet directly.

     

    The worksheet object can be one of two types:

     

    Host Item: Microsoft.Office.Tools.Excel.Worksheet host items are created at design time.

    Native Excel object: Excel Microsoft.Office.Interop.Excel.Worksheet objects are created at run time.

     

    The following code deletes a worksheet from a workbook by referencing the sheet through the index number of the Sheets collection. This code assumes that a new worksheet was created programmatically.

     

            CType(Me.Application.ActiveWorkbook.Sheets(4), Excel.Worksheet).Delete()

     

    3. Please take a look at the following article that can help you with the Excel Objects questions better:

     

    Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective 

    4. If you have any related questions, you can also post in Visual Studio Tools for Office forum for the further support.

     

    Thanks again for your question.

    Thursday, September 27, 2007 5:59 AM

All replies

  • e_com1,

     

    According to your question on deleting Excel Worksheets, I would like to provide you the suggestions as follows:

     

    1. In your VB.NET application, please make sure you have the Excel 12.0 Object Library reference for Excel 2007. Then imports Microsoft.Office.Interop namespace at the beginning of your code.

     

    2. Please use Excel.Worksheets.Delete method to delete a specified worksheet. If the worksheet is created at run time, you must access it through the Microsoft Office Excel Sheets collection; otherwise you can access the Worksheet directly.

     

    The worksheet object can be one of two types:

     

    Host Item: Microsoft.Office.Tools.Excel.Worksheet host items are created at design time.

    Native Excel object: Excel Microsoft.Office.Interop.Excel.Worksheet objects are created at run time.

     

    The following code deletes a worksheet from a workbook by referencing the sheet through the index number of the Sheets collection. This code assumes that a new worksheet was created programmatically.

     

            CType(Me.Application.ActiveWorkbook.Sheets(4), Excel.Worksheet).Delete()

     

    3. Please take a look at the following article that can help you with the Excel Objects questions better:

     

    Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective 

    4. If you have any related questions, you can also post in Visual Studio Tools for Office forum for the further support.

     

    Thanks again for your question.

    Thursday, September 27, 2007 5:59 AM
  • Hi,

     

    Since it's possible to delete a worksheet, i was wondering, is it possible to delete a particular column or cell in the worksheet as well. You see, i'm planning to get my application to read real time data from an excel file. But since there's a limit to how much data excel can store, i was planning to get it to delete the existing data from the excel worksheet once it has been read so that new incoming data can be written onto the worksheet and subsequently read as well. However, i'm not very sure on how to go about doing that. So i'm hoping tt u can help?

     

    Thanks.

    Friday, January 04, 2008 2:43 AM
  • Lord shadownKhan,

     

    Thanks for the question with the follow up. Could you please create a new thread to post your question in the correct forum? VB Interop and Upgrade forum is not proper for this kind of problem.

     

    If you are developing in VB.NET, please post the question in VB General/Language forum. If you are developing in VBA, please post in Visual Basic for Applications forum. If the project is created in Visual Studio Tools for Office, please post in VSTOO forum.

     

    Thanks again.

    Friday, January 04, 2008 5:14 AM
  • You reference "Understanding the Excel Object Model from a Visual Studio 2005 Developer's Perspective  ".

    Is there a similar document for VS 2008?

    Carl
    Tuesday, November 24, 2009 3:52 AM