_Excel_RangeFind

[Valnurat]

I'm trying to find a text in my excel with Excel_RangeFind, but I get an error.

My code:

expandcollapse popup

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

; Create a constant variable in Local scope of the message to display in FileOpenDialog.
Local Const $sMessage = "Select a single file of any type."
; Display an open dialog to select a file.
Local $sFileOpenDialog = FileOpenDialog($sMessage, @WindowsDir & "\", "All (*.*)", $FD_FILEMUSTEXIST)
If @error Then
    ; Display the error message.
    MsgBox($MB_SYSTEMMODAL, "", "No file was selected.")
    ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
    FileChangeDir(@ScriptDir)
    Exit
Else
    ; Change the working directory (@WorkingDir) back to the location of the script directory as FileOpenDialog sets it to the last accessed folder.
    FileChangeDir(@ScriptDir)
    ; Replace instances of "|" with @CRLF in the string returned by FileOpenDialog.
    $sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)
    ; 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, $sFileOpenDialog)
    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 string "=C10*10" in the formulas, exact match
    ; *****************************************************************************
    Local $aResult = _Excel_RangeFind($oWorkbook, "Maskprov 7", Default, Default, $xlWhole)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 2", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 2", "Find all occurrences of string 'Maskprov 7' in the formulas, exact match." & @CRLF & "Data successfully searched.")
    _ArrayDisplay($aResult, "")
EndIf

My error:

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR
->21:10:04 AutoIt3.exe ended.rc:1
+>21:10:04 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 11.22

Do I something wrong?

[water]

This subject has been discussed a few times. It is a bug in Autoit 3.3.14.2.
Either downgrade to AutoIt 3.3.12.0 or modify _Excel_RangeFind as described in one of the threads I mentioned.

[Valnurat]

I saw in another post that I should do this and run it, but it does not help.

;$aResult[$iIndex][1] = $oMatch.Name.Name

My version of Autoit is 3.6.2

[water]

There is no 3.6.2 version of AutoIt.
What does

ConsoleWrite(@AutoItVersion & @CRLF)

return?

[Valnurat]

3.3.14.2

Maybe it was my Scite version.

[Valnurat]

Oh, I just saw that my excel.au3 is a readonly file.

I did made it not readonly, but how can I save it as excel.au3?

[Valnurat]

I manage to save it on another location, but now I get this error:

"C:\Users\DKSOKVK\Documents\AutoIT\IncludeMyUDF\Excel.au3" (660) : ==> The requested action with this object has failed.:
$aResult[$iIndex][5] = $oMatch.Comment.Text
$aResult[$iIndex][5] = $oMatch.Comment^ ERROR
->22:40:49 AutoIt3.exe ended.rc:1
+>22:40:49 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 10.07

 

[alien4u]

1 hour ago, Valnurat said:

I manage to save it on another location, but now I get this error:

"C:\Users\DKSOKVK\Documents\AutoIT\IncludeMyUDF\Excel.au3" (660) : ==> The requested action with this object has failed.:
$aResult[$iIndex][5] = $oMatch.Comment.Text
$aResult[$iIndex][5] = $oMatch.Comment^ ERROR
->22:40:49 AutoIt3.exe ended.rc:1
+>22:40:49 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 10.07

 

Try to use this RangeFind modified function by @MichaelHB:
 

expandcollapse popup

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

You could find more info about this here:
https://www.autoitscript.com/forum/topic/182057-how-do-i-select-a-worksheet-in-excel

Regards
Alien.

[Valnurat]

1 hour ago, water said:

This subject has been discussed a few times. It is a bug in Autoit 3.3.14.2.
Either downgrade to AutoIt 3.3.12.0 or modify _Excel_RangeFind as described in one of the threads I mentioned.

If I downgrade do I miss other stuff?

[alien4u]

1 hour ago, Valnurat said:

If I downgrade do I miss other stuff?

My advise: try the Modified version of RangeFind if you can't make it work in the way you need, then Downgrade...

Regards
Alien.

[water]

Check History / ChangeLog in the help file and you will see what you will be missing.

[Valnurat]

Hmm, I have uninstalled AutoIT v. 3.3.14.2 and I uninstall SciTE v. 3.6.2.

I installed AutoIT v. 3.2.12.0, but when I trying to install SciTE4AutoIT3 16.306.1237.0 I get an error (SciTE.JPG). I have just pressed "ok".

When I'm trying to compile I get an error (SciTE1.jpg)

What do I need to do?

[Valnurat]

I reinstall AutoIT to 3.3.14.2 and SciTE to 3.6.2 and modified the RangeFind.

Save the original Excel.au3 to ExcelModify.au3 and placed it in my own folder for non-original include au3.

When I complie it I get this error:

>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\DKSOKVK\Documents\RAD Studio\Projects\Alfalaval\Test Apps\Consultant.au3
"C:\Users\DKSOKVK\Documents\RAD Studio\Projects\Alfalaval\Test Apps\Consultant.au3"(33,91) : error: _Excel_RangeFind(): undefined function.
 Local $aResult = _Excel_RangeFind($oWorkbook, "Maskprov 7", "B8:B1739", Default, $xlWhole)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

I'm lost. Don't know what happens.

Can someone help me?

 

[water]

You are sure ExcelModify.au3 contains function _Excel_RangeFind? How does the line

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)

look like in your modified UDF? Can you please post?

[Valnurat]

Maybe I have missunderstod your recommendation, but should I not modify the original excel.au3 UDF file?

Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)

I saw there was 2 underscores before calling the function. After I change that it worked.

Edited May 4, 2016 by Valnurat

[water]

You should and you did.
But you call _Excel_RangeFind (note that the function name starts with a single underscore) and in your UDF you have __Excel_RangeFind (note that the function name starts with TWO underscores).

[Valnurat]

Yes, I saw that.

[Valnurat]

My goal is to have some info I got in a comma CVS file merged with a excel doc.

In the excel.doc the search column is B and I need to do it from row 8, because 1-7 is locked.

Do I do it correct?

I really hope someone can help me.

[water]

You read the CSV into an array and then loop through this array. Search in column "B" for the key and then add the remaining data from the array?
Should be possible.
As the RangeFind problem seems to be fixed which problems do you see now?

[Valnurat]

This is my result:

Row|Sheet|Name|Cell|Value
[0]|Consultant OU - Questions - EME|$B$1573|Maskprov 7|Maskprov 7

But how do I use this info to add text into the excel doc?