Check for Empty Cell in Excel

Question:

I have a simple winbatch script that reads an Excel spreadsheet that is currently open, and returns the values, one line at a time, until it hits the last line of data. The last line of data is one that has no value in column "A".
However, no matter what I do, the script always returns the value "0" if the cell is empty. My problem is "0" can be a valid entry, so I cannot use this.
I have tried setting the cell format to TEXT with no luck.
Basically, for each row in an open Excel spreadsheet, it waits for a specific dialog box to appear, fills in the dialog with the spreadsheet values and processes it.
Here is a sample script to read (an empty) cell "A1" in a currently open Excel spreadsheet.
objXL = ObjectAccess("Excel.Application", @True) Book = objXL.ActiveWorkBook Sheet = Book.ActiveSheet Cell = Sheet.Range("A1") Cell.Activate cv = Cell.Value ObjectClose(Cell) ObjectClose(Sheet) ObjectClose(Book) ObjectClose(objXL) Message("Result ...", "Value retrieved from Cell A1 was: %cv%")

The code works fine, but I just cannot get the loop to stop at a row that contains an empty cell in column A.

Any ideas on how to get the script to return an empty value, a space, or anything other than a "0" would be greatly appreciated.

Answer:

This just looks at the first column of each cell in a table it increments the row counter and checks for a blank cell. If the cell is blank it ends the loop...

See if the column you're checking has numeric formatting or something, anyway this works...

:runreports
;   Start excel here
excelxls = objectopen("Excel.Application")
excelxls.visible = @true
rptxls = excelxls.workbooks
;   specify the workbook to look thru...
excelfile = "C:\Data\Excel\HTML IE DHTML.xls"
rptxls.open (excelfile)

col = 1
row = 2  ; <--- start at row 2

while @true
      actSheet = excelxls.activesheet
      thecell = actSheet.Cells(row,col)      ; <--- increment this each time...
      currentrow = thecell.row               ; <--- this will tell you the current row
;      message(currentrow, thecell.value)       ;      the cursor is on...
      if thecell.text == "" then break      ; <--- check the TEXT property -- 
                                          ; when you hit an empty cell end the loop...
      row = row + 1
endwhile

message("Debug", row)   ; <--- last row with data...

;Clean up
excelxls.quit
objectclose(thecell)
objectclose(actSheet)
objectclose(rptxls)
objectclose(excelxls)
return

User Reply

Yes, looking at TEXT attributes is the way to go...

Article ID:   W16102

File Created: 2004:03:30:15:42:52

Last Updated: 2004:03:30:15:42:52

Database Search

TechHome