[Solved by other way] How to search and fetch whole ROW into another EXCEL file

[jackylee0908]

Hi sir,

As attached screenshot, I'd like to search the keyword '2: "HGST HUS726020ALS210 A907"'(red marked) and copy whole data(green marked) of ROW into another EXCEL file(or another new worksheet), there will many keywords need to be searched and ROWs need to be copy to another place for advanced data filtering.

Please advise how to achieve that, thanks much!

Jacky

Edited January 21, 2019 by jackylee0908

[jackylee0908]

I put an example file here, the data need to be searched is from 'Test Log - 12 Core CPU' worksheet, and the example of output after searched by script is as 'Example Output' worksheet.

HDD_Performance_Result.xlsx

[Subz]

Look at the Excel udfs, example _Excel_RangeFind, _Excel_RangeRead or _Excel_RangeCopyPaste.

[jackylee0908]

42 minutes ago, Subz said:

Look at the Excel udfs, example _Excel_RangeFind, _Excel_RangeRead or _Excel_RangeCopyPaste.

Hi @Subz,

Thanks, but I am new for autoit, I understand that it can be achieved by combined use of _Excel_RangeFind, _Excel_RangeRead, and _Excel_RangeCopyPaste, but it is difficult for me, could you please provide the example code on search -> script understand the row of search result -> copy whole row -> paste to another place.

Thanks.

[Subz]

You have to show some effort, "but I am new for autoit" isn't an excuse not to read the help file, I personally learn't Autoit from the help file and examples posted in the forums.  Only time I've ever requested help is after several failed attempts on my own, but I always posted the code that I couldn't get working to show I had at least tried.  As I mentioned the _Excel_RangeFind has several examples for you to get started, just loop through the contents to get the row numbers and then use _Excel_RangeRead to read that row and paste it into the other sheet.

[water]

A good place to start is the wiki.
The Excel UDF entry describes what you need.
As you are working with ranges I suggest this page. It describes how to work with whole rows when you just have a single cell (as returned by _Excel_RangeFind).

[jackylee0908]

I have done it via VBA in EXCEL, it is more easier than by autoit, thanks for your comment in this topic.

[Subz]

Here is how you could have done it in Autoit:

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

Local $aDiskInfo[0][80], $aWorkbook
Local $sWorkbook = @ScriptDir & "\HDD_Performance_Result.xlsx"
Local $sSheetInput = "Test Log - 12 Core CPU"
Local $bSheetDelete = False ;~ Change to True to create new sheet.
Local $sSheetOutput = "Example Output"

Local $oExcel = _Excel_Open()
If @error Then Exit
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit _Excel_Close($oExcel)

; Find all occurrences of value "HGST HUS726020ALS210 A907" (partial match)
Local $aResult = _Excel_RangeFind($oWorkbook, "HGST HUS726020ALS210 A907", $oWorkbook.Sheets($sSheetInput).Usedrange.Columns("B:B"))
    If @error Then Exit
For $i = 0 To UBound($aResult) - 1
    $aRow = StringSplit($aResult[$i][2], "$")
    $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.Worksheets($sSheetInput).Range("A" & $aRow[3] & ":CB" & $aRow[3]))
    _ArrayAdd($aDiskInfo, $aWorkbook)
Next
If $bSheetDelete Then _Excel_SheetDelete($oWorkbook, $sSheetOutput)
Local $oSheet = _Excel_SheetAdd($oWorkbook, Default, False, 1, $sSheetOutput)
    If @error And @error <> 3 Then Exit
Local $iRow = $bSheetDelete = True ? 1 : $oWorkbook.Sheets($sSheetOutput).UsedRange.Rows.Count + 1
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Sheets($sSheetOutput), $aDiskInfo, "A" & $iRow)