Forum Discussion

dvox's avatar
Occasional Contributor
14 years ago

delete excel row

I am using excel sheet for data driven testing. The input values are being extracted from excel and used for web testing in my scripts.

But once the values are read from excel and placed on the webpage, I would like to delete that row from excel sheet to prevent any future use of the current data. 

I am not sure if TC8 would support this delete row operation in excel. Could you please point me to a direction to implement this in my script?



6 Replies

  • Hi Kanth,

    You can work with Excel via COM to accomplish your task:

    Sub DeleteExcelRow

      Dim appExcel

      Dim wkbExported

      Dim strFile

      Set appExcel = Sys.OLEObject("Excel.Application")

      appExcel.Visible = True

      Set wkbExported = appExcel.Workbooks.Open("C:\Test.xls")

      ' Deletes the second row and moves the rows below one row up




      Set wkbExported = Nothing


      Set appExcel = Nothing

    End Sub

    If you want just to clear the contents of the row without moving other rows, use 'wkbExported.ActiveSheet.Range("2:2").ClearContents' instead of 'wkbExported.ActiveSheet.Range("2:2").Delete'.

    For more information, refer to the 'Excel Object Model Overview' MSDN Library article.
    • cauline's avatar

      I am trying to do this in JavaScript.  Does anyone have a sample of this?


      function RandomItems()
        // Randomize the Item Master Item list and Qty's
        var item, minQty;
        var excelDataObj = Project.Variables.ITEM_DATA;
        var fileName = "C:\\TestData\\ItemMasterList.xlsx"; 
        var sheetName = "ItemMasterList"; 
        var app = Sys.OleObject("Excel.Application");
        var book = app.Workbooks.Open(fileName); 
        var sheet = book.Sheets.Item(sheetName); 
        app.DisplayAlerts = false; 
        rowCount = 10;
        // Loop through a non-Randmomized Table and delete record from Source
        for(var row = 1; row < rowCount ; row++) 
           item   = excelDataObj.Value("ITEM_NUMBER");
           minQty = excelDataObj.Value("CUST_MIN_ORDER_QTY");
           Log.Message("List Num: " + row + ", " +  "Item #: " + item + ", " + "Min Qty: " + minQty);
           book.ActiveSheet.Range("2:2").Delete; // THIS DOESN'T WORK!!!!
      • Colin_McCrae's avatar
        Community Hero

        This is a 6 year old thread, relating to a massively out of date version of TestComplete. You would have been better of simply posting a new question!


        However. That aside ....


        I'm not a JavaScript guy. But quickly looking at your code, a few things jump out at me ....


        1. You are taking in your data from a Project Variable (a dictionary object?). They come from this within your loop, not from the Excel sheet?

        2. Your loop is hard coded to 10. It has no idea how long your spreadsheet actually is! (You can check this!)

        3. I'm not sure if JavaScript needs values to be converted to string before posting to log? You have a couple of data items you're trying to post which look like they could be numeric?

        4. You apply "sheet" to a variable, but never use it. Instead you refer to the "book" and it's "active sheet". And this is just for the delete. You never read anything from it.

        5. Each loop you always try and delete the second row? This is going to wreck the whole thing! Each loop will delete a row. And your loop is hardcoded to 10. So on loop 10, it should be looking at row 10. But if you have deleted rows as you go, you no longer have a row 10. Well, you do. But it will be empty. But you never actually read a row anyway!

        6. You save after each loop iteration. Do you need to? You could just save once you're finished.


        This code doesn't seem coherent. And doesn't seem to quite do anything right? I'm not sure what you're trying to achieve here?


        Can you provide a little more detail on that front?