Forum Discussion

dvox's avatar
dvox
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?



Thanks,

dvox

6 Replies

Sort By
  • AlexanderM's avatar
    AlexanderM
    Staff
    14 years ago

    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

      wkbExported.ActiveSheet.Range("2:2").Delete

      wkbExported.Save

      wkbExported.Close

      Set wkbExported = Nothing

      appExcel.Quit

      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
      cauline
      Contributor
      8 years ago

      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!!!!
          
     excelDataObj.Next();
     book.Save(); 
  }
  
  app.Quit(); 
}
      • Colin_McCrae's avatar
        Colin_McCrae
        Community Hero
        8 years ago

        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?