none
Excel - Clear contents for a sheet

    Question

  • What is the best way to clear a sheet of all values off of an Excel sheet? Either using the WorkSheet or WorkBook class? My system is throwing an exception and am wondering if I am doing it wrong.

    advTHANKSance
    Monday, April 24, 2006 7:29 PM

Answers

  • Hi,

    Sorry, as you've deduced, there is no method that allows you to do a clear at the Worksheet or Workbook level.  However, the Cells property of Worksheet returns a Range that consists of all of the cells on the sheet.  So you can completely clear a worksheet with the syntax:

    Cells.ClearContents

    (Or use the Clear method if you want to clear formats and everything else)

    That said, I think you have a valid point that there would be benefit to exposing a Clear method directly on the Worksheet.  The above syntax is trivial, but it would would be clearer (pardon the pun) if it were callable directly from the worksheet.  Also, if you had charts (and maybe other objects as well), the implementation would involve more than just the one line of code.  I would encourage you to file this suggestion officially at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=29&SiteID=1

    Finally, to avoid confusion in the future, if you are asking a question specifically about the VSTO classes, you might want to add some namespace qualification.  Worksheet and Workbook classes exist in both Microsoft.Office.Interop.Excel and Microsoft.Office.Tools namespaces.  You would think that you wouldn't have classes in the interop assembly, but that isn't the case. 

    Sincerely,

    Geoff Darst

    Microsoft VSTO Team

    Wednesday, April 26, 2006 4:46 PM
  • You can clear contents by using the ClearContents method of the Selection object. Using the Selection object, you can select cells in a specific range or a whole worksheet.

    It is helpful to remember that this forum is mainly for issues that directly pertain to the Visual Studio Tools for Office tools per se. So you will be best served by posing this question to a forum or newsgroup wholly dedicated to application-specific issues such as yours. Here is a link to resources that will help you.

    http://msdn.microsoft.com/office/understanding/excel

    John.

    Tuesday, April 25, 2006 6:57 PM

All replies

  • You can clear contents by using the ClearContents method of the Selection object. Using the Selection object, you can select cells in a specific range or a whole worksheet.

    It is helpful to remember that this forum is mainly for issues that directly pertain to the Visual Studio Tools for Office tools per se. So you will be best served by posing this question to a forum or newsgroup wholly dedicated to application-specific issues such as yours. Here is a link to resources that will help you.

    http://msdn.microsoft.com/office/understanding/excel

    John.

    Tuesday, April 25, 2006 6:57 PM
  • Hi John,

    I appreciate your answer and the knowledge of the ClearContents is definately a method of doing a clear. My posting to this group was in hopes of finding out if the VSTO interface had a differing or alternate way of clearing a sheet of data. Hence by my sentance:

    > Either using the WorkSheet or WorkBook class?

    The method of using ClearContents requires one in Excel to specify a region which works fine for most case. But I find it un-symetrical, for when one uses a TextBox Control, there is a clear method and one does not have to specify where in the box, i.e. which lines to clear, it just clears it all. There are other examples I could point out where the full action is done.

    I just have found it odd that interops of excel don't provide it and was hoping that VSTO had added new functionality.

    Thanks,



    Wednesday, April 26, 2006 2:11 PM
  • Hi,

    Sorry, as you've deduced, there is no method that allows you to do a clear at the Worksheet or Workbook level.  However, the Cells property of Worksheet returns a Range that consists of all of the cells on the sheet.  So you can completely clear a worksheet with the syntax:

    Cells.ClearContents

    (Or use the Clear method if you want to clear formats and everything else)

    That said, I think you have a valid point that there would be benefit to exposing a Clear method directly on the Worksheet.  The above syntax is trivial, but it would would be clearer (pardon the pun) if it were callable directly from the worksheet.  Also, if you had charts (and maybe other objects as well), the implementation would involve more than just the one line of code.  I would encourage you to file this suggestion officially at: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=29&SiteID=1

    Finally, to avoid confusion in the future, if you are asking a question specifically about the VSTO classes, you might want to add some namespace qualification.  Worksheet and Workbook classes exist in both Microsoft.Office.Interop.Excel and Microsoft.Office.Tools namespaces.  You would think that you wouldn't have classes in the interop assembly, but that isn't the case. 

    Sincerely,

    Geoff Darst

    Microsoft VSTO Team

    Wednesday, April 26, 2006 4:46 PM
  • Hi Geoff,

    Thank you for the comments. I will follow all of your suggestions. This group has been very helpful to me and anything I can do to help the process is understood.

    Thanks!
    Thursday, April 27, 2006 2:56 PM
  • I have posted a suggestion as recommended to the Visual C++ General group.
    Monday, May 01, 2006 1:13 PM