Jump to content

_excel_rangeRead() return cell index


Go to solution Solved by water,

Recommended Posts

Hello everybody 

When I use _excel_rangeRead() function with $vRange value set to Default, is there a way to add the Row Index of the excel file at a column in the returned array ?

For example at the following excel file excel.jpg.30e965f2e708689042a34061de870287.jpg

 I need the returned array to be like this array.jpg.4728cc25cee4a2b7aa78057e2ba4941f.jpg

As far as I understand the function needs to be modified in the <Excel.au3> UDF. I tried using the "Cell.IndexRow" property, but my poor understanding of VBA doesn't help.

I would appreciate any help you could give me.

Thanks

 

Coding can be fun when you do it your own.

Link to post
Share on other sites
  • Solution

Something like this?

#include <Excel.au3>

; Prepare the workbook
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel, 1)
Global $aData[][] = [["A.A", "name", "phone", "mail"], _
        [1, "andreas", 12345678, "a@gmail.com"], _
        [2, "michael", 12345, "mic@yahoo.gr"], _
        [3, "john", 1234567, "jc@gmail.gr"]]
_Excel_RangeWrite($oWorkbook, 1, $aData, "B2")

Global $aRead = _Excel_RangeRead($oWorkbook, 1)                           ; Read the used range of the worksheet to an array
ReDim $aRead[UBound($aRead, 1)][UBound($aRead, 2)+1]                      ; Add a column to the array
Global $iFirstRow = $oWorkbook.Sheets(1).UsedRange.Row                    ; Get the first row number of the used range
Global $sFirstCell = $oWorkbook.Sheets(1).UsedRange.Range("A1").Address   ; Get the address of the top left cell of the used range
Global $ilastCol = UBound($aRead, 2)-1                                    ; Add the row index of the used range to the array
For $i = 0 To UBound($aRead, 1)-1
    $aRead[$i][$iLastCol] = $i + $iFirstRow
Next
_Excel_RangeWrite($oWorkbook, 1, $aRead, $sFirstCell)                     ; Write the modified array to the worksheet

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-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 (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
Quote

Global $iFirstRow = $oWorkbook.Sheets(1).UsedRange.Row                    ; Get the first row number of the used range
Global $sFirstCell = $oWorkbook.Sheets(1).UsedRange.Range("A1").Address

This was the "key" code that I couldn't figure out.

Thank you very much !!!

Coding can be fun when you do it your own.

Link to post
Share on other sites

😃

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-11-10 - Version 1.6.0.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-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 (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...