s3nsei Posted November 11, 2010 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
water Posted November 11, 2010 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
s3nsei Posted November 11, 2010 Author 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!!
water Posted November 11, 2010 Posted November 11, 2010 Glad to be of service My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
s3nsei Posted November 11, 2010 Author 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
Juvigy Posted November 11, 2010 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
water Posted November 11, 2010 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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
JoHanatCent Posted November 11, 2010 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
s3nsei Posted November 12, 2010 Author 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.
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