I am attempting to use this line of code from a very old post:
oWorkbook.ActiveSheet.Rows(2).Delete;
This doesn't seem to work. The Test Complete type ahead only contains Cell, CelCount and Row Count. I simply want to delete one row or a range of rows from an excel file.
Any assistance is appreciated.
Hi,
> Is there a paste option
For example:
https://www.google.com/search?q=excel+vbscript+copy+paste+data
And the whole Excel COM documentation.
Champion Level 3What is it that you are trying to do with the data that ends up with you deleting it? Best practice is to not delete your test data. Perhaps we can find you another way to run the test without doing that.
Actually the task is a little bit trickier then that. I have three excel files. I need to manipulate each one, for example
Excel1 - delete the first five rows and the last row of data, then copy all data in three columns and paste into Excel3.
Excel 2 - copy data from three columns and then paste into Excel3
This kind of task was doable in UFT, but now that I am using Test Complete, it's a lot different.
Any help is appreciated.
Champion Level 3How do you get this data into Excel1 and Excel2 in the first place?
Champion Level 3Hi,
> This doesn't seem to work
If https://stackoverflow.com/questions/31878154/delete-rows-x-to-y-in-on-a-specific-excel-worksheet does not help, could you elaborate what does 'This doesn't seem to work' mean?
Alex,
I found that I need to access the excel com directly to at least delete a row. using this
wkbExported = appExcel.Workbooks.Open("\\\\network\\\\folder1\\\\folder2\\\\folder3\\\\datafile_20210113.csv");
wkbExported.ActiveSheet.Range("1:1").Delete();
or to delete columns using this:
wkbExported.ActiveSheet.Range("J:J").Delete();
Is there a paste option when copying from one file to another? I need to paste a large amount of data from one spreadsheet to another. Please advise.
Champion Level 3Hi,
> Is there a paste option
For example:
https://www.google.com/search?q=excel+vbscript+copy+paste+data
And the whole Excel COM documentation.
I have some simple fixes using which you can easily delete blank rows in Excel.
1. Choose the data set > press Ctrl + F keys to open the Find and Replace dialog.
2. Hit the “Options” button > choose “Values” from “Look in” And let the “Find What” text box blank > click “Find All”.
3. Dialog box of “Find and Replace” will highlight entire blank cells.
4. Click any one record > press Ctrl + A for selecting the entire blank cells.
5. Close the dialog box of “Find and Replace” and without clicking anywhere else go to Home > Delete > Delete Rows.
6. Doing this will delete the entire selected rows.
For more tricks like this, check out this link.
source: www.repairmsexcel.com/blog/delete-blank-rows-in-excel
