Jump to content

Extract multiple rows from Excel Read returned array


Recommended Posts

Good day everyone, 

So, I'm trying to extract all rows from a searched string within an array. Pretty sure I'm missing something here but arrays are not my thing despite I've read the help file, the wiki and many topics on the forum (yeah yeah, I'm probably dumb :D)

Using the Excel UDF, I can easily put the whole file content into an array:

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $sWorkbook = @ScriptDir & "\LAP.xlsx"
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Read column C
Global $oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
Global $aResult = _Excel_RangeRead($oWorkbook, Default, "B1:B"" & $oRange.Row)
_ArrayDisplay($aResult)

_Excel_BookClose($sWorkbook)
_Excel_Close($oExcel)

From above, I can just have the whole B$ Rows from the whole array content but what I'd like to achieve is the following:

Search for something in Col A$ and then retrieve the corresponding rows from the results:

$LAP = InputBox("LAP", "Search for LAP", "")

Doing so, I 'm able to retrieve the result index in the array by displaying $LAP content but can't get the related rows.

Here's the excel file sample

image.png.5cfb901a4b7f4c4e54d8bc809b529ad2.png

100% this is simple as f* but my mind seems not to want to find it more :(

Thanks a lot any help that might come.

-31290-

Edited by 31290

~~~ Doom Shall Never Die, Only The Players ~~~

Link to post
Share on other sites

Maybe something like:

_SearchArray()
Func _SearchArray()
    Local $sInputBox
    While 1
        $sInputBox = InputBox("LAP", "Search for LAP", "")
        If @error Then Return SetError(1, 0, "")
        Exitloop
    Wend
    ;~ Search in Column A for Input and return row index
    Local $iSearchResult = _ArraySearch($aResult, $sInputBox, 0, 0, 0, 0, 1, 0)
    If Not @error Then
        ;~ Display Second Column Item
        MsgBox(4096, "", $aResult[$iSearchResult][1])
    EndIf
EndFunc

 

Edited by Subz
Link to post
Share on other sites

@Subz I warmly thank you for pointing me in the way I want! 

Fortunately, there are only 6 Rows I need to display :) 

I admit I forgot to get a separate function for me to make the search but I was really unable to make the code inside it.

Have a good one!

 

~~~ Doom Shall Never Die, Only The Players ~~~

Link to post
Share on other sites
54 minutes ago, Subz said:

probably just putting the InputBox inside a loop should perform the same task.

Yeah, no worries.

54 minutes ago, Subz said:

assumed no one would reach the limit

As long as the user didn’t accumulate 4000 cancels before the user closes the program, no one would ever know :)

Code hard, but don’t hard code...

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...