Its not the best way to check the blank value in a mandatory row and stop the process while importing data from excel file.
You can get the active rows in an excel file by using SpecialCell function.
The syntax for the SpecialCells Method is;
expression.SpecialCells(Type, Value)
Where "expression" must be a Range Object. For example Range("A1:C100"), ActiveSheet.UsedRange etc.
Type=XlCellType and can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells
These arguments cannot be added together to return more than one XlCellType.
Value=XlSpecialCellsValue and can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues
These arguments can be added together to return more than one XlSpecialCellsValue.
The lotus script code to get the active rows count.
Set varExcel = CreateObject( "Excel.Application" )
varExcel.Visible = False ' Making the selected Excel invisible
varExcel.Workbooks.Open xlFilePath '// Open the Excel file
Set xlWorkbook = varExcel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
xlSheet.Cells.SpecialCells(11).Activate
xlsRows = varExcel.ActiveWindow.ActiveCell.Row
xlsRows variable gives the count of the active rows in an excel file.
