Jump to content

Get the last written cell in excel


Recommended Posts

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $oExcel = _Excel_Open()
lobal $sWorkbook = _Excel_BookNew($oExcel)

$oExcel.ActiveWorkBook.SaveAs(@ScriptDir & "\Resultados_ola.xlsx")

Global $aLastRow = $sWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
;~      Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
;~      Global $aLastRow = $sWorkbook.Activesheet.UsedRange.Rows.Count




_Excel_RangeWrite($sWorkbook, $sWorkbook.Activesheet, "OLA", $aLastRow+1)

_Excel_BookClose($sWorkbook)
_Excel_Close($sWorkbook)

I got part of this code from other topics, but then i read that the Excel UDF changed and now i don't know what code works anymore xD

So I want to get the last row to get written so i can write in the next row. I tried serveral codes that i found in the forum (the ones in comment) but i couldn't get the value no matter what i did. What am i doing wrong?

Thank you in advance

Edited by EvilRubberDuck
Link to comment
Share on other sites

The code you posted already uses the latest version of the Excel UDF.
How to get the latest used cell can be found in the wiki.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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

 

Link to comment
Share on other sites

Because you open a new workbook which is always empty.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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

 

Link to comment
Share on other sites

Okey you are right, but with this new code it still doesn't work:

#include <Excel.au3>
#include <MsgBoxConstants.au3>





            Global $sFilePath1 = @ScriptDir & "\Resultados_ola.xlsx"
            ConsoleWrite($sFilePath1 & @LF)

                If FileExists($sFilePath1) = 0 Then
                    Global $oExcel = _Excel_Open()
                    Global $oWorkbook = _Excel_BookNew($oExcel)
                    _Excel_BookSaveAs($oWorkbook, @ScriptDir & "\Resultados_ola.xlsx", "xlsx")
                    

                Else
                Global $oExcel = _Excel_Open()
                Global $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath1)

                Global $oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
                ConsoleWrite($oRange & @LF)

                EndIf




            _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "OLA", $oRange+1)
            
            _Excel_BookClose($oWorkbook)
            _Excel_Close($oExcel)

 

Link to comment
Share on other sites

I didn't much care for the consolewrite line what i wanted was that each time i ran the script it write "OLA" under the last cell written. But it did help solve my problem because knowing that i had to add ".row" made it work in the rangewrite! So tahnk so much for the help!

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "OLA", "A" & $oRange.row+1)

            _Excel_BookClose($oWorkbook)
            _Excel_Close($oExcel)

 

Link to comment
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
 Share

×
×
  • Create New...