Find location of cell that contains specific text

[water]

On 6.7.2016 at 3:04 AM, Laurynelis said:

Btw, using this part of you example, how would you search in specific sheet of excel workbook, instead of all sheets?

Simply set parameter $vRange to the range to be searched e.g. $oWorkbook.Worksheets('SpecificSheetName').UsedRange

Edited July 7, 2016 by water

[water]

On 4.7.2016 at 10:07 PM, Laurynelis said:

"$oDocument.Find(What:="Label")" simply wont do it..

That's because AutoIt does not support named parameters.

[water]

On 5.7.2016 at 2:34 PM, MichaelHB said:

@water, i dont have a win 10 to test but i think Laurynelis problem could be related to the "$sCLSID_Workbook" (_Excel_BookAttach), for some reason it get stuck in the While loop (it should gives an error when it reaches an invalid instance). I dont see any problems in my win 7.

I do not think it is CLSID related because in the OPs first post he didn't use _Excel_BookAttach.

[MichaelHB]

True, and both functions (_Excel_Open / _Excel_BookOpen) should not hang the script. I cant reproduce the OP issue, but when he used the ObjGet by path it worked for him. The difference in both cases were ObjCreate and ObjGet by class and the CLSID of the Workbook in the _Excel_BookAttach function and those should not freeze the script .

[Laurynelis]

7 hours ago, water said:

Simply set parameter $vRange to the range to be searched e.g. $oWorkbook.Worksheets('SpecificSheetName').UsedRange

Works wonders!
Thank you
 

[water]

[Laurynelis]

On 7/7/2016 at 4:30 PM, MichaelHB said:

True, and both functions (_Excel_Open / _Excel_BookOpen) should not hang the script. I cant reproduce the OP issue, but when he used the ObjGet by path it worked for him. The difference in both cases were ObjCreate and ObjGet by class and the CLSID of the Workbook in the _Excel_BookAttach function and those should not freeze the script .

230 script lines later... got some ideas on why i might having problems, so i thought i ask and consult with you guys

1. I have 64bit excel and my AutoIT is 32bit, could this be a problem with that excel udf?
2. Will my built application (exe format) with excel functions work on computer who has no excel at all? Has other .xlsx reader but not MS Excel.
3. Can multiple excel workbooks be loaded in application at once? Having some problems once i load multiple excels via ObjGet($sFileName) to separate variables

[water]

1. Compile your script as 64 bit and check if it works then
2. No. Your script always calls functions provided by Excel. No Excel - no script.
   There are UDFs available that provided limited access to Excel workbooks without Excel being installed
3. Yes. I suggest to use the UDF to work with multiple workbooks.

[nitekram]

Sorry to bring up an old post, but I am having a similar issue...and do not understand, why the example will not work?  _Excel_RangeFind - should I not be able to replace the file name with my file name, and the search word with my search word and it work?  I get the following error message:

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (670) : ==> The requested action with this object has failed.:
$oSheet = $oWorkbook.Sheets($iIndexSheets)
$oSheet = $oWorkbook^ ERROR

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\MOVIES - NORTH -june 2017.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Find all occurrences of value "37000" (partial match)
; *****************************************************************************
Local $aResult = _Excel_RangeFind($oWorkbook, "dvd")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'dvd' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

 

 

When I use the follow code from this thread...it works, but it does not find the first instance, no matter if I add blank cells in front of it or not...

expandcollapse popup

; genes movies

;"\MOVIES - NORTH -june 2017.xls"





#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>

Local $sValueToFind = "DVD"

Local $sFileOpenDialog = FileOpenDialog("Please select the Excel file", "", "Excel (*.xlsx;*.xlsm;*.xlsb;*.xltx;*.xltm;*.xls;*.xlt;*.xml)", BitOR($FD_FILEMUSTEXIST, $FD_PATHMUSTEXIST))
If @error Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were selected.")
ConsoleWrite($sFileOpenDialog & @CRLF)

ShellExecute($sFileOpenDialog, "", "", "open", @SW_MAXIMIZE)
If @error Then Exit MsgBox(0, "ERROR", "Error in ShellExecute")

Local $aExcelFileName = StringRegExp($sFileOpenDialog, '[^\\]*$', 1)
If Not IsArray($aExcelFileName) Then Exit MsgBox(0, "ERROR", "Error in StringRegExp" & @CRLF & "@error: " & @error)

If Not WinWait($aExcelFileName[0], "", 15) Then Exit MsgBox(0, "ERROR", "Error in WinWait")
Sleep(3000)

Local $oWorkbook = _Excel_BookAttach($aExcelFileName[0], "FileName")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "ERROR", "Error opening workbook '" & $sFileOpenDialog & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $aResult = __Excel_RangeFind($oWorkbook, $sValueToFind)
If Not @error And IsArray($aResult) Then
    _ArrayDisplay($aResult);, "Excel UDF: _Excel_RangeFind Example", "", 0, "|", "Sheet|Cell|Value|Formula")
    Exit
Else
    Exit MsgBox(0, "Error", "The value/text (" & $sValueToFind & ") could not be found.")
EndIf

Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Activesheet.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][4], $iIndex = 0, $iIndexSheets = 1, $iNumberOfSheets = $oWorkbook.Worksheets.Count
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Address
                $aResult[$iIndex][2] = $oMatch.Value
                $aResult[$iIndex][3] = $oMatch.Formula
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][4]
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        If $iIndexSheets > $iNumberOfSheets Then ExitLoop
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex][4]
    Return $aResult
EndFunc

Exit

 

 

I am trying to find the word DVD in any cell on the worksheet...but having trouble with making it work?

MOVIES - NORTH -june 2017.xls

Edited June 3, 2017 by nitekram

[water]

Which version of AutoIt do you run?

[nitekram]

AutoIt Version # 3.3.14.2

[water]

Did you apply the fixes as described here?

 

[nitekram]

I now have lol...sorry, and now that part is no longer crashing, thanks.

 

I still have the same issue with the attached excel doc...with it not finding the first instance - any thoughts?

[water]

Will check as soon as I return to my office on Tuesday.

[water]

The Excel UDF finds the first instance as well - unfortunately it doesn't display it at the top of the Array but at the very end (row 785).
I'm not sure if this is a problem with MS Excel or the Excel UDF right now.

[nitekram]

On 6/5/2017 at 7:59 AM, water said:

The Excel UDF finds the first instance as well - unfortunately it doesn't display it at the top of the Array but at the very end (row 785).
I'm not sure if this is a problem with MS Excel or the Excel UDF right now.

I moved that instance (first cell) to another cell further down and it did not set it to the bottom when it showed it in the array, and then I moved it one (search cell) up and it was one index up when I ran it again - figured that might drill it down a bit?

[water]

What i think:
Seems Excel starts to search in the cell after the topmost cell. So it finds the first occurrence in row two even if there is an occurrence in row 1. When it reaches the last row of the sheet it wraps and searches row 1 and hence adds this occurrence to the end of the resulting array.

But I need to do some more testing.

[nitekram]

On 6/6/2017 at 10:34 AM, water said:

But I need to do some more testing.

Is there a work around that you can think of...I mean if the file is not seen during the evaluation from the end user?