_Excel_RangeFind and Write $Results & .rows

[Radiohead22]

Hi all,
sono nuovo del forum, ho bisogno di cercare una parola (o numero) in un file .xlsx molto grande di circa 20000 righe e di scrivere il risultato (e tutta la riga corrispondente) dentro al file stesso (o in un nuovo file .xlsx)
sono riuscito a cercare la parola e scriverla nel file ma non riesco a fare visualizzare l'intera righa della parola trovata (per ogni ricerca può trovare anche 1000 corrispondeze della parola stessa)
ho provato con _Excel_RangeWrite e con _ArrayDisplay, scrive e visualizza la ricerca ma non la riga corrispondente alle parole trovate.

Per favore aiutatemi, grazie in anticipo.

Ecco il mio codice:

Jos-Google translate:

Quote

I'm new to the forum, I need to search for a word (or number) in a very large .xlsx file of about 20000 lines and to write the result (and the whole corresponding line) inside the file itself (or in a new .xlsx file )
I was able to search for the word and write it in the file but I can not show the entire line of the word found (for each search can also find 1000 matches of the word itself)
I tried with _Excel_RangeWrite and with _ArrayDisplay, it writes and displays the search but not the line corresponding to the words found.

Please help me, thanks in advance.

#include <Array.au3>
#include <Excel.au3>
$CODE = "paraurti"
$file1 = @ScriptDir & '\Test.xlsx'
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $file1)
$sWorkbook = _Excel_BookAttach($oWorkbook)
Local $Result = _Excel_RangeFind($oWorkbook, $CODE)
_Excel_RangeWrite($oWorkbook, Default, $Result, "L1")
;~ _ArrayDisplay($Result)

Edited April 8, 2018 by Jos

[Jos]

@Radiohead22,

Welcome, Could you please post your questions in English as this is a English only forum?

Thanks,
Jos

[water]

Welcome to AutoIt and the forum!

With the help of Google translate I hope to understand what you are looking for.
Untested:

For $i = 0 to UBound($Result, 1) - 1
    $oEntireRow = $oWorkbook.Sheets($Result[$i][0]).Range($Result[$i][2]).EntireRow
    ; Process the Row here
Next

[water]

By the way:
Drop the following line as it is not needed. _Excel_BookAttach connects to an already opened workbook (opened by the user or another program). But you open the workbook yourself:

$sWorkbook = _Excel_BookAttach($oWorkbook)

 

[Radiohead22]

 

Hi all, I'm new to the forum, I need to search for a word (or number) in a very large .xlsx file of about 20000 lines and to write the result (and the whole corresponding line) inside the file itself (or in a new .xlsx file ) I was able to search for the word and write it in the file but I can not show the entire line of the word found (for each search can also find 1000 matches of the word itself) I tried with _Excel_RangeWrite and with _ArrayDisplay, it writes and displays the search but not the line corresponding to the words found. Please help me, thanks in advance. Here is my code:

 

Edited April 8, 2018 by Jos
Stop creatig similar topics and stick to this one.!

[Radiohead22]

8 minuti fa, Jos ha detto:

@ Radiohead22 ,

Benvenuto, puoi postare le tue domande in inglese in quanto questo è un forum solo in inglese?

Grazie,
Jos

 

very sorry i forgot to translate before

[Jos]

Just now, Radiohead22 said:

 

very sorry i forgot to translate before

No problema

Jos

[Radiohead22]

15 minutes ago, water said:

Welcome to AutoIt and the forum!

With the help of Google translate I hope to understand what you are looking for.
Untested:

For $i = 0 to UBound($Result, 1) - 1
    $oEntireRow = $oWorkbook.Sheets($Result[$i][0]).Range($Result[$i][2]).EntireRow
    ; Process the Row here
Next

Very thanks Water for the reply,

but it does not work, where am I wrong?

 

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

$CODE = "paraurti"

$file1 = @ScriptDir & '\Test.xlsx'

Local $oExcel = _Excel_Open()

Local $oWorkbook = _Excel_BookOpen($oExcel, $file1)

Local $Result = _Excel_RangeFind($oWorkbook, $CODE)

For $i = 0 to UBound($Result, 1) - 1
    $oEntireRow = $oWorkbook.Sheets($Result[$i][0]).Range($Result[$i][2]).EntireRow
    _Excel_RangeWrite($oWorkbook, Default, $Result, "L1")
Next

[Radiohead22]

 

I tried with:

Local $Result = _Excel_RangeFind($oWorkbook, $CODE)
For $i = 0 to UBound($Result, 1) - 1
    $oEntireRow = $oWorkbook.Sheets($Result[$i][0]).Range($Result[$i][2]).EntireRow
    _Excel_RangeWrite($oWorkbook, Default, $oEntireRow, "L1")
Next

but it does not work too

[water]

"Does not work" means: You get an error message? You get a wrong result? You get no result at all?
Please be as specific as possible describing what doesn't work

[Radiohead22]

You're right I'll be more specific

I modified it a little bit, but I can not continue

 

Local $Result = _Excel_RangeFind($oWorkbook, $CODE)

For $i = 0 to UBound($Result, 1) - 1

$oEntireRow = $oWorkbook.Sheets($Result[$i][0]).Range($Result[$i][2]).EntireRow

$sResult = _Excel_RangeRead($oWorkbook, Default, $oEntireRow)

_Excel_RangeWrite($oWorkbook, Default, $sResult, "A20")

_ArrayDisplay($sResult)

Next

 

with _Excel_RangeWrite, it writes the same result from the A20 line down, but only writes the last result found repeated

with _ArrayDisplay  displays the results found but shows them one by one not all in the same window (a window result) and in any case no more than 3 results even if it finds 10 for example.

do you think there is a way to display (or write) the whole row from column "A" to column "I" instead of the whole row?

I'm sorry to bother you but I need it for work, thank you very much

 

[water]

The following example looks for a value of 120000 in the example workbook and copies all found rows to a second worksheet.
If the source range contains relative references to other cells then they will be lost when copying.

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbookIn = _Excel_BookOpen($oExcel, "C:\Temp\_Excel1.xls")
Global $aResult = _Excel_RangeFind($oWorkbookIn, 120000)
_ArrayDisplay($aResult)
For $i = 0 To UBound($aResult, 1) - 1
    $oSheet = $oWorkbookIn.Sheets($aResult[$i][0])
    $oEntireRow = $oSheet.Range($aResult[$i][2]).EntireRow
    _Excel_RangeCopyPaste($oSheet, $oEntireRow, $oWorkbookIn.Sheets(2).Range("A" & $i+1))
Next

When you replace 120000 with the string "Story" then it perfectly works.

[Radiohead22]

WOW your are great

With _Excel_RangeCopyPaste it writes perfectly on sheet2  

_ArrayDisplay don't show whole rows but only results again

[water]

_ArrayDisplay does only display the array created by _Excel_RangeFind. And _Excel_RangeFind just returns the cell where the search string was found.
If you need to display the whole row then you need to create an array holding the whole rows and then display this extended array.

[Radiohead22]

 

I have no idea how to do I know autoit for about 2 years and I love it, but I've always had problems with arrays, however write the result on sheet2 is already a great thing, thank you so much for your great work

[water]