saywell Posted May 24, 2012 Share Posted May 24, 2012 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 Link to comment Share on other sites More sharing options...
water Posted May 24, 2012 Share Posted May 24, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
saywell Posted May 24, 2012 Author Share Posted May 24, 2012 Thanks, water, that works a treat!! Regards, William Link to comment Share on other sites More sharing options...
water Posted May 24, 2012 Share Posted May 24, 2012 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now