FrancescoDiMuro

Came back with help about Excel functions :D

11 posts in this topic

#1 ·  Posted (edited)

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 by FrancescoDiMuro

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by FrancescoDiMuro

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites

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.

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :D

Share this post


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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

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 

Share this post


Link to post
Share on other sites

#11 ·  Posted

Something like this:

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


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