Excel find value

[water]

Did you try it with _Excel_RangeFind?
I tried $xlValues and $xlFormulas with "M(M)/D(D)/YYYY" with a german Excel but it didn't work.

[iamtheky]

i have not tried german, but with my example a few posts above I changed the date display to every option available including where it was words.  Such that the return of rangefind for all cells would not show the date in any kind of slash format. And it still found the target.  I dont know what the cause of that behavior is.

Edited June 6, 2015 by boththose

[water]

I suspect that the format to be used depends on the locale.
Will test on Monday.

[iamtheky]

i should have assumed if it was a german excel, the OS would be as well

[water]

I tested with the German version of Excel 2010 (32 bit) on Windows 7 (64 bit).
The only date format that worked for me is the short date format as defined in the Windows system control. I tested the Excel search function Ctrl-f.

Will see if I can find anything on the web.

[Jewtus]

no not really, that is just the way it is displayed (as best as I can understand why it shows serial dates, but acts on actual dates, i havent found a way yet to search by the serial date, if that exists that would probably clear up some of the fog in my view).  You could also do it like this,   your target date needs to be month/day/year with no 0 padding.  "M(M)/D(D)/YYYY"  

edit: *maybe not needs to be, but it has worked for me even when i change the excel document to other date options that are nowhere close to this format, it apparently disregards that formatting for rangefind purposes.

 

#include<array.au3>
#include<excel.au3>

$sTarget = "7/1/2014" ;July 1 2014

$oApp = _Excel_Open(False)
$oBook = _Excel_BookOpen($oApp , @ScriptDir & "\test.xlsx" , True)
$aFind = _Excel_RangeFind($oBook , $sTarget , Default , $xlFormulas, $xlPart)
_Excel_BookClose($oBook)
_Excel_Close($oApp)

_ArrayDisplay($aFind)

 

​I tried this snippet and I'm getting errors on the UDF:

 

C:\Sources\NewTool (autoit based)\Autoit Tools\Include\Excel.au3 (622) : ==> The requested action with this object has failed.:
$oSheet = $oWorkbook.Sheets($iIndexSheets)
$oSheet = $oWorkbook^ ERROR

 

 

Is there not a way to search a specific worksheet? The reason I ask is because there are 15 tabs on the file and each tab has a date variable (the excel is a report that has daily statistics for different areas. The source file is linked to SQL server to pull the different views down then format them into a report/dashboard that gets copied to the master "dashboard" excel file).

(note edited because content was not displaying)

Edited June 8, 2015 by Jewtus
content was not displaying

[water]

Set parameter $vRange to the usedrange of the sheet you want to search.

[Jewtus]

Ok, so I tried this:

$sTarget = "5/21/2015" 

$oApp = _Excel_Open(True)
$oBook = _Excel_BookOpen($oApp , $fFinalFile , True)
$aFind = _Excel_RangeFind($oBook , $sTarget,"SEL!A8:AM8", Default , $xlFormulas, $xlPart)

_ArrayDisplay($aFind)
_Excel_BookClose($oBook)
_Excel_Close($oApp)

and I get the following error

"C:\Sources\NewTool (autoit based)\Autoit Tools\Include\Excel.au3" (602) : ==> The requested action with this object has failed.:
$oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
$oMatch = $vRange^ ERROR

When I try it without the ", Default , $xlFormulas, $xlPart" it executes but it gets a blank result. I tried adding wildcards to the string and using various other combinations of the date (IE 2015-21-5, 2015.21.5, etc)

Edited June 8, 2015 by Jewtus

[water]

Do you run the latest beta version of AutoIt? Because with 3.3.12.0 AutoIt shouldn't crash.

[Jewtus]

Yea, I do run the beta (3.3.13.19). I try to do most of my stuff in the beta version to avoid regressions (which is why a lot of what I'm doing is a mix of UDFs and VB). I currently have something like 15 different tools that I've built for my work and I don't have the time to troubleshoot every issue so when the UDF acts a little weird, I tend to do the VB equivalent or extract the specific part of the UDF i need. 

 

I'll keep messing with the VB functions if its an issue with beta.

 

P.S. Turns out that using the autoit boards on chrome at work makes some content not show up... tried using palemoon browser and I'm able to see all the forum content.

Edited June 9, 2015 by Jewtus

[water]

I suggest to use the latest production version of AutoIt (3.3.12.0) because it is stable. In the beta version Jon has started to change the way COM errors are handled which - in the unfinished beta state - leads to lot of "problems".

[Jewtus]

Ok, I'll try that and see how it goes.