Damein Posted September 23, 2015 Share Posted September 23, 2015 (edited) So this may be asked before but I can't think of a way to ask it or even to describe it in words really so I made a quick video about it. For a quick little like... whats wrong I guess: _Excel_RangeWrite writes data insanely slower when the focus is on the Excel document but writes 50x faster if the focus is not. So.. yeah, not sure why it does this and I imagine I could just simply make the window invisible (Which is 100% fine, no one needs to see it as it outputs it into the Excel document, it never gets touched until a later time) but I just wanted to find out why this is. Edited September 23, 2015 by Damein Most recent sig. I made Quick Launcher W/ Profiles Topic Movie Database Topic & Website | LiveStreamer Pro Website | YouTube Stand-Alone Playlist Manager: Topic | Weather Desktop Widget: Topic | Flash Memory Game: Topic | Volume Control With Mouse / iTunes Hotkeys: Topic | Weather program: Topic | Paws & Tales radio drama podcast mini-player: Topic | Quick Math Calculations: Topic Link to comment Share on other sites More sharing options...
water Posted September 24, 2015 Share Posted September 24, 2015 The Excel UDF does not automate the GUI but uses COM to access Excel methods, properties and collections.The only problem I might see is if a cell is opened by the user for input then even COM has a problem to read this cell.Could you please try to set parameter $bInteractive in _Excel_Open to False so Excel blocks all keyboard and mouse input by the user (except input to dialog boxes) 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...
Damein Posted September 24, 2015 Author Share Posted September 24, 2015 Setting my Excel open to: $oAppl = _Excel_Open(Default, Default, Default, False) Did not change the speed of the input. Most recent sig. I made Quick Launcher W/ Profiles Topic Movie Database Topic & Website | LiveStreamer Pro Website | YouTube Stand-Alone Playlist Manager: Topic | Weather Desktop Widget: Topic | Flash Memory Game: Topic | Volume Control With Mouse / iTunes Hotkeys: Topic | Weather program: Topic | Paws & Tales radio drama podcast mini-player: Topic | Quick Math Calculations: Topic Link to comment Share on other sites More sharing options...
water Posted September 24, 2015 Share Posted September 24, 2015 I have never seen such behaviour before and I have no idea what would cause to slow down the UDF just by moving the mouse over the Excel window.Your script is quite large so it is hard to tell where the problem is.What I have seen from your script is that you are using _Excel_RangeWrite in a loop (I assume you have translated it from the old Excel UDF). With the new UDF you can write a whole array (1D or 2D) with a single call.If you need multiple writes to Excel then I would deactivate ScreenUpdating until all data has been written and then update the screen.Set parameter $bScreenUpdating to False when calling _Excel_Open and use $oExcel.ScreenUpdating = True when you have written all data to the Excel sheet. 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...
Damein Posted September 24, 2015 Author Share Posted September 24, 2015 I will look into the RangeWrite using an array instead of a loop, thanks. As for the updating I did see that when you directed me to use the Interactive parameter. Like I said before they never need to see it really so I think I will either just force it to hide the sheet or just use the Noupdating technique. Just wanted to figure out why it is that it was doing that. Thanks! Most recent sig. I made Quick Launcher W/ Profiles Topic Movie Database Topic & Website | LiveStreamer Pro Website | YouTube Stand-Alone Playlist Manager: Topic | Weather Desktop Widget: Topic | Flash Memory Game: Topic | Volume Control With Mouse / iTunes Hotkeys: Topic | Weather program: Topic | Paws & Tales radio drama podcast mini-player: Topic | Quick Math Calculations: Topic 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