Excel_RangeFind

[Faedien]

This is my first post, but I've gotten solutions from this forum hundreds of times.  This community is amazing!

I can't figure this one out though.  I'm trying to open a specific file and see if a specific number (in this case "100044141") happens.  I want to eventually have the program copy the row where that number appears and then put that row of data in another sheet.  I can't get past this though.

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $sWorkbook = "c:\tmp\CALDB.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, Default, Default, True)
Local $loanplace = _Excel_RangeFind($oWorkbook, "100044141", Default, Default, Default, Default)

This script sees this error below.

Quote

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (670) : ==> The requested action with this object has failed.:
$oSheet = $oWorkbook.Sheets($iIndexSheets)
$oSheet = $oWorkbook^ ERROR

I've tried looking elsewhere for solutions to this, and I've checked with error checking to make sure that $oExcel and $oWorkbook were in fact objects.  That all checked out fine, so I'm not sure what else I need to tell the program.  

Thanks in advance!

[MichaelHB]

Try this modified _Excel_RangeFind function.

expandcollapse popup

Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Activesheet.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][4], $iIndex = 0, $iIndexSheets = 1, $iNumberOfSheets = $oWorkbook.Worksheets.Count
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $aResult[$iIndex][1] = $oMatch.Address
                $aResult[$iIndex][2] = $oMatch.Value
                $aResult[$iIndex][3] = $oMatch.Formula
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][4]
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        If $iIndexSheets > $iNumberOfSheets Then ExitLoop
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex][4]
    Return $aResult
EndFunc

 

[Faedien]

Worked like a charm, thank you so much!

[MichaelHB]

Glad that helped you.