Read data from specific Row

[CiaronJohn]

Hi AutoITers,

I have a basic question, I have an excel worksheet with a lot of data in it, but I only want to acquire the data of the entire ROW? Is there any possible way to do this? I tried using _Excel_RangeRead with parameter of ROW but no value was acquired.

Func doCountermeasure()

    Local $oExcel  = _Excel_Open(False)
    Local $testdesign = _Excel_BookOpen( $oExcel, StringTrimLeft(FileReadLine  ( $errorlogFile, 2 ), 20), True, True ) ;open test sheet loaded in FSUnit tool
    Local $aWorkSheets = _Excel_SheetList($testdesign) ;list worksheets of the test design
    Local $file = @ScriptDir & "\TempFile.txt" ;create temporary text file
    FileOpen($file, 2)
    Local $worksheets = _FileWriteFromArray  ( $file, $aWorkSheets ) ;populate text file with name of worksheets
    Local $aInput

    _FileReadToArray($file, $aInput)

    For $i = 3 to UBound($aInput) - 2 ;loop to different worksheets
        ;MsgBox (0,'',$aInput[$i])StringTrimRight( $aInput[$i], 1)
        Local $factors = _Excel_RangeRead ( $testdesign, StringTrimRight( $aInput[$i], 1), "E26") ;gather data from row E26 of each worksheets
        _ArrayDisplay ($factors)
    Next
    _Excel_BookClose ( $testdesign )
    ;FileDelete( $file )

EndFunc

Thank you!

 

 

[CiaronJohn]

Just now, CiaronJohn said:

Hi AutoITers,

I have a basic question, I have an excel worksheet with a lot of data in it, but I only want to acquire the data of the entire specific ROW? Is there any possible way to do this? I tried using _Excel_RangeRead with parameter of ROW but no value was acquired.

Func doCountermeasure()

    Local $oExcel  = _Excel_Open(False)
    Local $testdesign = _Excel_BookOpen( $oExcel, StringTrimLeft(FileReadLine  ( $errorlogFile, 2 ), 20), True, True ) ;open test sheet loaded in FSUnit tool
    Local $aWorkSheets = _Excel_SheetList($testdesign) ;list worksheets of the test design
    Local $file = @ScriptDir & "\TempFile.txt" ;create temporary text file
    FileOpen($file, 2)
    Local $worksheets = _FileWriteFromArray  ( $file, $aWorkSheets ) ;populate text file with name of worksheets
    Local $aInput

    _FileReadToArray($file, $aInput)

    For $i = 3 to UBound($aInput) - 2 ;loop to different worksheets
        ;MsgBox (0,'',$aInput[$i])StringTrimRight( $aInput[$i], 1)
        Local $factors = _Excel_RangeRead ( $testdesign, StringTrimRight( $aInput[$i], 1), "E26") ;gather data from row E26 of each worksheets
        _ArrayDisplay ($factors)
    Next
    _Excel_BookClose ( $testdesign )
    ;FileDelete( $file )

EndFunc

Thank you!

 

 

 

[Nine]

use .EntireRow method.  See here.

[CiaronJohn]

Hi Nine,

 

I already tried that, but all i got is '0'.

[argumentum]

save as CSV and read the line you wanna get  

[CiaronJohn]

Hi argumentum,

 

Thank you. But I have multiple worksheets in a file. So, I think it will be harder to gather necessary data from that.

[water]

The .EntireRow method is the way to go. Please post the code you used that does not work. 

[CiaronJohn]

Hi water,

We figured the problem out using this; $testdesign.sheets($i).Cells(27,$irows). But my problem now is on how to determine the last row with entry.

[CiaronJohn]

Hi water and Nine,

 

My problems are solved. Thanks to the excel UDF! Mabuhay from PH!

[water]

One last comment:
The wiki is a good source: https://www.autoitscript.com/wiki/Excel_Range#Entire_Row_.2F_Entire_Column

 

[CiaronJohn]

 

 

Hi @water, I have a question sill regarding this topic. Is there any possible way to extract data under "Expected values" column? Row 24 by the way are merged cells per sections.

 

Thank you!

[water]

Simply use _Excel_RangeRead to read this 4 columns. Merged cells will be returned in the leftmost cell of the array.

Means: You read columns K:N the string "expected values" would be returned in row 23, column 0 of the returned array.