Jump to content

Recommended Posts

Posted

Good day :graduated: ,

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

Posted

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

 

Posted

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!! :graduated:
Posted

Glad to be of service :graduated:

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

 

Posted (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 by s3nsei
Posted

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

 

Posted

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. :graduated:

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...