CODEWITHSUNDEEP
scriptingvbscript
Paula A Vangel
Paula A Vangel

Reputation: 31

I need to find a way to delete a row in a spreadsheet using VBScript

Hello Fellow data warriors- I need to find a way to delete a row in a spreadsheet using VBScript.

The row I want to delete is in a spreadsheet that contains the results of a People Soft Query of employee data. I have a routine written to load the eployee data into a SQL Server database table. The problem is that there is a record count in the first row of the spreadsheet that throws off my automated load. I would like to find a script that I could use to automatically open the excel spreadsheet and delete the line containing the record count and then save the file. I am working in a Windows environment.

Thank you for any help with my battle!

Upvotes: 3

Views: 23066

Answers (4)

Deepak Karma
Deepak Karma

Reputation: 9

Use below script using Activex component in dts package

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("E:\Deepak\Test.xls")

i = 1

Do Until objExcel.Cells(2, 1).Value = ""

    Set objRange = objExcel.Cells(2, 1).EntireRow

   objRange.Delete

Loop

objExcel.ActiveWorkbook.Save

objExcel.ActiveWorkbook.Close

Upvotes: 2

MGZero
MGZero

Reputation: 5963

Use .NET's interop libraries to create an excel instance, open your workbook, and grab a range of cells to delete.

 Dim excel As Excel.Application
 Dim wkbk As Excel.Workbook
 Dim wksht As Excel.Worksheet

 excel = New Excel.Application
 wkbk = excel.Workbooks.Open("workbookName.xls")
 wksht = wkbk.Worksheets(1)

 wksht.Rows("1:1").Delete()

Also, please for the love of all that is good, remember to kill the excel instances when you're done with them. They'll sit in memory otherwise. Although you can kill them from the task manager, most of the time you

Upvotes: 0

jjclarkson
jjclarkson

Reputation: 5954

ExcelBook.Worksheets(1).Rows("1:1").Delete

Upvotes: 1

Ash Burlaczenko
Ash Burlaczenko

Reputation: 25465

To delete the row from the spreadsheet this something like this

objRange = objExcel.Cells($i, 1).EntireRow
objRange.Delete

Upvotes: 1

Related Questions