Skistus Posted May 22, 2022 Share Posted May 22, 2022 (edited) Hello!! I'll try to explain my idea a little bit, because I really don't know how to explain it... we have an accounting in excel where in different boxes we only insert the name of the person and previously the numbers are assigned ... what I would like to do is to copy only the boxes that contain number and name, ignoring those that only contain name. reading in the forum I found this function to copy the excel document... or the selected boxes. #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xlsx", True) Local $oRange = $oWorkbook1.ActiveSheet.Range("D5:G17") _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, $oRange) with this function you would be copying the boxes of the excel ... Quote 1 TEST 3 2 2 TEST 2 TEST 3 3 TEST 2 3 TEST 3 TEST 3 15 EST 4 TEST 4 TEST 3 4 4 5 TEST 3 15 TEST 15 TEST TEST 6 TEST 3 6 6 7 TEST 3 8 TEST TEST 8 8 TEST 3 3 3 9 TEST 3 10 JTESTT 10 TEXT 10 TEST 3 3 3 11 TEST 3 15 JTTEST TEST 15 TEST9 12 TEST 3 2 2 13 TEST 3 15 TEST 15 that is what I would be copying... what I would like to do is to delete all the ones that have no name since they were not assigned so we would not need them, the idea is just to copy and leave in a variable or clickboard the ones that have number + name for use. would this be possible? can you help me understand? Edited May 22, 2022 by Skistus Link to comment Share on other sites More sharing options...
Subz Posted May 22, 2022 Share Posted May 22, 2022 Difficult to understand your question, maybe attach a test spreadsheet so we don't have to recreate it and post what the expected result should be. Since unsure if you want the entire row or individual cell or what qualifies for "number + name", i.e. is 1 TEST 3 or 2 TEST or 8 TEST TEST? Link to comment Share on other sites More sharing options...
abberration Posted May 23, 2022 Share Posted May 23, 2022 I created a 3x3 Excel sheet with similar data to what you showed. This example is not pretty, but might be helpful. Not sure if you just wanted a list that would be easier to use, but this keeps the same structure, but empties the cells you don't want to see. #include <Array.au3> #include <Excel.au3> Local $oExcel =_Excel_Open(1) ; Open your Excel sheet $Book = _Excel_BookOpen($oExcel,@ScriptDir & "\test.xlsx") $aData = _Excel_RangeRead($Book, Default, "A1:C3") ; Read sheet to array _Excel_BookClose($Book) $oWorkbook = _Excel_BookNew($oExcel) For $i = 1 To 3 For $j = 1 To 3 If StringRegExp($aData[$i-1][$j-1], "[a-z]|[A-Z]") = 0 Then ; If no letters are present then... $aData[$i-1][$j-1] = "" ; Overwrite cell with nothing EndIf Next Next _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aData) ; Write data to new sheet Easy MP3 | Software Installer | Password Manager Link to comment Share on other sites More sharing options...
Skistus Posted May 23, 2022 Author Share Posted May 23, 2022 here I attach my test excel Hello Subz in excel fill the cells with numbers, total prize or participations, and then add the name of the person who touches that cell ... would be a cell with number + name, the idea is to copy the entire range of participants and delete the cells that do not have names ... as they will not participate, I do not know if I make myself understood ? abberration your script is what I mean how interesting really what you can do knowing autoit .... you can not read the ranges of the cells to load them automatically in the $i = 1 To "3" and For $j = 1 To "3" as sometimes the excel document sometimes we can use 13 x 13 depends how many people we add to the document.... also you can pass all the result to a single row ? to be able to copy and paste it out of excel for its management, excuse me so much I have much ignorance if so I would appreciate if you continue commenting on the parts of the code to better understand how it works. !!!! and really ty ty _Excel1.xlsx Link to comment Share on other sites More sharing options...
Skistus Posted May 23, 2022 Author Share Posted May 23, 2022 update... For $i = 1 To Ubound($aData,1) For $j = 1 To Ubound($aData,2) If StringRegExp($aData[$i-1][$j-1], "[a-z]|[A-Z]") = 0 Then ; If no letters are present then... $aData[$i-1][$j-1] = "" ; Overwrite cell with nothing EndIf Next Next using Ubound I can get the dimensions, rows and column ... now how to pass the whole result to a single column? Link to comment Share on other sites More sharing options...
abberration Posted May 24, 2022 Share Posted May 24, 2022 (edited) Hello, Skistus AutoIt can do amazing things. I hope this code will help you and I hope you will read my comments and learn a bit how it works. If you have any questions, I will try my best to answer (or if I'm not here, some one will). One thing that I did is make the cell $aOutput[0] hold the number of names that it finds. I like to do this in case I need to go through a loop to find something. Just before writing the data to the Excel file, I used _ArrayDelete so it did not get written. #include <Array.au3> #include <Excel.au3> Local $oExcel =_Excel_Open(1) ; Open your Excel sheet $Book = _Excel_BookOpen($oExcel,@ScriptDir & "\_Excel1.xlsx") $aData = _Excel_RangeRead($Book, "Hoja1", "D5:P17") ; Read sheet to array _Excel_BookClose($Book) _ArrayDisplay($aData) ; view current array Local $aOutput[2] ; make new array for the names $aOutput[0] = 0 For $i = 1 To UBound($aData, 1) For $j = 1 To UBound($aData, 2) If StringRegExp($aData[$i-1][$j-1], "[a-z]|[A-Z]") = 1 Then ; If it is a name, add to new array $aOutput[0] += 1 ; increase the first array number ReDim $aOutput[$aOutput[0] + 1] ; resize the array $aOutput[$aOutput[0]] = $aData[$i-1][$j-1] ; write name to array EndIf Next Next _ArrayDisplay($aOutput) ; show new array _ArrayDelete($aOutput, 0) ; Delete first array cell that contains number of names $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aOutput) ; Write data to new sheet Edited May 24, 2022 by abberration Easy MP3 | Software Installer | Password Manager Link to comment Share on other sites More sharing options...
Skistus Posted May 29, 2022 Author Share Posted May 29, 2022 sorry it took me so long to answer, abberration the code is perfect!!! thank you very much for the help!! and thanks for the comments although it doesn't look like it helps!!! it's time to learn more about autoit! 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