[solved] Last row in Excel returns wrong value

[Automania]

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 April 7, 2021 by Automania

[Danp2]

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 April 7, 2021 by Danp2

[Automania]

Thanks again, DanP2. Didn'T know about ctrl+end. I created a completely new file and it shows 1 (wasn't the case yesterday but then I tried some of the disabled commands in the code)! Now knowing that it generally works I can narrow down the issue. Thanks again!

[Automania]

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 April 7, 2021 by Automania

[water]

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

[Automania]

Thanks! Useful guide!

[water]

Thanks