Excel - Worksheet Index.

[s3nsei 0]

Good day ,

Please help me, i'm trying to count the used of an excel's row, below is the sample code i got to count the first Worksheet, i need to count the other worksheet as well, but i don't know the correct code, actually i've looked around in msdn library, but i'm too idiot to find the answer.

Anyone can help? Thanks a lot!

CODE :

$Count = $oExcel.Worksheets(1).Usedrange.Rows.Count

[water 1,771]

Did you notice that AutoIT comes with an EXCEL UDF (User Defined Function)? Please see the help file for the available functions.

Use

$aArray = _ExcelSheetList($oExcel)

to get an aray with the names of all sheets. $aArray[0] contains the number of available sheets. Use this number as index.

[s3nsei 0]

Did you notice that AutoIT comes with an EXCEL UDF (User Defined Function)? Please see the help file for the available functions.

Use

$aArray = _ExcelSheetList($oExcel)

to get an aray with the names of all sheets. $aArray[0] contains the number of available sheets. Use this number as index.

Omg, thank you!!

[water 1,771]

Glad to be of service

[s3nsei 0]

Did you notice that AutoIT comes with an EXCEL UDF (User Defined Function)? Please see the help file for the available functions.

Use

$aArray = _ExcelSheetList($oExcel)

to get an aray with the names of all sheets. $aArray[0] contains the number of available sheets. Use this number as index.

Later..

Still encountered error, please kindly tell how to fix it, the code :

$Sheet = _ExcelSheetList($oExcel)

$Count = $Sheet[1].Usedrange.Rows.Count

Error message : Object referenced outside a "With" statement.

Edited November 11, 2010 by s3nsei

[Juvigy 28]

Change $Sheet[1] to $Sheets(1) or $Sheet(1) - dont have time to check

Edited November 11, 2010 by Juvigy

[water 1,771]

Change $Sheet[1] to $Sheets(1) or $Sheet(1) - dont have time to check

Use something like

$aArray = _ExcelSheetList($oExcel)
For $iIndex = 1 to $aArray[0]
  MsgBox(0,"", "Sheet " & $iIndex & " has " & $oExcel.Worksheets($iIndex).Usedrange.Rows.Count & " used rows")
Next

[JoHanatCent 7]

To get a Total of all sheets:

$Sheet =_ExcelSheetList($oExcel)
For $i = 1 To $Sheet[0]
    $Count += $oExcel.Worksheets($i).Usedrange.Rows.Count
Next
MsgBox(0, 'Total', $Count)

Edited November 11, 2010 by JoHanatCent

[s3nsei 0]

Thanks a lot guys, it worked!

[s3nsei 0]

Duh, i need another instruction..

The idea is : count used row in each sheet1-3, then write to the next column of each row

My code :

$oExcel = _ExcelBookOpen($sFilePath,0)

For $iIndex = 1 to 3

$Sheet = $oExcel.Worksheets($iIndex)

$Count = $Sheet.Usedrange.Rows.Count

For $x = 1 To $Count

_ExcelWriteCell($Sheet, 0, $x, 2)

Next

Next

END CODE

It count used row successfully, but i can't write the column with _ExcelWriteCell function,

any idea / suggestion?

Thank you so much.