Damein

Weird bug in _Excel_RangeWrite / Excel UDF / AutoIt?

5 posts in this topic

#1 ·  Posted (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 by Damein

MCR.jpg?t=1286371579

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

Share this post


Link to post
Share on other sites



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 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Setting my Excel open to: $oAppl = _Excel_Open(Default, Default, Default, False)

 

Did not change the speed of the input.


MCR.jpg?t=1286371579

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

Share this post


Link to post
Share on other sites

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 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-03 - Version 1.2.4.0) - 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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!


MCR.jpg?t=1286371579

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

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