Jump to content

Excel, unique ref


Recommended Posts

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!

Link to comment
Share on other sites

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

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

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

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

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

:)

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