Jump to content
EvilRubberDuck

Get the last written cell in excel

Recommended Posts

EvilRubberDuck
#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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
EvilRubberDuck

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
water

Because you open a new workbook which is always empty.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
EvilRubberDuck

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
EvilRubberDuck

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

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×