Jump to content

Get the value of last cell used in row


Recommended Posts

  • Replies 66
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

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

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

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&a

Posted Images

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

 

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
 

Link to post
Share on other sites

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

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

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

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.

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.

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...