Valnurat

_Excel_RangeFind

23 posts in this topic

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



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

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

There is no 3.6.2 version of AutoIt.
What does

ConsoleWrite(@AutoItVersion & @CRLF)

return?


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

3.3.14.2

Maybe it was my Scite version. :)


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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

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

#10 ·  Posted

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

#11 ·  Posted

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

#12 ·  Posted

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

#13 ·  Posted

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

#14 ·  Posted

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

#15 ·  Posted (edited)

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

#16 ·  Posted

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

#17 ·  Posted

Yes, I saw that. :>


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

#18 ·  Posted

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

#19 ·  Posted

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

#20 ·  Posted

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

    • sree161
      By sree161
      Hi,
      i have a code which generates two numeric outputs.i have to save them in excel.
      Is there any way to export them to specific cells in excel???
    • TotalAuto
      By TotalAuto
      Howdy All
      I've gone around and around in circles wasting hours trying to fix this problem to no avail so I thought I would try posting for help given there are some real smart people in this forum.
      Background
      I have 5 separate .au3 scripts that all do different basic tasks. For example:
      1.au3 = opens Chrome browser to obtain information from a specific website then closes
      2.au3 = opens an Excel workbook to run a macro then saves and closes
      3.au3 = Open two seperate workbooks and transfers data from one to the other then saves and closes
      4.au3 = .......etc
      5.au3 = .......etc
      These 5 separate scripts, doing completely different tasks, need to each run once per day at 5 different designated times within a day and then close. For example:
      1.au3 = 10am
      2.au3 = 12pm
      3.au3 = 2pm
      4.au3 = 4pm
      5.au3 = 5pm
      Problem
      I can not get these different scripts to reliably run at their designated times. When I manually run these scripts individually by using 'Go F5' they work perfectly. If I convert these scripts to an .exe and manually run each one they work about 98% of time.
      How can I schedule these different .au3 files or associated .exe files to reliably run each day at different times?
      Attempts To Fix The Issue
      1. I have tried converting each .au3 script into their own .exe file and use Window's Task Scheduler to run these .exe files at their designated time but the success rate  of running each .exe in full, to completion, without error is very low at about 30%. Things that commonly go wrong include a browser not opening but movement still continues on the desk top. Other times things just do not happen at all.
      2. I have Windows 7 64 bit and have tried creating and running both (x86) and (x64) .exe files but again reliability problems exist;
      3. I have also tried moving each .exe file to the My Documents folder as I read someone that keeping your files on your desktop ties your files to individual Windows user profiles which can cause AutoIt operating errors. Not sure if this is correct but thought I would try it anyway. It did not fix my issue.
      4. I installed a program called AlwaysUp which supposedly always reliably runs in the background and will reliably execute your AutoIt script or .exe files. Still have significant reliability issues.
      Therefore, can someone please provide advice on how I can get my 5 different .au3 files to run at 5 designated times through the day, every day, without fail? Overtime I will be creating more and more different .au3 files to run certain tasks throughout the day and do not want to create one large AutoIt file that continually runs and attempts to do all the different tasks itself.
      Any help is very appreciated.
      Thanks
      Dan
       
       
    • hugomito
      By hugomito
      Hi all,
      I built an AutoIt application that interacts with several other applications/software. Now, it's turn to interfact with Visual Studio Team Services (VSTS).
      Does anyone has a blog, demo, script to share?
      Thanks!
    • Viki
      By Viki
      This is my first time here so please dont bombard me that what a silly question I am asking!!
      I have 500 rows (A1:A500) in a spreadsheet and I just want to copy one by one row and then paste into another application and then press enter, loop should repeat this until finishes all 500 rows.
      I have looked at clipget(), clip(put() but dont know how to select next row in next turn. I also looked at Array to store but again no luck. Can some guide me please..
    • DineshPawar
      By DineshPawar
      0down votefavorite   In my current project, Excel macro open AutoIt script using a function Call Shell("location of autoit script").
      For proper functioning of AutoIt script it's need to be close parent Excel workbook, so this closing Excel action is written in AutoIt script itself.
      But as soon as AutoIt script close parent Excel workbook then script get pause and it do nothing.
      How open the AutoIt script from Excel workbook and after that parent Workbook get close?