Jump to content
Sign in to follow this  
jackylee0908

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

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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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)

 

Share this post


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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...