delete excel row

[dvox]

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

[AlexanderM]

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]

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]

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?

[cauline]

Thank you for your unhelpful critique of my code!  If I wanted a tongue-lashing, I would have gone to another thread.  All I am simply trying to do is delete a row from an Excel Spreadsheet using JavaScript.  I don't really need the editorial.  This was just a sample mock-up of code to attempt to delete a row in Excel using the COM driver.

[Colin_McCrae]

You said it doesn't work.

You posted your code.

What do you expect!

The "Range" and "Delete" commands to remove a row work perfectly. I've just tried them. I didn't see any problem with that line. But did see plenty other problems and inconsistencies in the code.

But if we're going to jump down each others throats .....

Posting a load of bad code. Along with "it doesn't work" ... is not very helpful. Stating where it actually fell over, and what the error message was, would be a lot more helpful than adding a comment against one line saying "IT DOESN'T WORK!". And it does work. As I say, I just tried it myself. Worked perfectly.

You don't have to use "Range" either. You could also use "Rows".

First google result searching for your problem?

http://stackoverflow.com/questions/2160170/delete-rows-in-excel-sheet-using-javascript

However, I suspect the problem is that the "Delete" method needs "()" added in JavaScript. (I think - as I said, I'm not a JavaScript user ....)

[tristaanogre]

---

@Colin_McCrae wrote:

However, I suspect the problem is that the "Delete" method needs "()" added in JavaScript. (I think - as I said, I'm not a JavaScript user ....)

---

Correct... Method calls require the () in JavaScript so, ultimately, you should get a JavaScript error on that line.

If that's all you're looking for, then there's the answer to your question.  There is the potential of other issues happening once that is corrected, however, if the code is executed as is.  I'll give the benefit of the doubt that you are a sufficient JavaScript code writer and can handle those problems as they arise.