Jasug Posted December 26, 2012 Share Posted December 26, 2012 Hi, I have an AutoIt script that does a lookup in an .xls file and when a match is found it copies data from an offset cell. This part works fine, but I cant seem to control when a match is not found. The ;~ part is what I am hoping someone can help with. Func Lookup() $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 $oExcel.WorkBooks.Open ("\\document\location\name.xls") With $oExcel.ActiveSheet.Range("A:A").Find ($Name) $Match = .Find ($Name) ;~ If $Match = False Then ;~ do something Then ;~ Exit ;~ Else ;~ do some other thing ;~ EndIf $Match.Offset (0, 1).copy EndWith $oExcel.Quit EndFunc Link to comment Share on other sites More sharing options...
water Posted December 26, 2012 Share Posted December 26, 2012 The Find method returns a range object if a match was found. Func Lookup() $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 $oExcel.WorkBooks.Open ("\\document\location\name.xls") With $oExcel.ActiveSheet.Range("A:A").Find ($Name) $Match = .Find ($Name) If Not IsObj($Match) Then ; do something Exit Else $Match.Offset (0, 1).copy EndIf EndWith $oExcel.Quit EndFunc 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...
Jasug Posted December 28, 2012 Author Share Posted December 28, 2012 Hi Water, Thanks for your quick reply. I just tried it but get Error: Only Object-type variables allowed in a "With" statement" Any Ideas. Regards Jasug Link to comment Share on other sites More sharing options...
water Posted December 28, 2012 Share Posted December 28, 2012 This should work: Func Lookup() $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 $oExcel.WorkBooks.Open ("\\document\location\name.xls") $Match = $oExcel.ActiveSheet.Range("A:A").Find ($Name) If Not IsObj($Match) Then ; do something Exit Else $Match.Offset (0, 1).copy EndIf $oExcel.Quit EndFunc 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...
Jasug Posted December 28, 2012 Author Share Posted December 28, 2012 Hi Water, Works perfectly... Much appreciated. Regards Jasug Link to comment Share on other sites More sharing options...
water Posted December 28, 2012 Share Posted December 28, 2012 BTW: The new Excel UDF (replacing the one that comes with AutoIt in the near future) is available It will have all features to work with current versions of Excel.Feature requests are welcome. 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...
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