Excel, unique ref

[Tralots]

Hi People,

After two painfull days searching for the way to do this I`ve decided to join this awesome forum and ask for help.
Basically, what I want to do, is read how many rows exists and for each of them assinge a "unique" reference.
 

data    data    data    data    data    reference0001
data    data    data    data    data    reference0002
data    data    data    data    data    reference0003
data    data    data    data    data    reference0004
data    data    data    data    data    reference0005
data    data    data    data    data    reference0006
data    data    data    data    data    reference0007
data    data    data    data    data    reference0008
data    data    data    data    data    reference0009
data    data    data    data    data    reference0010
data    data    data    data    data    reference0011
data    data    data    data    data    reference0012
data    data    data    data    data    reference0013

Basically i want the excel to look like above.

I would assume that it should be done with arrays, but it`s a completely blank picture for me

Thanks a lot!

[benners]

Have a look at _Excel_RangeRead in the help file. This will read the excel sheet to an array and you can use ubound to get the row count

Edited June 22, 2017 by benners

[water]

So you do not want to process "data" in your script but simply add a "reference" to every row?
If yes, then usedrange is the key.
I can provide a solution if you can confirm my assumption.

[Tralots]

Hi All,

$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")

This is how I find how many rows are present in excel file, as the count will differ every time.
Now whe I know this information, and for instance there is 400rows, I need to assinge unique reference in column F for each of the line.

I`ve been playing arround with usedrange, i can add reference to each of the used rows but it doesn`t increese by 1 for every row.

Hopefully i cleared thing up, Thanks!

 

Edited June 23, 2017 by Tralots

[water]

Something like this (slow version as it writes cell by cell):

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Global $iFirstRow = $oWorkbook.ActiveSheet.UsedRange.Row ; Row number of the first used row
Global $iRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count ; Number of rows in the used range
Global $iCount = 1
Global $iColumn = $oWorkbook.ActiveSheet.UsedRange.Columns.Count + 1 ; Column to write the unique id to
For $i = $iFirstRow To $iFirstRow + $iRows - 1
    _Excel_RangeWrite($oWorkbook, Default, "reference" & StringFormat("%04i", $iCount) , _Excel_ColumnToLetter($iColumn) & $i)
Next

 

Edited June 23, 2017 by water

[water]

Fast version (only a single write to Excel):

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
Global $iFirstRow = $oWorkbook.ActiveSheet.UsedRange.Row ; Row number of the first used row
Global $iRows = $oWorkbook.ActiveSheet.UsedRange.Rows.Count ; Number of rows in the used range
Global $iCount = 1
Global $iColumn = $oWorkbook.ActiveSheet.UsedRange.Columns.Count + 1 ; Column to write the unique id to
Global $aReference[$iRows][1]
For $i = 0 To $iRows - 1
    $aReference[$i][0] = "reference" & StringFormat("%04i", $i + 1)
Next
_Excel_RangeWrite($oWorkbook, Default, $aReference , _Excel_ColumnToLetter($iColumn) & $iFirstRow)

 

Edited June 23, 2017 by water

[Tralots]

Hi water,

Thanks for providing the code, the fast version worked brilliantly, appreciate your help.

[water]