Laurynelis

Find location of cell that contains specific text

38 posts in this topic

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

1.png

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! ;)

Share this post


Link to post
Share on other sites



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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

1 person likes this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites

Which version of AutoIt do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

SciTE-Lite  
Version 3.5.4

Share this post


Link to post
Share on other sites

Sorry, 3.3.14.2

Share this post


Link to post
Share on other sites

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

MsgBox(0, "", @AutoItVersion)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


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

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@Laurynelis

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

#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

 

Share this post


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

Share this post


Link to post
Share on other sites

Try this:

#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

 

1 person likes this

Share this post


Link to post
Share on other sites

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

Thank you very much!

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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