Get the value of last cell used in row

[alien4u]

11 hours ago, water said:

I'm on vacation right now. Will answer at the end of next week. 

OK water thank you, in the mean time I will try and read to see if I can do it.

Thanks you again.

Regards
Alien.

[alien4u]

Hi again @antmar904
Thanks to @water for the suggestion and to @Jfish for the help I found a better way to achieve what you request using native Excel Objects and Methods so is more efficient and you don't need to read the entire Worksheet to an array and then parse that array, the fact is no array is needed.

The final code is like this:
 

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
; This will give you the Number of Used Rows in Column A that u will use like a base for finding last used cell per each row
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
; With a For loop from Row 1 to the final used Row in Column A
For $i = 1 To $countRow
    ; Number of Columns in each Row and we need that last used column number
    With $oWorkbook.ActiveSheet
        $countCol = .Cells($i, .Columns.Count).End($xlToLeft).Column
    EndWith
    ; We need to translate Colunm Number to Letter to use that in RangeRead()
    $ColLetter = _Excel_ColumnToLetter($countCol)
    $aResult = _Excel_RangeRead($oWorkbook, Default,$ColLetter&$i , 1)
MsgBox(0,"",$aResult)
Next

And here you have @Jfish last code:

#include <Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
;Number of Rows
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    ; Number of Columns in 1 Row
    With $oWorkbook.ActiveSheet
    $LastUsedCellInRow = .Range("IV"&$i).End($xlToLeft).Address
    $LastUsedCellInRow = StringReplace($LastUsedCellInRow,"$","")
    $aResult = _Excel_RangeRead($oWorkbook, Default,$LastUsedCellInRow , 1)
EndWith
MsgBox("","",$aResult)
Next

Regards
Alien.

Edited April 18, 2016 by alien4u

[alien4u]

The Shortest way so far without the need of calling _Excel_RangeRead():

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    EndWith
MsgBox(0,"",$lastColvalue)
Next

Regards
Alien.
 

[Trong]

I think no need "AutoIt" to generate the report.
With this request can easily with itself (Excel)!

[alien4u]

2 hours ago, VIP said:

I think no need "AutoIt" to generate the report.
With this request can easily with itself (Excel)!

Obviously is not about the report is about get that data from Excel and do something more with AutoIT base on that data.

Regards
Alien.

[Trong]

On 15/4/2016 at 11:15 PM, antmar904 said:

Hi All,

I am trying to get the value of the last cell used in every row (shown in red).

Exp:

 

Output I need:

Try This:
 

expandcollapse popup

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

Global $ExcelFile = @ScriptDir & "\LNExport.xlsx"

Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
Local $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, Default, Default)
If @error Then
    MsgBox(0, "Error", "Error opening the workbook")
    _Excel_Close($oExcel)
    Exit
EndIf

Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default, 1)
If @error Then
    MsgBox(0, "Error", "Unable to read workbook")
    _Excel_BookClose($oWorkbook)
    Exit
EndIf
;~  _ArrayDisplay($aResult, "Data", "")
;~ _Excel_BookClose($oWorkbook)
Local $aArray_2D[UBound($aResult, 1) - 1][2]
Local $LastCOL, $zResult
For $i = 1 To UBound($aResult, 1) - 1
    $FristROW = $aResult[$i][0]
    For $j = 0 To UBound($aResult, 2) - 1
        If $aResult[$i][$j] <> "" Then $LastCOL = $aResult[$i][$j]
    Next
    $aArray_2D[$i - 1][0] = $aResult[$i][0]
    $aArray_2D[$i - 1][1] = $LastCOL
Next
_ArrayDisplay($aArray_2D, "Data", "")
_Excel_BookClose($oWorkbook)
;~ WinClose("Microsoft Excel")
Exit

 

[water]

To me it seems the solution posted in #23 is the most elegant way to retrieve the last cell of each row.

[water]

On 16.4.2016 at 6:10 AM, alien4u said:

Also try to use _Excel_RangeFind() but none of the examples on the Help File works.
Error with all of them:
"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

Regards
Alien.

This is a bug in the 3.3.14.0 implementation of COM error handling. Use 3.3.12.0 to fix this problem.

[antmar904]

I am also still working on this, ill post what I have a little later on.

Thanks again for all your help.

[water]

On 18.4.2016 at 1:50 AM, alien4u said:

The Shortest way so far without the need of calling _Excel_RangeRead():

#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl,@ScriptDir&"\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
     $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    EndWith
MsgBox(0,"",$lastColvalue)
Next

Regards
Alien.
 

Added an example how to get the last cell of a column to the wiki.

[antmar904]

@water

That worked great, thank you.

[water]

[antmar904]

Hello again.

So I'm still not getting the results that I wanted.

Basically what I am doing is exporting our Lotus Notes Global Address book which I can then look up a user and get their computer name.

But, the LN export will show all of the users computers old and new but I just want the new computer name which is usually the last computer name in the "Client Machine" column.

Computer names are seperated by a ",".

I would like write the last computer name to a new column called "Primary Computer".

Again, any help is very much appriciated.

Here is an example of a LN export:

[BrewManNH]

What do you get back using the code supplied?

[antmar904]

I'm getting the correct data returned from the org exp I posted however that was in the wrong format.

So with the exp I just posted which is the correct format I am getting a message box with value listed in column C.

 

What I would like is to write the last computer name in the new column labled "Primary Comptuer", exp:

[BrewManNH]

Well, you'll need to find the last occurrence of a computer name from the returned value, the easiest way would be to use stringsplit and find the last entry in the array using UBound.  Maybe something like this (untested)

#include <Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Excel1.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
    $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    EndWith
    MsgBox(0, "", $lastColvalue)
    $aLastComputer = StringSplit($lastColvalue, ",")
    $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
    MsgBox(0, "", $sLastComputer)
Next

 

[antmar904]

@BrewManNH

 

This worked well if I comment out ;MsgBox(0, "", $lastColvalue) so the msgbox just gives me the last computer name.

How can I then write $aLastComptuer to the "Primary Computer" column?

Thanks again.

[BrewManNH]

I'm not writing the script for you, you'll have to start doing the work yourself on how to put a value into an Excel sheet. Look in the help file for the Excel functions.

[antmar904]

@BrewManNH

Thanks again for your help.

#include <Excel.au3>
Local $oAppl = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Test.xlsx") ; your workbook here
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
    $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    ;MsgBox(0, "", $lastColvalue)
    $aLastComputer = StringSplit($lastColvalue, ",")
    $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
    ;MsgBox(0, "", $sLastComputer)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "D"&$i, "", "")
    EndWith
Next

 

[antmar904]

So my workbook has about 11,500 rows but my script stops entering the last computer name @ row  1138.