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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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 (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.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

    • Virgilio1
      By Virgilio1
      Salve Amici,
      in un mio progetto vorrei utilizzare una dll per creare dei file excel senza utilizzare l'applicativo Excel.
      Ora dalla versione 2007 di Office la Microsoft utilizza per i file un nuovo formato aperto "Microsoft Open XML format".
      in PHP questo è molto semplice utilizzando la Libreria PHPExcel (http://www.codeplex.com/PHPExcel)
      Cercando in rete ho trovato una dll che dovrebbe fare lo stesso (https://code.google.com/archive/p/excellibrary/) ma non ho assolutamente le capacita di integrare la Dll in autoit, qualcuno mi può aiutare ?
      Sarebbe veramente molto efficiente poter creare e manipolare file excel in autoit senza dover caricare in memoria l'applicativo Excel.
      Grazie
      -.-.-.-.-.
      Hello friends,
      in my project I want to use a dll to create the excel file without using the Excel application.
      Now from the Microsoft Office 2007 version uses for the files a new open format "Microsoft Open XML format".
      PHP This is very simple using the Library PHPExcel (http://www.codeplex.com/PHPExcel)
      Searching the net I found a dll that should do the same (https://code.google.com/archive/p/excellibrary/) but I have absolutely the ability to integrate the .dll in autoit, anyone can help me?
      It would really be very efficient to create and manipulate Excel files into memory autoit without having to load the Excel application.
      Thank you
    • Sergy
      By Sergy
      I have price-list in xls. When I open it by _Excel_Open and _Excel_BookOpen and after that close by _Excel_Close, I have a message about "Save changes?"
      I try open it in read-only mode, but no changes made. I still see annoing message.
      Windows 10 prof, MS Office 2007.
       
      #include <Excel.au3> #include <MsgBoxConstants.au3> ConsoleWrite(@AutoItVersion) Global $sPriceFile = @ScriptDir&"\opt_pr_list-mini.xls" Local $oExcel = _Excel_Open(False, False, False, True)  If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $sPriceFile, True )     ; readonly If @error Then     MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error opening workbook @error = " & @error & ", @extended = " & @extended)     _Excel_Close($oExcel)     Exit EndIf _Excel_Close($oExcel, False, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example 2", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Exit What I do wrong?
      Thanks.
      opt_pr_list-mini.xls.zip
    • kctvt
      By kctvt
      Hi there, i'm looking for a script to take max number of a column in Excel.
       
      Ex :  Column C , i have : 
      12
      13
      22
      123
      154
      ....
      .....
      .....
      134534
      (About 134600 rows)

      So, How to know which is the max number in Column C.
      I have this code, but it take me a lot of time >"< 
      So... please help me a faster code.
       
      $x = 3 $CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1) $CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2) $CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3) $CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4) $CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5) $CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6) $CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7) $CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8) $CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9) $CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10) Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",") $DMAX = _ArrayMax($aArray, 1, 1) $DMIN = _ArrayMin($aArray, 1, 1) $n = 11 While 1 $CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n) If $CloseCheckn > $DMAX Then Global $DMAX = $CloseCheckn EndIf If $CloseCheckn < $DMIN Then Global $MIN = $CloseCheckn EndIf If $CloseCheckn = "" Then ExitLoop EndIf $n = $n + 1 WEnd _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2")  
       
      Thanks  
       
       
       
       
    • snaileater
      By snaileater
      I'm trying the Excel.udf, my starting point are the examples found in the help.
      My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ...
      New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ...
      What could i be missing ?
      Here's the minimalistic snippet i'm playing with :
      Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... 
      I use the latest releases of AutoIt and Office 97 ...
      Thanks for your help ...
    • PINTO1927
      By PINTO1927
      Hello guys,
      through this example that you find at the end I can not get to write a text in cell A1 example, without being open excel.
      #include <Excel.au3> $oExcel = _Excel_Open(False) $sWorkbook = @DesktopDir & "\test\test.xlsx" $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")  
      while if I make "_Excel_Open(True)" visible, the text is written.