Display Column and Row Data

Question:

I need to pen a single sheet excel file and display column B as an askitemlist. Select one entry. Then I want to read the line of column a, b, c, d of the corresponding selection.

Answer:

;Define .XLS file
;xls = "C:\Temp\Data\test.xls"
title = "Excel Files"
path = DirScript()
mask = "XLS Files (*.xls)|*.xls"
xls = AskFilename(title, path, mask, "", 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")
   objXL = 0
   Exit
EndIf
wrkbkname = objActiveWorkbook.Name
;Message("Active WorkBook Name",wrkbkname)

;Get Active WorkSheet Object
objActiveSheet = objXL.ActiveSheet
If objActiveSheet == 0
   Message("","No active workSheet")
   objActiveWorkbook = 0
   objXL = 0
   Exit
EndIf
wrkshtname = objActiveSheet.Name
;Message("Active WorkSheet Name",wrkshtname)

objWorkSheet = objXL.Worksheets(wrkshtname)
objcols = objWorkSheet.Columns("B:B")
objcols.Select
objcols.Copy
data = ClipGet()
;Message("Column B",data)
data = StrReplace( data, @CRLF, @LF )
choice = AskItemlist("Pick one", data, @LF, @SORTED, @SINGLE, @FALSE)

;Determine the selected cell.
row = ItemLocate( choice, data, @LF )

objRow = objWorkSheet.Rows(row)
;Select and copy row to clipboard
objRow.Select
objRow.Copy
rowdata = ClipGet()
Message( "Row ":row , rowdata )


objWorkbooks.Close()
objXL.Quit()
objcols = 0
objWorkSheet = 0
objActiveSheet = 0
objActiveWorkbook = 0
objWorkbooks = 0
objXL = 0

Article ID:   W17450

File Created: 2008:04:10:15:10:36

Last Updated: 2008:04:10:15:10:36

Database Search

TechHome

Display Column and Row Data

Question:

Answer: