Jump to content
aiter

_Excel_RangeFind fails

Recommended Posts

aiter

I have tried to use _Excel_RangFind, but it fails. It even fails in the example in the help file (see example 1).

This the error

>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\Program Files (x86)\AutoIt3\Examples\Helpfile\_Excel_RangeFind.au3
+>12:09:10 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\_Excel_RangeFind.au3"    
--> 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

My own code fails with a similar message

Local $aResult = _Excel_RangeFind($oWorkbook, "john")
_ArrayDisplay($aResult)

Help please.

 

Share this post


Link to post
Share on other sites
aiter

Oh, just saw message at the top of the board.  is it best to create my own find function in my program using the fixed code?

Will it be fixed in the new autoit release or is there a new release already (I am using 3.3.14.2)?

Edited by aiter

Share this post


Link to post
Share on other sites
aiter

The fixed function works by the way. I added the function to my script and called it _Excel_RangeFinder

Share this post


Link to post
Share on other sites
water

It's a bug in Autoit 3.3.14.2 but there is no release date for a new AutoIt version.
For the time being _Excel_RangeFinder is a cool solution ;)


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
aiter

There is a still a problem with this function. When it does not find the value, it still returns an array and does not set @error.    

Local $aResult = _Excel_RangeFinder($oWorkbook, "garbageeeee") ;,$SheetRange)
;if @error <> 0 Then
    MsgBox(0,'',@error & ' -- ' & $aResult)
;endif
_ArrayDisplay($aResult)

displays 0 for @error , $aResult is an empty array

How do I know when no match is found?

What is Default?  Displaying it shows a literal value of 'Default'. It seems to be understood by the API.  It confuses me.

Edited by aiter

Share this post


Link to post
Share on other sites
water

"No records found" isn't returned as an error by Excel. The function just returns an empty array. So you need to check for the number of rows in the array.


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

  • Similar Content

    • AnonymousX
      By AnonymousX
      Sorry if this is a repost, but having some trouble searching for the answer and pretty tired right now.
      I'm just wondering how to make a border around a range of cells in Excel. I want the regular lines created by selecting "All Borders" option around F2:G3
      I saw this code:
      With $oExcel.ActiveSheet.range("F2:G3") .Select .Borders($xlEdgeBottom).LineStyle = $xlContinuous .Borders($xlEdgeBottom).Weight = $xlThick .Borders($xlEdgeBottom).ColorIndex = $xlAutomatic EndWith but I get errors of these variables not existing. It seems these no longer exist in the "#include <Excel.au3>"
      Is there another include file I need?
      I got this to work for highlighting cells, wondering if there is a option similar to this for all borders?
      $oExcel.ActiveSheet.Range("F2:G3").Interior.ColorIndex = 6 Thanks
    • YouriKamperman
      By YouriKamperman
      I am working on a script that will turn all file names in a directory into an array, and then writes this Array to an Excel file.
      This in itself is working, but the RangeWrite function always puts the Array count in the first cell.
      How can i make sure this does not happen? I can of course just filter it out in Excel, but i am trying to keep all logic of filtering text in my script.
      This is what my script looks like:
      Local $Yesterday = _DateAdd('d', -1, _NowCalcDate())
      Local $cDate = StringReplace($Yesterday, "/", "-")
      Local $aFileList = _FileListToArray(@WorkingDir & "/" & $cDate, "*")
      Local $oExcel = _Excel_Open()
      Local $oWorkbook = _Excel_BookNew($oExcel)
      _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aFileList)
    • Eminence
      By Eminence
      Hello,
      Is there a way wherein I can access the data from an array coming from an Excel file then have it assigned on to a variable?
      Below is a snippet of my current code. For now, it just reads and outputs the data from the excel file and have it displayed via an array.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open(False) If @error Then Exit MsgBox(0, "Error", "Error creating application object." & @CRLF & "Error: " & @error & " Extends: " & @extended) ; Open Excel Woorkbook and return object Local $sWorkbook = @ScriptDir & "\Excel Files\Test Data.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, False, True) If @error Then MsgBox(0, "Error", "Error opening workbook'" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & "Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf Local $aResult = _Excel_RangeRead($oWorkbook) ; Error Trapping If @error Then MsgBox(0, "Error", "Error reading data from '" & $sWorkbook & ".'" & @CRLF & "Error: " & @error & " Extends: " & @extended) _Excel_Close($oExcel) Exit EndIf _ArrayDisplay($aResult) My Excel file has values from Column A to H with values from 1 to 30, what I desired to do is have the value in "A7" assigned on to a variable. 
       
      Any help is appreciated. Thanks in advance.
    • Skeletor
      By Skeletor
      A gui splash screen. Themed after the famous MS Office Suite.
      Three colours to choose from, Red, Blue and Green.
      You can change the labels to your own application.
      Animated side scrolling dots just like MS does.

      Also bundled with this is the KODA form. So you can open up and see the basic structure of this splash screen.
    • Skeletor
      By Skeletor
      MS Splash Screens
      The office 2016/17 office suite has made many people turn heads with its functions and especially their splash screen.
      I now present to you, the MS Style Splash Screen. For Rookies, Novices and anyone who wants a ready made splash screen for your gui application.

      You can now download it and also try it out yourself.
      You have three colors to choose from:
      Blue
      Red
      Green
      All three colours are from the famous programs.
      I also included the KODA form to you can have freedom with this splash screen.
      Note: the KODA form does not have the three colours as well as the correct blue. 

      Have fun and enjoy.
      Note:
      Thanks to kaz for updating the side scrolling dots.
       
       
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.