drapdv Posted May 5, 2010 Share Posted May 5, 2010 I'm sure someone else has asked about this but I couldn't find it in a search.... I have some code that reads several arrays from Excel and then writes them in other places. The arrays tend to be pretty large, some in excess of 10,000 entries. Because of that, it take awhile, and I'd love to have a progress bar show the status of the reads/writes as they go along but at the moment all I can get it to do is start and end. I could use an Excel write function but it takes SIGNIFICANTLY longer, which seems antithetical. Oh yes, here's the code: ;Part Number Formatter - puts to REFINED DATA WinSetState($oExcel, "", @SW_SHOWMAXIMIZED) $oExcel.Run("XMLParser") For $a = 0 To UBound($aMakes, 1) - 1 $oXML = $oExcel.Sheets("XML DATA-"&$aMakes[$a][0]) $oXML.Activate $aColumn = $aMakes[$a][1] $lastRow = _ExcelReadCell($oExcel, "AA1") ProgressOn("Progress", "Reading XML DATA-"&$aMakes[$a][0]&" Array", "Working...") $aXML = _ExcelSheetReadArray($oExcel, "XML DATA-"&$aMakes[$a][0], 2, 11, $lastRow, 1) ProgressSet(100, "Done!") Sleep(750) ProgressOff() For $n = UBound($aXML, 1) - 1 To 0 Step -1 If StringStripWS($aXML[$n], 8) = "" Then _ArrayDelete($aXML, $n) Next $u = UBound($aXML, 1) -1 ReDim $aXML[$u] $oRefDat.Activate ProgressOn("Progress", "Writing XML DATA-"&$aMakes[$a][0]&" Array", "Working...") _ExcelSheetWriteArray($oExcel, $xlsRefDat, 4, $aColumn, $aXML, 1) ProgressSet(100, "Done!") Sleep(750) ProgressOff() $oXML = 0 $aXML = 0 Next Link to comment Share on other sites More sharing options...
Spiff59 Posted May 5, 2010 Share Posted May 5, 2010 (edited) The progress bar routines have no idea what your script may be doing or how long anything takes. The bar is manually driven. You have to calculate when to set the bar to 10%, or 25%, etc. It can't automatically track the progress of a file I/O statement. Glancing at your script, the best you could do is something like divide 100 into UBound($aMakes, 1), and increment a progress bar that amount for each loop of the For/Next statement. Edit: trashed all the quoted text Edited May 5, 2010 by Spiff59 Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 5, 2010 Share Posted May 5, 2010 Arrays of a few tens of thousands elements are not scary. The _Excel* array functions are due for a re-write in light of this information: The Excel COM interface can get/put arrays with AutoIt. If you tweak some custom functions with that, they should be blazing fast. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
drapdv Posted May 5, 2010 Author Share Posted May 5, 2010 Arrays of a few tens of thousands elements are not scary. The _Excel* array functions are due for a re-write in light of this information: The Excel COM interface can get/put arrays with AutoIt. If you tweak some custom functions with that, they should be blazing fast.OOOooooo.......this is very interesting indeed......Thank you! 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