Excel lookup table problems

[Jasug]

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

[water]

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

[Jasug]

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

[water]

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

[Jasug]

Hi Water,

Works perfectly...

Much appreciated.

Regards

Jasug

[water]

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.