Excel assistance on reading column for specify keyword

[lolipop]

Hi,

Anyone have assist for me on the following?

What I need to achieve is to read a specific column (example, column L) for a specific keyword (example, "SIGN") and then get the corresponding row data (example, Keyword "SIGN" can be found in column L row 2, row 6 and row 10. Then I need to grab the data from the whole row 2, row 6 and row 10.)

I have read the example _ExcelReadArray but it I can't seems to target only specific keyword. Also, I only require the information from the correct row not all the rows. Any advise or example you guys can point me to is much appreciated. Thanks.

[water]

Will post an example as soon as i am at my windows pc again.

[lolipop]

Hi water, Thanks for the response. I have managed to write a sample based on some of your previous sample in the forum and this is what I have come up with.

expandcollapse popup

#include 
#include 
Global $sFilePath = @ScriptDir & "\temp.xls"
$ConcatVal = "Sign-In"
$oExcel = _ExcelBookOpen($sFilePath)
ExcelFindLastRow()
$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "L1:L"&$iLastRow)
Global $TotalSignIn = $aFound[0][0]
$i = 1
While $i <= $TotalSignIn
$SelectedRow = $aFound[$i][3]
Global $test = _ExcelReadArray($oExcel, $SelectedRow, 1, 13, 0)
_ArrayDisplay($test)
$i = $i + 1
WEnd

Func ExcelFindLastRow()
$oExcel1 = ObjGet($sFilePath, "Excel.Application")
$oSheet = $oExcel1.ActiveSheet
Global $iLastRow = $oSheet.Cells.Find('*', $oSheet.Cells(1, 1), Default, Default, 1, 2).Row
EndFunc

;===============================================================================
;
; Description: Finds all instances of a string in a range and returns their addresses as a two dimensional array.
; Syntax: _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
; $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _
; $fMatchFormat = False)
; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
; $sFindWhat - The string to search for
; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
; $iColStart - The starting column for the number format(left) (default=1)
; $iRowEnd - The ending row for the number format (bottom) (default=1)
; $iColEnd - The ending column for the number format (right) (default=1)
; $iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)
; $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
; $fMatchCase - Specify whether case should match in search (True or False) (default=False)
; $fMatchFormat - Specify whether cell formatting should match in search (True, False, or empty string) (default=empty string=do not use parameter)
; Requirement(s): AutoIt Beta 3.2.1.12
; Return Value(s): On Success - Returns a two dimensional array with addresses of matching cells. If no matches found, returns null string
; $array[0][0] - The number of found cells
; $array[x][0] - The address of found cell x in A1 format
; $array[x][1] - The address of found cell x in R1C1 format
; $array[x][2] - The row of found cell x as an integer
; $array[x][3] - The column of found cell x as an integer
; On Failure - Returns 0 and sets @error on errors:
; @error=1 - Specified object does not exist
; @error=2 - Starting row or column invalid
; @extended=0 - Starting row invalid
; @extended=1 - Starting column invalid
; @error=3 - Ending row or column invalid
; @extended=0 - Ending row invalid
; @extended=1 - Ending column invalid
; @error=4 - Data type parameter invalid
; @error=5 - Whole or part parameter invalid
; Author(s): SEO  and MRDev, many thanks to DaLiMan
; Note(s): None
;
;===============================================================================
Func _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "")
Local $iCount, $sA1, $sR1C1, $sFound, $Temp1, $Temp2, $sFirst
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iDataType <> 0 Then
If $iDataType <> -4163 Or $iDataType <> -4123 Or $iDataType <> -4144 Then
Return SetError(4, 0, 0)
EndIf
EndIf
If $iWholeOrPart < 1 Or $iWholeOrPart > 2 Then Return SetError(5, 0, 0)
If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
If $iColStart < 1 Then Return SetError(2, 1, 0)
If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
$oExcel.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Select
Else
$oExcel.Range($sRangeOrRowStart).Select
EndIf
If $iDataType = 0 Then
If $fMatchFormat = "" Then
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default)
Else
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
EndIf
Else
If $fMatchFormat = "" Then
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default)
Else
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, $iDataType, $iWholeOrPart, Default, Default, $fMatchCase, Default, $fMatchFormat)
EndIf
EndIf
If IsObj($oFound) Then
$oFound.Activate
Else
Local $aFound[1][1]
$aFound[0][0] = 0
Return $aFound
EndIf
While 1
If $iCount > 0 And $iCount < 2 Then $sFirst = $sA1
$Temp1 = $oExcel.ActiveCell.Address
$Temp2 = $oExcel.ActiveCell.Address(True, True, $xlR1C1)
If $Temp1 = $sFirst Then ExitLoop
If $iCount > 0 Then
$sA1 = $sA1 & "*" & $Temp1
$sR1C1 = $sR1C1 & "*" & $Temp2
Else
$sA1 = $Temp1
$sR1C1 = $Temp2
EndIf
$iCount += 1
$oExcel.Selection.FindNext($oExcel.ActiveCell).Activate
WEnd
Local $aFound[$iCount + 1][4]
$sA1 = StringReplace($sA1, "$", "")
Local $aA1 = StringSplit($sA1, "*")
Local $aR1C1 = StringSplit($sR1C1, "*")
$aFound[0][0] = $iCount
For $xx = 1 To $iCount
$aFound[$xx][0] = $aA1[$xx]
$aFound[$xx][1] = $aR1C1[$xx]
$Temp1 = StringRegExp($aR1C1[$xx], "[RZ]([^CS]*)[CS](.*)",3)
$aFound[$xx][2] = Number($Temp1[1])
$aFound[$xx][3] = Number($Temp1[0])
Next
Return $aFound
EndFunc ;==>_ExcelFindInRange

One of the function at the bottom I took it from excelcom udf.

Edited March 8, 2013 by lolipop

[lolipop]

By the way, I trying to achieve something similar to the picture. Not sure if you can advise me with sample or reference.

Basically I'm trying to make a sign in-out system. The 4 entry in the picture are data retrieve from an excel. The excel basically contain all the sign in name and information. I need to retrieve all the sign in excel entry and display their data with an sign out button so that people can use it to sign out of the system. Once click sign out, it will send a status update to the excel spreadsheet and update. I have problem coming up with the sign out button as it need to be dynamic. Meaning, if there are only 2 entry, then only 2 sign out button, 5 entry then 5 sign out button, etc....

If anyone can advise me on this it will be great.