Jump to content

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


 Share

Recommended Posts

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

Example.JPG

Edited by jackylee0908
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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)

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...