Jump to content
Sign in to follow this  
lolipop

Excel assistance on reading column for specify keyword

Recommended Posts

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.

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

#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 by lolipop

Share this post


Link to post
Share on other sites
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.

Posted Image

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×