Jump to content

_Excel_RangeFind and Write $Results & .rows


Recommended Posts

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

@Radiohead22,

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

Thanks,
Jos

SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Link to post
Share on other sites

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

 

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

Link to post
Share on other sites

 

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

Link to post
Share on other sites

"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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

_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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...