Jump to content

[solved] Last row in Excel returns wrong value


Recommended Posts

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
Link to post
Share on other sites

Try the following --

  • Open your spreadsheet in Excel
  • Press Ctrl+End

Note that the selected cell changes to G10. This indicates that Excel thinks there is data somewhere other than A1, so IMO the behavior you are experiencing is correct.

Edit: See here for details on how to fix

Edited by Danp2
Link to post
Share on other sites
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
Link to post
Share on other sites

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

Thanks :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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