Jump to content
Jewtus

Excel find value

Recommended Posts

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 by Jewtus
content not displaying

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by Jewtus
content not rendering

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by Jewtus
content not rendering

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by Jewtus
content not displaying

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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


,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

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

×