Jump to content

Get the value of last cell used in row


Recommended Posts

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

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 comment
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 comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...