Find location of cell that contains specific text

[Laurynelis]

Hello guys. I've been reading this forum for over a year now and learned tons of stuff from you all. I could not find one specific problem solution so decided to join and ask

What I'm trying to do is find a cell location in excel sheet that contains certain text.

What i have:

Func LoadGBMP()
Local $FileList = _FileListToArray ("Master")
Local $sFileName = @ScriptDir & "\Master\" & $FileList[1]
Local $oExcelDoc = ObjGet($sFileName)
Local $oDocument = $oExcelDoc.Worksheets("Attributes")
ConsoleWrite($oDocument.range("A1").value) ;check if everything is working so far
EndFunc

I think it could be done with arrays - comparing each array value to a searching text, but I hope it could be done by a simple method that I don't know. Yet.

Something like "$CellLocation = $oExcelDoc.Worksheets("Attributes").findrange(lol)"

 

=================

Second question: Where do you guys find documentation for generic excel functions, like ".worksheets.", ".range.", "value." etc? I found these in example scripts but they are working without any library other than <AutoItConstants.au3> which does not have that info.

Thanks in advance!

[water]

Did you have a look at the Excel UDF that comes with AutoIt?
Function _Excel_RangeFind should do what you need.

[MichaelHB]

Hi Laurynelis,

Also take a look in the Excel wiki (click on water signature "Reveal hidden contents") and also look at the examples. This will help you alot with excel automation. And for your second question, you can start here https://msdn.microsoft.com/en-us/library/office/ee861528.aspx

[Laurynelis]

Thanks for suggestions!

 

1 hour ago, water said:

Did you have a look at the Excel UDF that comes with AutoIt?
Function _Excel_RangeFind should do what you need.

I did. It gave me errors at first, so I went with another sample which does not use excel UDF. I think I'll reconsider.

 

1 hour ago, MichaelHB said:

And for your second question, you can start here https://msdn.microsoft.com/en-us/library/office/ee861528.aspx

Found some good information there, looks like this should work "Set m_rnFind = .Find(What:="X", LookIn:=xlFormulas)", but I was not able to make it work.

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

Going for Excel UDF.

[water]

When you get an error, please post the full message so we can see what goes wrong.
After calling a function check the variable @error as described in the help file.

[Laurynelis]

34 minutes ago, water said:

When you get an error, please post the full message so we can see what goes wrong.
After calling a function check the variable @error as described in the help file.

I took the first example of "_Excel_RangeFind" function in documentation, changed excel file to my own, and application freezes without any errors.

#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 & "\test.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\test.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Find all occurrences of value "37000" (partial match)
; *****************************************************************************
Local $aResult = _Excel_RangeFind($oWorkbook, "lol")
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 '37000' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult)

Stopping execution brings the following:

Quote

>Process failed to respond; forcing abrupt termination...
>Exit code: 1    Time: 198.2

 

test.xlsx

[water]

Which version of AutoIt do you run?

[Laurynelis]

SciTE-Lite  
Version 3.5.4

[Laurynelis]

Sorry, 3.3.14.2

[water]

I mean the version of AutoIt, not the SciTE version.
Could you please run

MsgBox(0, "", @AutoItVersion)

 

[Laurynelis]

1 minute ago, water said:

I mean the version of AutoIt, not the SciTE version.
Could you please run

MsgBox(0, "", @AutoItVersion)

Sorry, 3.3.14.2

 

[water]

Never had or heard of a problem with this function looping until y ou cancel the script.
Will check as soon as I return to my office on Thursday.

[MichaelHB]

@Laurynelis

Try this example with your test.xlsx file, this code works for me.

expandcollapse popup

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

Local $sValueToFind = "lol"

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

 

[Laurynelis]

10 hours ago, MichaelHB said:

@Laurynelis

Try this example with your test.xlsx file, this code works for me.

#include <MsgBoxConstants.au3>
....

 

1. I select the file test.xlsx
2. System opens the file
3. Application stops

After I stop application:

>Process failed to respond; forcing abrupt termination...
>Exit code: 1    Time: 177.6

 

Something is not right here. Maybe its my Windows10, or maybe i should reinstall autoit.

I'll try these scripts on other computers.

[MichaelHB]

Try this:

expandcollapse popup

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

Local $sValueToFind = "lol"

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 = ObjGet($sFileOpenDialog)
If @error Or Not IsObj($oWorkbook) Then Exit MsgBox($MB_SYSTEMMODAL, "ERROR", "Error in ObjGet")

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

 

[Laurynelis]

This one works, and it is exactly what I needed.

Thank you very much!

[MichaelHB]

Laurynelis,

Thanks for let me know.  Glad that solved your problem.

 

@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.

[Laurynelis]

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

Local $sValueToFind = "lol"

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 = ObjGet($sFileOpenDialog)
If @error Or Not IsObj($oWorkbook) Then Exit MsgBox($MB_SYSTEMMODAL, "ERROR", "Error in ObjGet")

Local $aResult = __Excel_RangeFind($oWorkbook, $sValueToFind)

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

Tried using
Local $oWorkbook = ObjGet($sFileOpenDialog)
Local $oWorksheet = $oWorkbook.Worksheets('SpecificSheetName') but it does not work.

Also tried playing with _Excel_RangeFind($oWorkbook, $sValueToFind, 'SpecificSheetName')

Sorry for bothering again

[MichaelHB]

Laurynelis,

The array returned by the function ($aResult) already gives you the sheet name in its first column, all you need is to first the results you want. If you want restrict the search to a specific sheet you will need to modify the function. I belive that is easier to filter the array as its gives the information that you want.

[water]

13 hours ago, Laurynelis said:

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

Tried using
Local $oWorkbook = ObjGet($sFileOpenDialog)
Local $oWorksheet = $oWorkbook.Worksheets('SpecificSheetName') but it does not work.

I wouldn't mix the Excel UDF, ShellExecute and direkt Access to the Excel COM.
I will check if the CLSID for a workbook has changed with Excel 365.