Radiohead22 Posted April 8, 2018 Share Posted April 8, 2018 (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) Edited April 8, 2018 by Jos Link to comment Share on other sites More sharing options...
Developers Jos Posted April 8, 2018 Developers Share Posted April 8, 2018 @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 comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 (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 April 8, 2018 by Jos Stop creatig similar topics and stick to this one.! Link to comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 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 Link to comment Share on other sites More sharing options...
Developers Jos Posted April 8, 2018 Developers Share Posted April 8, 2018 Just now, Radiohead22 said: very sorry i forgot to translate before No problema 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 comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 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 comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 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 comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 "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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 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 comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 WOW your are great With _Excel_RangeCopyPaste it writes perfectly on sheet2 _ArrayDisplay don't show whole rows but only results again Link to comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 _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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Radiohead22 Posted April 8, 2018 Author Share Posted April 8, 2018 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 Link to comment Share on other sites More sharing options...
water Posted April 8, 2018 Share Posted April 8, 2018 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now