Sign in to follow this  
Followers 0
Jasug

Excel lookup table problems

6 posts in this topic

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

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi Water,

Works perfectly...

Much appreciated.

Regards

Jasug

Share this post


Link to post
Share on other sites

:D

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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
Sign in to follow this  
Followers 0