Quick test professional

QTP Tips QTP codes QTP Faqs and more

DELETE ROWS FROM XL SHEET

DELETE ROWS FROM

 XL SHEET

 Public Function BIP_xlsDeleteRowRange (sSrcPath, sDestPath, sStartRow, sEndRow) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False

‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Rows(sStartRow +”:”+ sEndRow).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function 

Advertisements

February 14, 2008 - Posted by | Excel Automation | , , , , , , ,

14 Comments »

  1. Hi,
    I want to do exactly the same thing, but without using Execl Objects. I just want to delete a row directly from the dataTable into QTP

    Comment by Fredy | March 17, 2008 | Reply

  2. Fredy,
    It is not possible to delete a row or column from the Data table. you have to use Excel automation.
    Instead what you can do is you can skip the row in datatable.

    Comment by quicktestprofessional | March 18, 2008 | Reply

  3. Now I have some questions about the your code, deleting a row in excel, since I am trying to use it.

    1. Where do I put the name and location of my excel file?

    Ex: “C:\Names_places.xls” ,”Data” ,”Global”

    2. Where in the above code do I tell it exactly which row in which column to delete?

    EX: I always want to delete Row 1 in the first column, Name. I will be adding this code to the end of my script.

    Thanks!

    Steve
    Alternate email: slang46188@aol.com

    Comment by Steve Langille | May 20, 2008 | Reply

  4. Hello
    I have a question (nead help :)).
    What I wont to do is to put data from DataTable into array.
    Following code is not working
    For i=0 to UBound(tbContantArray)

    MyArray(i)=DataTable.GetSheet(“A1”)
    How can i pick walue from A1,A2,A3…
    Qtp can’t recognise “A1”.
    Help

    Comment by Aida | May 23, 2008 | Reply

  5. U r really awesome i like the way u designed the scripts . I would like to thank you very much for helping people like this. I became a fan of you as i have implemented most of things in my project which worked well. Once again thanks 🙂

    Comment by Maheshwar | April 24, 2009 | Reply

    • HI,

      Can you plz let me know what exactly i need to put in this script to delete rows –
      I tried wth this but it didnt work
      oSheet.Rows(A1 +”:”+ B2).Delete

      Comment by Vijay | December 8, 2009 | Reply

      • people, I got it working.Replaced A1 and B1 with integer values enclosed with quotes.
        oSheet.Rows(“2″ +”:”+ “3”).Delete

        Comment by Vijay | December 9, 2009

    • Hi,

      For deleting rows we should use

      objDriverSheet2.Rows(“1″ +”:”+ “100”).Delete

      For deleting columns we should use

      objDriverSheet2.Columns(“A”+ “:” + “Z”).Delete

      Comment by Baranitharan | March 9, 2010 | Reply

  6. How do you delete rows which contains particular Text ?

    Comment by goody | March 9, 2010 | Reply

  7. How do you delete rows which contains particular Text?

    Comment by goody | March 9, 2010 | Reply

  8. Thanks lot it Helped me, I added last two lines , this will surpress invalid result error message pop up.

    ‘**********************************************************************
    ‘ Visual Basic ActiveX Script
    ‘************************************************************************
    Function Main()
    Dim oExcel
    Dim oBook
    Dim oSheet
    Set oExcel = CreateObject(“Excel.Application”)
    oExcel.DisplayAlerts = False
    Set oBook = oExcel.Workbooks.Open(“C:\Documents and Settings\mshastk\Desktop\TLM_04_09_2012.xls”)
    Set oSheet = oExcel.Activesheet
    oSheet.Rows(“2:65536”).Delete
    oBook.SaveAs (“C:\Documents and Settings\mshastk\Desktop\TLM_04_09_2012.xls”)
    oExcel.Workbooks.Close()
    oExcel.Quit
    Main = DTSTaskExecResult_Success
    End Function

    Comment by Kotresha A M S | September 10, 2012 | Reply

  9. the above code will work only when we convert the variables to String
    oSheet.Rows(CStr(sStartRow) +”:”+ CStr(sEndRow)).Delete

    Comment by Abijith | April 23, 2013 | Reply

  10. hieee Mohan , how to Delete single row of excel
    case::
    iI searching keyword execute collom =yes in excelsheet1 then write nameofModule in other excelsheet2 if execute = no then i want delete empty row from excelsheet and continue for next yes
    excelSheet1
    ModuleName Excecute

    Test011 yes
    Test012 no
    Test013 yes
    Test014 yes so Excel sheet having only 3 entry of yes so i m trying to delete Perticular row by taking runtime rowNo but it’s not working v used fornext & if else

    Comment by amol z | June 25, 2013 | Reply

  11. how to delete coloumn in excel sheet

    Comment by v.poorna | February 11, 2015 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: