Excel find value

[Jewtus]

I'm trying to get the address of a cell with a given date in it. I've been working with the Excel_rangefind but it keeps saying my workbook is not an object or not a workbook object. This is what I have

$oWorkbook2=$oExcel.WorkBooks.Open ($fFinalFile)
$SELFinalWorkheet=$oWorkbook2.Worksheets("SELECT")

and It errors when I use the following

_Excel_RangeFind($oWorkbook2,'20150522000000','A8:AM8')

I've also tried using the worksheet and it gives the same error. So I tried to go to the basic function and when I use this:

$aDates=$SELFinalWorkheet.Range("A8:AM8").Value

 

(NOTE: Had to put this in code because it wasn't showing on the post)
I can see the 20150522000000 value. I tried using

 

 

$aDates=$SELFinalWorkheet.Range("A8:AM8").Find('20150522000000')

and it doesn't do anything (no results are found).

 

Am I missing something obvious here?

Edited June 4, 2015 by Jewtus
content not displaying

[water]

You are mixing too many things: Using COM with the Excel UDF, directly using COM and automating the GUI.

I suggest to only use a single method to keep it simple and avoid errors.
Which route do you want to go? With the UDF way I can help

[Jewtus]

Either route will work (I tried com objects and the UDF and mixing them to try to get done what I needed). What I'm trying to do is copy a specific range on one workbook then find the date (located in Cell C8 on the first worksheet) then find the date on the second workbook and paste the copied column from the first workbook to that column. Does that make sense? I didn't understand how to do it with _Excel_RangeCopyPaste because it seems like they need to be the same workbook. The other part that poses a problem is there are 10 or so worksheets in each workbook that I need to do this for.

 

(note put text in a code block because content was not rendering)

Edited June 5, 2015 by Jewtus
content not rendering

[water]

Try to search for the date in (standard Excel) format: "MM/DD/YYYY"

[Jewtus]

$oExcel =_Excel_Open()
$oWorkbook1=_Excel_BookOpen($oExcel,$fOrigFile)
$oWorkbook2=_Excel_BookOpen($oExcel,$fFinalFile)
$afindrange=_Excel_RangeFind($oWorkbook2,"05/22/2015","A8:AM8")
_ArrayDisplay($afindrange)

 

 

Still getting a blank array as a result. I did realize that some of the fields were formulas, which I though might have been the issue, so I changed the 22nd to a static number and still no results

 

Edited June 5, 2015 by Jewtus
content not rendering

[water]

Will have a look on Monday because at home I have no Excel.

[Jewtus]

No rush. This is something I'm doing manually and it doesn't take long, just don't want to have to remember to do it anymore.

[water]

Another idea (taken from Google). Use the Excel function Datevalue to convert a DD/MM/YYY value to the internal representation and then use _Excel_RangeFind.

[Jewtus]

That was what I was trying originally and it gives me 20150522000000 which didn't work in the find function either.

(edited because content was not showing up)

Edited June 5, 2015 by Jewtus
content not displaying

[water]

What you see is either a string or the formatted output of the internal representation of a date.

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.
Check this site for more information.

[iamtheky]

you could outsource the work to arrays:

rangeread the whole section

Search the resulting array for that item

do the same thing to find the target in 2nd xls.

then write the items from the found column in the first array to appropriate column in the 2nd xls.

[water]

Yes, but he still would need to calculate the number of days since 1900-1-0 to get the value to search for.

[iamtheky]

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)

 

Edited June 5, 2015 by boththose

[water]

That was what I was trying originally and it gives me 20150522000000 which didn't work in the find function either.

​From what boththose posted and Google tells me you should try: "22/5/2015" "5/22/2015"

Edited June 6, 2015 by water

[iamtheky]

5/22/2015, or is there something with his formatting that would require days first?  Also, if you found an answer as to what is going on can you explain/link it?  I just slowly backed out from the year and replaced asterisks to try/fail my way to that example.  I have no idea why I can search the formula for the date in that format, even when i dont use that format in the document.

[water]

Ops, my bad. As you stated, correct is: 5/22/2015 (fixed in previous post).

[water]

Just tested on my business computer.
Looks like the date format you need to enter in _Excel_RangeFind is exactly the format the date is being displayed in the formula field in Excel.
Here the date is being displayed as DD.MM.YYYY in the formula field. That's how you have to search for the date:

Global $aResult = _Excel_RangeFind($oWorkbook, "22.05.2015")

Could you please verify that this is true for your environment as well?
If yes, I'm going to add a line to the help file and a section to the wiki.

[Bowmore]

@‌water

I can confirm your finding in Excel 2010. _Excel_RangeFind() works provided the date string is in the same format as date cells are set to display in Excel. In my case this works.

Global $aResult = _Excel_RangeFind($oWorkbook, "2015-05-22")

 

Edited June 6, 2015 by Bowmore
Spelling

[water]

Thanks Bowmore for the reply!
Will add the finding to the help file and wiki.

[iamtheky]

Both of you are searching the $xlValues.  I believe you can use the format I showed ("M(M)/D(D)/YYYY") against $xlFormulas and disregard the way it is displayed.

*but again, i can find no documentation for why this works

Edited June 6, 2015 by boththose