Came back with help about Excel functions :D

[FrancescoDiMuro]

Good morning everybody! I came back with new fresh questions about Excel functions...
My intent is to read a portion of the sheet, that could not be always in the same position and, more important, it will never be the same "lenght"... 
By the way, the only thing that could be the same, is the # of cells, but not the # of rows... Can someone help me out, please? Thank you so much guys! 

PS: I'm not working with Pivot Table

Edited November 25, 2016 by FrancescoDiMuro

[water]

How would you select this range if you had to do it by hand?

[FrancescoDiMuro]

10 minutes ago, water said:

How would you select this range if you had to do it by hand?

I got this header, that is always the same:
Column D -> 'ADDRESS, Column E-> 'TAG, Column F -> 'Description.
In the same sheet there are 4 of this header, and the pattern is always the same. 
I need to copy and manipulate data in these 3 columns, and finished a header, I should continue in a different way with the next one, and so on, until the last.
But the thing is that the row # in always different... So... That's the file I'm working with... I need to copy all digital inputs, and manipulate them, then all digital outputs, and manipulate them... And so on... 
Please, help me

ALL.2_Copia di LISTA I-O PLC 122 REV0.xls

PS: And I want to skip rows where the column D row X is blank.

Edited November 25, 2016 by FrancescoDiMuro

[FrancescoDiMuro]

Working on? I was looking at R code... Maybe it's more indicated for this kind of things...

2 hours ago, water said:

How would you select this range if you had to do it by hand?

 

[water]

The worksheet only contains a few rows/columns. I would simply read the whole worksheet into an array and then loop through the array to search for the needed start/end of the range to process.

 

[FrancescoDiMuro]

28 minutes ago, water said:

The worksheet only contains a few rows/columns. I would simply read the whole worksheet into an array and then loop through the array to search for the needed start/end of the range to process.

 

Can you do an example for reading D:G columns until they ends? Thanks

[water]

Read the whole worksheet into an array and only process columns D and G:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; ************************
; Read the whole worksheet
; ************************
Local $aResult = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Process columns D and G of the worksheet
For $i = 0 To UBound($aResult) - 1
    $sColumnD = $aResult[$i][3] ; Process column "D" . "D" is column number 4 which is offset 3 in the array
    $sColumnG = $aResult[$i][6] ; Process column "G" . "G" is column number 7 which is offset 6 in the array
Next

 

Edited November 25, 2016 by water

[FrancescoDiMuro]

14 minutes ago, water said:

Read the whole worksheet into an array and only process columns D and G:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

; ************************
; Read the whole worksheet
; ************************
Local $aResult = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult)

; Process columns D and G of the worksheet
For $i = 0 To UBound($aResult) - 1
    $sColumnD = $aResult[$i][3] ; Process column "D" . "D" is column number 4 which is offset 3 in the array
    $sColumnG = $aResult[$i][6] ; Process column "G" . "G" is column number 7 which is offset 6 in the array
Next

 

Thanks water Now I'm reading the whole file, and storing D and G columns in 2 string variables, isn't it?
Now I should process that data and paste it in another file... How can I skip a row based on the value stored in the row***, column E or F? Ty for your help

[water]

; Process columns D and G of the worksheet
For $i = 0 To UBound($aResult) - 1
    If $aResult[$i][4] = "Value1" Or $aResult[$i][5] = "Value2" Then ContinueLoop ; If column E or F contain the specified value then the next row in the array will be processed
    ; Write the needed data to another file here
Next

 

[FrancescoDiMuro]

1 minute ago, water said:

; Process columns D and G of the worksheet
For $i = 0 To UBound($aResult) - 1
    If $aResult[$i][4] = "Value1" Or $aResult[$i][5] = "Value2" Then ContinueLoop ; If column E or F contain the specified value then the next row in the array will be processed
    ; Write the needed data to another file here
Next

 

Thank you water Another question... At the end of this data manipulating, I have to make a csv file ( the file I have to create ), and that file has to be formatted in this way... "Column1";"Column2";..."Column n";... How can I do it?  Ty <3 

[water]

Something like this:

$sStringToWrite = '"' & $aResult[$i][4] & '";"' & $aResult[$i][5] & '"'