Tralots

Excel, unique ref

8 posts in this topic

#1 ·  Posted

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!

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#3 ·  Posted

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.


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

#4 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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

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

#6 ·  Posted (edited)

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 by water
2 people like this

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

#7 ·  Posted

Hi water,

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

Share this post


Link to post
Share on other sites

#8 ·  Posted

:)


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