Get All Rows of Data in a Column

;Define .XLS file 
xls = "C:\Temp\Data\test.xls"
;Define the column number
column_number = 1

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objXL.DisplayAlerts = @FALSE

;Open XLS file
objWorkbooks = objXL.Workbooks
objWorkbooks.open(XLS)

;Get Active WorkBook Object
objActiveWorkbook = objXL.ActiveWorkbook
if objActiveWorkbook == 0
	Message("","No active workbook")
	ObjectClose(objXL)
	exit
endif
wrkbkname = objActiveWorkbook.Name
;Message("Active WorkBook Name",wrkbkname)

;Get Active WorkSheet Object
objActiveSheet = objXL.ActiveSheet
if objActiveSheet == 0
	Message("","No active workSheet")
	ObjectClose(objActiveWorkbook)
	ObjectClose(objXL)
	exit
endif
wrkshtname = objActiveSheet.Name
;Message("Active WorkSheet Name",wrkshtname)

objWorkSheet = objXL.Worksheets(wrkshtname)
objRows = objWorkSheet.Rows
count = objRows.Count 
For xx = 1 to count
	 objCell = objWorkSheet.Cells(xx, column_number)
	 
	 ;Check if cell is empty
	 objWSFunction = objXL.WorksheetFunction
	 rslt = objWSFunction.CountA(objCell)
    if rslt == 0 then break
	 
	 ;Select and copy cell to clipboard
	 objCell.Select
	 objCell.Copy
	 line = ClipGet()
	 Message(StrCat("Row ",xx, " Column ",column_number),line)

	 ;!! CODE TO DEAL WITH CELL DATA GOES HERE !!


	 ;clear clipboard of previous data
	 ClipPut("")
	 ObjectClose(objCell)
	 ObjectClose(objWSFunction)
Next



objWorkbooks.Close()
objXL.Quit()
ObjectClose(objRows)
ObjectClose(objWorkSheet)
ObjectClose(objActiveSheet)
ObjectClose(objActiveWorkbook)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Article ID:   W16103

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

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

Database Search

TechHome

Get All Rows of Data in a Column