s3nsei Posted November 11, 2010 Share Posted November 11, 2010 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 Link to comment Share on other sites More sharing options...
water Posted November 11, 2010 Share Posted November 11, 2010 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. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
s3nsei Posted November 11, 2010 Author Share Posted November 11, 2010 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!! Link to comment Share on other sites More sharing options...
water Posted November 11, 2010 Share Posted November 11, 2010 Glad to be of service My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
s3nsei Posted November 11, 2010 Author Share Posted November 11, 2010 (edited) 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 Link to comment Share on other sites More sharing options...
Juvigy Posted November 11, 2010 Share Posted November 11, 2010 (edited) Change $Sheet[1] to $Sheets(1) or $Sheet(1) - dont have time to check Edited November 11, 2010 by Juvigy Link to comment Share on other sites More sharing options...
water Posted November 11, 2010 Share Posted November 11, 2010 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
JoHanatCent Posted November 11, 2010 Share Posted November 11, 2010 (edited) 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 Link to comment Share on other sites More sharing options...
s3nsei Posted November 12, 2010 Author Share Posted November 12, 2010 Thanks a lot guys, it worked! Link to comment Share on other sites More sharing options...
s3nsei Posted November 12, 2010 Author Share Posted November 12, 2010 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now