Jump to content
antmar904

Get the value of last cell used in row

Recommended Posts

antmar904

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
alien4u

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

excel.png

Edited by alien4u

Share this post


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

 

Share this post


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

Share this post


Link to post
Share on other sites
antmar904

Hi @alien4u

Exp

ex2.JPG.e088b80ca4780e311034cf582707cd72

 

Output from

output2.JPG.b8e0499e423e92207b5c6af3cfd2

Edited by antmar904

Share this post


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

Share this post


Link to post
Share on other sites
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 by alien4u
Fix typo in code

Share this post


Link to post
Share on other sites
antmar904

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
alien4u

Fixed using index 0 for the array.

Regards
Alien.

Share this post


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

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

Share this post


Link to post
Share on other sites
antmar904

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

Share this post


Link to post
Share on other sites
antmar904

Thanks again!

ill post my findings.

Share this post


Link to post
Share on other sites
water

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

Share this post


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

Share this post


Link to post
Share on other sites
water

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.