Jump to content
Radiohead22

_Excel_RangeFind and Write $Results & .rows

Recommended Posts

Radiohead22
Posted (edited)

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)

result_write.jpg

result_diplay.jpg

original file.jpg

Edited by Jos

Share this post


Link to post
Share on other sites
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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Radiohead22
Posted (edited)

 

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 by Jos
Stop creatig similar topics and stick to this one.!

Share this post


Link to post
Share on other sites
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 :(

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Radiohead22

WOW your are great :)

With _Excel_RangeCopyPaste it writes perfectly on sheet2  

_ArrayDisplay don't show whole rows but only results again :(

diplay.jpg

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×