CODEWITHSUNDEEP
excelvbassis
GPH
GPH

Reputation: 1897

SSIS Script Task Delete Row from excel Sheet

I have an SSIS package that creates and populates an excel sheet, it creates the sheet from a template file.

One of the issue's I had was that excel would change the format of the rows. I did a work around of inserting a header row and hiding it.

However I now need to script a VB task in SSIS that opens the excel sheet and deletes that specific row.

I've seen some articles online however have not been able to find any code I can try and replicate and my VB knowledge is very limited and I am really struggling. SO far i've figured out how to delete the row.

Sub DeleteRow1()
Rows(4).Delete
End Sub

However i need to assign file strings and get the file to open and close as well...

Upvotes: 1

Views: 7424

Answers (2)

GPH
GPH

Reputation: 1897

after some discussions with a programmer here i managed to get some script to do exactly what I needed. Posting as an answer for anyone else out there who ever needs to do this in SSIS. 

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet

xlApp = New Excel.Application
xlApp.Workbooks.Open(Dts.Variables("NewFileName").Value.ToString)
xlSheet = xlApp.Workbooks(1).ActiveSheet


xlSheet.Rows(4).Delete()

xlApp.Workbooks(1).Save()
xlApp.Workbooks(1).Close()
xlSheet = Nothing

'
Dts.TaskResult = ScriptResults.Success

Uses a variable in SSIS to get the file name. This works perfectly although you do need to add Microsoft.Office.Interop.Excel Reference.

Upvotes: 3

Tom K.
Tom K.

Reputation: 1042

Sub deleteRow1()

Dim wkbk As Workbook

'You can also use an input box like this
' Dim wkbkname As Variant
' wkbkname = InputBox("Enter Workbook Name here.")
' Use wkbkname instead of wkbk then

Set wkbk = "Filename"

wkbk.Sheets(1).Rows(4).delete

End Sub

I hope that's what you were looking for.

Upvotes: 1

Related Questions