0

I'm Trying to Delete some range(A2:BC2000) of Content on Excel using Script task on SSIS, or do you guys gave any alternate methods. Here is the code that i used 

Public Sub Main()
        Dim Wexcel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim Wb As Workbook = Wexcel.Workbooks.Open("C:\Users\Shawn\Documents\Depart.xlsx")
        Dim wf As WorksheetFunction = Wexcel.WorksheetFunction
        Dim sheet As Worksheet

        For Each sheet In Wexcel.Worksheets
            If wf.CountA(sheet.Range("A2:BC20000").Value) > 0 Then
                Wexcel.DisplayAlerts = False
                sheet.Delete()
                Wexcel.DisplayAlerts = True


            End If
        Next sheet
        Dts.TaskResult = ScriptResults.Success
    End Sub

if i run my package with this scripttask i'm getting an error below

enter image description here

Can anyone has solution to this??

Community
  • 1
  • 1
Bumblebee
  • 179
  • 1
  • 16
  • Just a suggestion - is it possible your script tries to delete all sheets? - that would cause an error since there must be at least one left. – avb Mar 21 '18 at 20:08
  • your right !! it was my fault i was just deleting all the sheets. thanks for the help!!! – Bumblebee Mar 22 '18 at 00:12

1 Answers1

0

I'm Trying to Delete some range(A2:BC2000) of Content

In your code, you have these lines:

 Wexcel.DisplayAlerts = False
sheet.Delete()
Wexcel.DisplayAlerts = True

These 3 lines delete the sheet, not the range.

If you just want to clear contents of the specified range, then use:

sheet.Range("A2:BC2000").ClearContents

Hope this helps

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38