Handle Errors in Cells

 Keywords: XLS XLSX Excel Cell N/A #N/A ERROR VT_ERROR

Question:

Some of my Excel cells have #N/A in them. I cannot figure out a way to prevent them fromt causing the program to stop with an Error as I am looping through a Column and testing the cells to see if any are blank.
Example:
Cell C7 contains: #N/A
I am looping a variable "Row" from 1 to 10. When "Row" reaches 7 the following line causes an error:
Var1 = WS1.cells(Row, "C").value
After the first line is executed, in the debug Watch window, Var1 shows as "Type: VT ERROR with no Value assigned.Then when ther next line is executed I get the following fatal error:
Error 3057: Variable could not be converted to a valid Number.
Any way to deal with this? I can't figure out how to either mark the cell with something else or simply skip that cell.

Answer:

I recommend calling ObjectTypeGet on the cell value . If it returns ERROR then handle for it.

For Example:



; Ignore all cell with errors

objXLS = ObjectCreate("Excel.Application")
objXLS.visible = @TRUE
objWrkbks = objXLS.workbooks
;   specify the workbook to look thru...
excelfile = "D:\temp\DATA\NA_Sample.xlsx"
objWrkbks.Open (excelfile)

totalcols = 3
totalrows = 5

For row = 1 To totalrows
   For col = 1 To totalcols
      actSheet = objXLS.activesheet
      objCell = actSheet.Cells(row,col)
      val = objCell.value
      type = ObjectTypeGet(val)
      If type == 'ERROR'
         Message('row = ': row: ' col = ' : col, 'SKIPPED due to error in cell')
         Continue ; skip all cells with errors
      EndIf
      Message('row = ': row: ' col = ' : col, val)
   Next
Next


;Clean up
objXLS.quit
objCell = 0
actSheet = 0
objWrkbks = 0
objXLS = 0
Exit

Article ID:   W18110

Filename:   Handle Errors in Cells.txt

File Created: 2009:10:07:11:43:08

Last Updated: 2009:10:07:11:43:08

Database Search

TechHome