Get the value of last cell used in row

[antmar904]

Hi All,

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

Exp:

 

Output I need:

[alien4u]

Code you try or use? to help you because I don't have time now to make everything from scratch.

Regards
Alien.

[alien4u]

From the Help file:
 

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
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, @ScriptDir & "\Excel1.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Excel1.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range on sheet 2 of the specified workbook
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, 1, "A1:A5:F1:F5", 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:A5 to F1:F5 of sheet 1")

Play with the resulting Array with all the data and you will be able to extract last character of each row.

Regards
Alien.

Edited April 15, 2016 by alien4u

[antmar904]

Hi alien4u,

Thank you

I was already able to display the workbook into an array, I'm just unsure on how to get the value of the last cell in each row.

 

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

Local $oExcel = _Excel_Open()
If @error Then
    MsgBox(0, "Error", "Error creating Excel object")
    _Excel_Close($oExcel)
    Exit
EndIf
 Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\LNExport.xlsx", 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)
Exit

 

[alien4u]

Hi @antmar904

To many ways to list, test this right after your last EndIf:
 

Global $result = "|"

 Local $iRows = UBound($aResult, $UBOUND_ROWS)
 Local $iCols = UBound($aResult, $UBOUND_COLUMNS)

 For $i = 1 To $iRows -1
    For $j = 1 To $iCols -1
        If $aResult[$i][$j] <> "" Then
        $result &= $aResult[$i][$j]&","
    EndIf
    Next
        $result = StringTrimRight($result,1)
    $result &= "|"
Next

 MsgBox("","",$result)

 

Regards
Alien
 

[antmar904]

Hi @alien4u

Exp

 

Output from

Edited April 15, 2016 by antmar904

[alien4u]

Yes and also with delimiters everything inside |is for one row| delimiting columns by ,

You just need to understand it and modify it, anyways I will try something else...

[alien4u]

@antmar904

This will output only the final character:
 

Global $result = "|"

 Local $iRows = UBound($aResult, $UBOUND_ROWS)
 Local $iCols = UBound($aResult, $UBOUND_COLUMNS)

 For $i = 0 To $iRows -1
    For $j = 0 To $iCols -1
        If $aResult[$i][$j] <> "" Then
        $result &= $aResult[$i][$j]&","

    EndIf
    Next
        $result = StringTrimRight($result,1)
        $resultend = StringRight($result,1)
        MsgBox("","",$resultend)
    $result &= "|"
Next

Try it.

Edited April 15, 2016 by alien4u
Fix typo in code

[antmar904]

yes it did

 

I am going through your code and trying to understand it, thank you.

 

However the real data will contain more than just one charactor.  It will be the users comptuer name.  exp (usadananttest).

 

Edited April 15, 2016 by antmar904

[alien4u]

Fixed using index 0 for the array.

Regards
Alien.

[antmar904]

I'm still not understanding how to get the value of the last cell in a row.

I tried modifying the code but I'm not getting it.

exp

output

I would like the output to be the users name in column A then the value of that last cell in the users row

Edited April 15, 2016 by antmar904

[alien4u]

For getting a value large than a single character you need to modify the code.

Something like this should work:

 

Global $result = "|"

 Local $iRows = UBound($aResult, $UBOUND_ROWS)
 Local $iCols = UBound($aResult, $UBOUND_COLUMNS)

 For $i = 0 To $iRows -1
    For $j = 0 To $iCols -1
        If $aResult[$i][$j] <> "" Then
            $result &= $aResult[$i][$j]&","
        EndIf
    Next
        $result = StringTrimRight($result,1)
        $resultend = StringRight($result,StringInStr($result,",",0,1))
        If StringInStr($resultend,",") Then
            $resultend = StringTrimLeft($resultend,1)
        EndIf
            MsgBox("","",$resultend)
    $result &= "|"
Next

 MsgBox("","",$result)

Try it.

Regards
Alien.

[antmar904]

Can I get this to show in an array box with the values of column A showing?

Edited April 15, 2016 by antmar904

[alien4u]

You can do anything you want, I help you with the hard part, you need now to read about Arrays, StringTrimLeft(), StringTrimRight(), StringInSrt() and also String Concatenation.

Everything is on the Help File.

Regards
Alien.

[antmar904]

Thanks again!

ill post my findings.

[water]

Please check the wiki. It explains how to get the last used cells directly from Excel. 

[alien4u]

Hi @water

I read this: https://www.autoitscript.com/wiki/Excel_UDF and I read this: https://www.autoitscript.com/wiki/Excel_Range

But I don't understand how to get last cell used on every row with that?

Could you please show us an example?

Regards
Alien.

[alien4u]

7 hours ago, alien4u said:

Hi @water

I read this: https://www.autoitscript.com/wiki/Excel_UDF and I read this: https://www.autoitscript.com/wiki/Excel_Range

But I don't understand how to get last cell used on every row with that?

Could you please show us an example?

Regards
Alien.

I end up trying this:

Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell), 1)

The problem with this is I'm only able to get the last cell value on the Worksheet but I'm not able to get last cell value per row.

I already try:

Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.Range("A1:A5").SpecialCells($xlCellTypeLastCell), 1)

;Also Tried:
Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.Range("A1:A5").Row.SpecialCells($xlCellTypeLastCell), 1)
Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.Range("A1:A5").SpecialCells($xlCellTypeLastCell).Row, 1)
Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.Range.Row.SpecialCells($xlCellTypeLastCell), 1)
Local $aResult = _Excel_RangeRead($oWorkbook, Default,$oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell).Row, 1)

I'm working base on trial and error because with my Internet connection is near impossible for me to open msdn.microsoft or technet.microsoft I only have a very bad Dial-Up connection at only 53Kbps and I can't find anything useful on the Help File or AutoIT wiki.

Any help would be nice.

Thanks.

Regards
Alien.

[alien4u]

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.

[water]

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