Jump to content
Sign in to follow this  
saywell

Excel question

Recommended Posts

saywell

I'm a very basic excel user, and have never before needed to use the Excel UDF.

I have now been asked to write a script that will process a very large csv file, which will mean working through each cell column by column, altering its content according to set rules.

This looks quite easy using _ExcelReadArray and its write equivalent, then looping through it doing what's necessary.

However, the files are huge - the first is over 12,000 rows and will get bigger each quarter through the year.

My question is; how do I get the number of rows?

_ExcelReadSheetToArray gives the number of rows in [0][0] but it takes ages to read in [hence my doing it one column at a time]. the ReadArray function requires the number of rows to be provided.

Is there a more elegant way of getting this value?

Regards,

William

Share this post


Link to post
Share on other sites
water

You could use _ExcelBookOpen and specify the CSV file to open. $oExcel.ActiveSheet.UsedRange.Rows.Count should tell you how many rows to process.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
saywell

Thanks, water, that works a treat!!

Regards,

William

Share this post


Link to post
Share on other sites
water
;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Sign in to follow this  

×