EvilRubberDuck

Get the last written cell in excel

7 posts in this topic

#1 ·  Posted (edited)

#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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

If you notice my script:

Global $aLastRow = $sWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)

I already use the code in the wiki. What i want to know is why it doesn't work in my script.

For example if i write after 

consolewrite($aLastRow)

I get no value...

Share this post


Link to post
Share on other sites

Because you open a new workbook which is always empty.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
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)

 

Share this post


Link to post
Share on other sites
Global $oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
ConsoleWrite($oRange & @LF)

The resulting value in $oRange is an object, which explains why you can't output it to the console. Instead, try:

ConsoleWrite($oRange.row & @LF)

 

1 person likes this

Share this post


Link to post
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)

 

Share this post


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

  • Similar Content

    • Paranthaman
      By Paranthaman
      Hi Everyone,
      I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly.
      What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite .
      _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered
      What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns
      i.e) For i=0 loop, every array content should be written in A Column of excel
      For i = 1 loop, every array content should be written in B Column of excel.
       
      Can anyone give me an idea of  how can i do this? Thanks 
    • SkysLastChance
      By SkysLastChance
      Why is my code not writing X in cell D1? 
      #include <Excel.au3> Global $r = 1,$oExcel Excel () Func Excel() While ProcessExists("EXCEL.EXE") $ms = MsgBox(5,"","Process error. You have an Excel sheet open. You must close it in order to let this program work. Please close it now.") If $ms=2 Then Exit WEnd Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)") If FileExists($sExcelFile) Then $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file. Else $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist. EndIf EndFunc Sleep (2000) _Excel_RangeWrite($oExcel,Default, "X", "D" & $r) Exit  
    • Suneel
      By Suneel
      Hi
      I want to select an excel file from a folder using selenium. The file name includes the data stamp. But, only that file will be existing in the folder. How to do it please help with the code
    • SkysLastChance
      By SkysLastChance
      I was wondering if it is possible to make this code skip to the next row if it reads a blank line?
      I tried this.
      If $aArray = "" Then $r += 1 EndIf #include <Excel.au3> Local $r = 1 Local $aArray = _Excel_RangeRead($oWorkbook) ;~ If $iBox is greater than no. of rows in $aArray then $iBox equals the number of rows in $aArray If $iBox > (UBound($aArray) - 1) Then $iBox = UBound($aArray) - 1 For $i = 1 To UBound($aArray) - 1 ;$i =0 Start from row A If $aArray = "" Then $r += 1 EndIf $sR0 = $aArray[$i][0] ;status $sR1 = $aArray[$i][1] ;first name $sR2 = $aArray[$i][2] ;Last name $sR3 = $aArray[$i][3] ;Last 4 SSN $sR4 = $aArray[$i][4] ;DOB $sR5 = $aArray[$i][5] ;Email Address $sR6 = $aArray[$i][6] ;Mailling Address $sR7 = $aArray[$i][7] ;City $sR8 = $aArray[$i][8] ;state $sR9 = $aArray[$i][9] ;Zip Code $sR10 = $aArray[$i][10] ;Gender $sR11 = $aArray[$i][11] ;Phone WinSetState ("ADM.MCK - Registration Management Desktop","",@SW_MAXIMIZE) WinWaitActive("[CLASS:Notepad]", "", 10) ControlSend("[CLASS:Notepad]", "", "", ("{F8}")) Sleep (500) ControlSend("[CLASS:Notepad]", "", "", ("{F8}")) Sleep (500) ControlSend("[CLASS:Notepad]", "", "", ("N")) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (3000) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR1 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR2 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR6& @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "", ("{TAB}")) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR7 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR8 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR9 & @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR11& @CR) Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR5 & @CR) ;Email Sleep (200) ControlSend("[CLASS:Notepad]", "", "Edit1", $sR5 & @CR) ;Email Sleep (200) ControlSend("[CLASS:Notepad]", "", "", (FormatDate($sR4) & @CR)) $r += 1  
       
    • FrancescoDiMuro
      By FrancescoDiMuro
      Hi guys! How are you? Hope you're fine
      I've been using Excel UDF for a while, and I always wanted to post this question:
      "Why, everytime I set the paramter $bVisible = False of the _Excel_Open() function, IF I HAVE AN EXCEL SHEET OPENED, I still can see the Excel opening and doing what I wrote in the script? And, in this case, how can I avoid this?"
      Thanks