Jewtus Posted June 4, 2015 Share Posted June 4, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted June 4, 2015 Share Posted June 4, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jewtus Posted June 5, 2015 Author Share Posted June 5, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 Try to search for the date in (standard Excel) format: "MM/DD/YYYY" My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jewtus Posted June 5, 2015 Author Share Posted June 5, 2015 (edited) $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 Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 Will have a look on Monday because at home I have no Excel. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jewtus Posted June 5, 2015 Author Share Posted June 5, 2015 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. Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Jewtus Posted June 5, 2015 Author Share Posted June 5, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
iamtheky Posted June 5, 2015 Share Posted June 5, 2015 you could outsource the work to arrays:rangeread the whole sectionSearch the resulting array for that itemdo 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. ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
iamtheky Posted June 5, 2015 Share Posted June 5, 2015 (edited) 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 ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
water Posted June 5, 2015 Share Posted June 5, 2015 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
iamtheky Posted June 5, 2015 Share Posted June 5, 2015 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. ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
water Posted June 6, 2015 Share Posted June 6, 2015 Ops, my bad. As you stated, correct is: 5/22/2015 (fixed in previous post). My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted June 6, 2015 Share Posted June 6, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Bowmore Posted June 6, 2015 Share Posted June 6, 2015 (edited) @waterI 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 "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 Link to comment Share on other sites More sharing options...
water Posted June 6, 2015 Share Posted June 6, 2015 Thanks Bowmore for the reply!Will add the finding to the help file and wiki. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
iamtheky Posted June 6, 2015 Share Posted June 6, 2015 (edited) 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 ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now