Jump to content
Valnurat

_Excel_RangeFind

Recommended Posts

Valnurat

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

My code:

#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?


Yours sincerely

Kenneth.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites
water

There is no 3.6.2 version of AutoIt.
What does

ConsoleWrite(@AutoItVersion & @CRLF)

return?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Valnurat

3.3.14.2

Maybe it was my Scite version. :)


Yours sincerely

Kenneth.

Share this post


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


Yours sincerely

Kenneth.

Share this post


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

 


Yours sincerely

Kenneth.

Share this post


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

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.

Share this post


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


Yours sincerely

Kenneth.

Share this post


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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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?

SciTE.JPG

SciTE1.JPG


Yours sincerely

Kenneth.

Share this post


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

 


Yours sincerely

Kenneth.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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 by Valnurat

Yours sincerely

Kenneth.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Valnurat

Yes, I saw that. :>


Yours sincerely

Kenneth.

Share this post


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


Yours sincerely

Kenneth.

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
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?


Yours sincerely

Kenneth.

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

  • Similar Content

    • bmy007ro
      By bmy007ro
      I have this psexec command working fine 
      Local $sMachine = InputBox("Input PC name", "Enter Computer Name")
      psexec \\$sMachine -u $sDomain\$sUserName -p $sPassword  \\NetworkFLD\FILENAME.cmd > C:\Temp\TT\My.log
       
      I wanted to converted to AutoIT script
      Cloud you help, please !?
       
    • autotin
      By autotin
      Hello I am a first time user of AutoIT with little coding experience and I would REALLY really appreciate your help.
      I work in a warehouse where I am responsible for data entry along with a slew of other different tasks such as auditing and managing paperwork. Most of my time is tied up entering info to close receipts of product orders (8-12+ hours for thousands of products). The quantity actually produced by the warehouse is sometimes different from the quantity listed on the reciept (in this case 20). I need to change all the build qty for sublocation "1-WIP" from 20 to the actual quantity 22 and set all other numbers as 0. The form is an iframe coded with jQuery and freezes for 1-2 seconds after every entry and refreshes. As such, it doesn't allow me to use tab and I need to click and type every incorrect field.
      I have attached an image as an example from the webapp, FinaleInventory.
      I'm thinking that I probably need a macro that has a message box entry where I can type the corrected number entry I want. Then it uses image/character recognition (Tesseract?) to find "WIP" sublocation =,   Move the mouse over to the right to "Quantity" relative to location of WIP If quantity number = entry, then move to next "WIP" image (Not quite sure how to make Tesseract "find next") Elseif quantity number ≠ entry then click quantity number of "WIP" and type corrected entry Scroll down Loop until bottom of page I would greatly appreciate any help, ideas or pointers for this project. Thank you!
      -A newbie scripter

    • Pagi
      By Pagi
       
      <a href="index.php=4143b27b41db0a40c499202865c3e24d" class="menuitem " target="\_self"> "Menu Item 1" </a>
      Can you tell me if it´s possible to click this menu button? If yes, how? Cause I have no idea, I think i should use _IETagNameGetCollection
    • mar3011
      By mar3011
      hello, I want to set in the inputbox field random key from the keyboard to which the function will be assigned, for example F4 function start notepad ALT + A function start notepad for one function. Thank you for any help.  
    • JuanFelipe
      By JuanFelipe
      Cambiar a español Hello friends, I need help with a problem I have, I want to know if there is any way to update a GUI already running, that is I have a GUI of a certain size and with a button I would like to enlarge it:
      example: $ form1 = GUICreate ("", 200,100,0,0)   When you press the button, the gui increases its size, and actuates the new size.
×