Jump to content

Recommended Posts

Posted (edited)

Hi all,

 

I've been pulling my hair out. I try to find out the row number of the last row being used to append new data into an excel sheet. I tried various commands and they always return something different but not the number they should. In the most recent example it always returns 10 even though I only got a single string in A1. So I would expect to get 1 as result, not (row) 10:

 

#include <Excel.au3>
Local $path = @MyDocumentsDir & "\result.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $path)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
 EndIf
 
 ;$lastrow = $oExcel.Range("A1").SpecialCells($xlCellTypeLastCell)
 ;local $LastRow = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
 ;Local $LastRow = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
  $lastrow = $oExcel.ActiveSheet.UsedRange.Rows.Count
 ;$lastrow = $oWorkbook.ActiveSheet.Cells.SpecialCells($xlCellTypeLastCell).row
 MsgBox(0,"lastrow", $lastrow)

 _Excel_Close($oWorkbook)

 

result.xlsx

Edited by Automania
Posted (edited)

Found the error. I had 2 excel sheets open and it seemed to take the value from the other one (wasn't really obvious until I took a different excel file). Even though I deleted the $oExcel object (as well as tried $oExcel1 for the other excel file) it still referenced to the wrong Excel file that was opened first. Wasn't aware of that. Now that I closed one Excel file it referenced the correct row value from the other, desired Excel file.

Edited by Automania
Posted

How to query different Excel Ranges is described in the Excel wiki: https://www.autoitscript.com/wiki/Excel_Range

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

Thanks :)

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

 

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