read & copy data from excel but only cells whit number and text?

[Skistus]

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

[Subz]

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?

[abberration]

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

 

[Skistus]

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

[Skistus]

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?

[abberration]

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

[Skistus]

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!