Solved

DELETE Cells In Excel 2002 With VB.NET

Posted on 2007-03-23
2
207 Views
Last Modified: 2010-04-23
I'm using Visual Studio 2005.  I would like to keep what I'm trying to do within VB since that is what I'm most familiar with.  I'm trying to make a connection to Excel 2002.  I can do this through ADO.NET.  I'm trying to DELETE cells A1 - O3.  You can't use the DELETE command with ADO.NET.  Is there another way of doing this?
0
Comment
Question by:dkraatz
2 Comments
 
LVL 21

Accepted Solution

by:
theGhost_k8 earned 125 total points
ID: 18779543
you can use:-
    CType(_worksheet.Rows(row), Excel.Range).Delete()


http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21753891.html?sfQueryTermInfo=1+cell+delet+excel+vb.net#a16067725
answer from an expert:--
Here is part of a class that I am working on to encapsulate some of the more common requests for Excel automation code (work in progress):

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Public Class ExcelWorksheet

  Private _app As Excel.Application
  Private _workbook As Excel.Workbook
  Private _worksheet As Excel.Worksheet

  Public Sub New()

    _app = New Excel.Application

  End Sub


  Protected Overrides Sub Finalize()
    MyBase.Finalize()

    _workbook.Close(SaveChanges:=False)

    _app.Quit()

    Marshal.ReleaseComObject(_app)
    Marshal.ReleaseComObject(_workbook)
    Marshal.ReleaseComObject(_worksheet)

  End Sub   'Finalize


  Public Sub Save()

    _workbook.Save()

  End Sub  'Save


  Public Sub DeleteRow(ByVal row As Integer)

    If row < 1 OrElse row > _worksheet.Rows.Count Then
      Throw New ArgumentOutOfRangeException("Row must be 1.." & _worksheet.Rows.Count)
    End If

    CType(_worksheet.Rows(row), Excel.Range).Delete()

  End Sub


  Public Function GetWorksheetData(ByVal fileName As String, _
   ByVal sheetName As String, ByVal fromCell As String, ByVal toCell As String, _
   ByVal firstRowHeader As Boolean) As DataTable

    _workbook = _app.Workbooks.Open(fileName)

    _worksheet = _workbook.Worksheets(sheetName)

    Dim rangeSelect As Excel.Range = _worksheet.Range(fromCell, toCell)

    Dim rowCount As Integer = rangeSelect.Rows.Count
    Dim columnCount As Integer = rangeSelect.Columns.Count

    Dim tableNew As New System.Data.DataTable(sheetName)

    For colIndex As Integer = 1 To columnCount

      Dim colName As String = "Column" & colIndex

      If firstRowHeader Then
        colName = CType(rangeSelect.Cells(1, colIndex), Excel.Range).Value
      End If

      tableNew.Columns.Add(New DataColumn(colName, GetType(String)))

    Next colIndex

    For rowIndex As Integer = 1 To rowCount

      Dim rowNew As DataRow = tableNew.NewRow

      For colIndex As Integer = 1 To columnCount
        rowNew(colindex - 1) = CType(rangeSelect.Cells(rowIndex, colindex), Excel.Range).Value
      Next

      tableNew.Rows.Add(rowNew)

    Next

    Return tableNew

  End Function   'GetWorksheetData

End Class

Bob
0
 

Author Comment

by:dkraatz
ID: 18801101
Thank you Ghost.  I don't really understand what all this does so a friend is breaking it down for me.  I appreciated you reply, and it looks like this is going to do the trick for me.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now