antmar904

Get the value of last cell used in row

67 posts in this topic

Hi All,

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

Exp:

ex.JPG.adc0e4a394b25cd5155d98b13f0c3789.

 

Output I need:

5711133fabbe0_exoutput.JPG.470e5e37b0836

Share this post


Link to post
Share on other sites



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

Regards
Alien.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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.

excel.png

Edited by alien4u

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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
 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Hi @alien4u

Exp

ex2.JPG.e088b80ca4780e311034cf582707cd72

 

Output from

output2.JPG.b8e0499e423e92207b5c6af3cfd2

Edited by antmar904

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

@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 by alien4u
Fix typo in code

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

yes it did

output3.JPG.4e73f527848ce0d0696da56aa34d

 

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 by antmar904

Share this post


Link to post
Share on other sites

Fixed using index 0 for the array.

Regards
Alien.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

data.JPG.791fc01f5c1291be2e5214dd9a6f83c

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

Capture.JPG.a309cb0b03c94d943ed76211450a

Edited by antmar904

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

Edited by antmar904

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thanks again!

ill post my findings.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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