Get Data Row by Row in Excel

 Keywords: grab Data Row by Row in Excel

Question:

I see that it can open up the excel sheet, and read the sheet, but does it read individual cells or can it read a whole row. i need it to read the whole row. i have 64 accounts that it has to read. that means that it has three columns each having an entity, username, and password. Can you help me figure this out?

Answer

Here is some code that reads the data row by row out of your Excel file.

;Define .XLS file 
xls = "C:\Temp\Data\test.csv"

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
	 objRow = objWorkSheet.Rows(xx)
	 
	 ;Check if row is empty
	 objWSFunction = objXL.WorksheetFunction
	 rslt = objWSFunction.CountA(objRow)
    if rslt == 0 then break
	 
	 ;Select and copy row to clipboard
	 objRow.Select
	 objRow.Copy
	 line = ClipGet()
	 Message(StrCat("Row ",xx),line)

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


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



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

Article ID:   W15633

File Created: 2003:05:13:11:29:20

Last Updated: 2003:05:13:11:29:20

Database Search

TechHome

Get Data Row by Row in Excel

Question:

Answer