
Thanks: 0
Likes: 0
Dislikes: 0

Senior Member
Counting excel rows and insert value if empty.
Hello...
I'm "playing" around with the DT and Xls files, but can't get this logic to work.
i = 1000
Import the xls to DT
Read through the first column "ID" (and only column)
if it is empty, insert "i".
Export the DT to xls
Code so far:
***********************************
xlsurl = "H:myxls.xls"
SheetTab= "MyTab"
datatable.ImportSheet xlsurl, SheetTab, SheetTab
d = 2013445
For i = 1 to datatable.GetSheet(sheetTab).GetRowCount
datatable.GetSheet(sheetTab).setCurrentRow i
If datatable.GetSheet(SheetTab) = "" Then
datatable("ID", SheetTab) = d
End if
Next
datatable.ExportSheet xlsurl, SheetTab
**************
Any advice on this challenge would be much appreciated.
UPDATED: Probably need to inform that the sheet is empty with only one row "ID" in A1. What I want is start inserting data into A2, A3, A4 and so on if they are empty.(This sheet will be used by many scripts, so it will only be empty in one import.)
 How many testers do you need to switch a light bulb? None, we just report it's dark 

Junior Member
Re: Counting excel rows and insert value if empty.
Try this .......
For i = 1 to datatable.GetSheet(sheetTab).GetRowCount
datatable.GetSheet(sheetTab).setCurrentRow i
If datatable.value("ID",SheetTab) = "" Then
datatable.value("ID", SheetTab) = d
End if
Next

Advanced Member
Re: Counting excel rows and insert value if empty.
At a glance, I would think that the GetRowCount returns the number of rows that have something in them. If you use that for the upper limit of your for..next loop, then your loop won't ever reach the first blank row (which is really RowCount+1).
But, why would you even need to loop it? If every row has stuff in it and you want the first empty row, just get the rowcount and SetCurrentRow to rowcount+1.
Unless maybe you have some rows that contain data in other columns but might be empty in this column, so you need to search them one at a time and add missing values to these partial rows?
"The last 10% of any software project will take 90% of the budgeted time. The first 90% will take the other 90%"

Senior Member
Re: Counting excel rows and insert value if empty.
Hmmm.. Yes, I see there is some logic error in my description.
Basically, what I need is this.
1) I have a ID number, lets say, 1000 in my script.
2) This number I need to export to xls, in the first row that is empty under the Column A (A1 is called ID)
Tried with the amendments suggested, both return the errormessage "Wrong number of arguments, or wrong syntax of method getsheet"
*************Code***********************
xlsurl = "H:myxls.xls"
SheetTab= "MyTab"
d = 2013445
For i = 1 to datatable.GetSheet(sheetTab).GetRowCount (And +1 later, both failed)
datatable.GetSheet(sheetTab).setCurrentRow i
If datatable.GetSheet("ID" , SheetTab) = "" Then
datatable("ID", SheetTab) = d
End if
Next
AND then tried this one too.
d = 2013445
rowcount = DataTable.GetSheet(SheetTab).GetRowCount + 1
If datatable.GetSheet("ID", SheetTab) = "" Then
datatable("ID", SheetTab) = d
End If
 How many testers do you need to switch a light bulb? None, we just report it's dark 

Senior Member
Re: Counting excel rows and insert value if empty.
Could try something like this :
myarray = array(1,2,3)
nRow = datatable.GetSheet(dtlocalsheet).GetRowCount +1
Datatable.GetSheet(dtlocalsheet).setCurrentRow nRow
For i = 0 to ubound(myarray)
datatable("ID",dtlocalsheet) = myarray(i)
Datatable.GetSheet(dtlocalsheet).setCurrentRow nRow +( i+1)
Next
Alternatively, could use Excel COM ( create a dynamic named range)

Junior Member
Re: Counting excel rows and insert value if empty.
Try this
Here I have used only excelApplication with Environment variables.I haven't used DataTable.
Here is code:

Set objExcel = CreateObject("Excel.Application")
objExcel.displayAlerts = False
objExcel.visible = true
Set objWorkBook = objExcel.WorkBooks.Open( Environment.value("TestDir") &"\Results.xls")
Environment.Value("objExcel") = objExcel
Environment.Value("objWorkBook") = objWorkBook
Set objWorkSheet= objWorkBook.WorkSheets("Sample")
rowcount= objWorkSheet.rows.count
For i=2 to Rowcount
If objWorkSheet.Cells(i,1)="" then
objWorkSheet.Cells(i,1)=1000
Exit for
End if
Next
objWorkBook.save
Set objWorkSheet=Nothing
SystemUtil.CloseProcessByName "EXCEL.EXE"
In Above code I am using "Results" workbook and "Sample" sheet and "ID" column as first column.
In the same way you have to create workbook in your Test Directory, then you can run the script.
Let me know if you come across any problems.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules
