Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - Tony

Pages: [1] 2 3 ... 39
1
Help and Support / Re: Script to add pyxll into Excel
« on: May 24, 2017, 02:15:28 PM »
That'd be great! You can either send me anything directly, or as a pull request to the pyxll-examples repo on github.

Best regards,
Tony

2
Help and Support / Re: Script to add pyxll into Excel
« on: May 23, 2017, 06:48:29 PM »
Hi Alfredo,

unfortunately I don't have anything to give you that does that... but I think what you want should be possible and not too tricky.

To register the addin, you have to add it to a couple of registry keys (where * is the version of Excel you are using):
 - HKCU/Software/Microsoft/Office/*/Excel/Options
 - HKCU/Software/Microsoft/Office/*/Excel/Add-in Manager

In the "Options" key you need to look values named "OPENx", and if there isn't one pointing to a pyxll.xll file add a new one and set it to '/R "{your pyxll.xll file location}"'.
In the "Add-in Manager" key you need to add a string value with the name equal to the path of the pyxll.xll file.

There's an example file that shows how to find these keys to delete them (to uninstall pyxll), which might be helpful to figure out how to create the keys :)
https://github.com/pyxll/pyxll-examples/blob/master/scripts/uninstall.py

You can look at your own registry using regedit to see what these keys look like, and use the uninstall script so you can compare before and after installing. Be careful that you are editing the right area of the registry as there are different areas for 32 bit and 64 bit applications (the uninstall.py example shows how to open both - and in regedit the 32 bit keys are under the "Wow6432Node", assuming you are using 64 bit windows).

Before you install an addin you need to make sure all running Excel processes are closed, otherwise they'll just over-write your changes when they exit.

Sorry I don't have anything that just does what you need, but I hope this helps.

Best regards,
Tony

3
Help and Support / Re: Assign Shortcut to PageUp Key
« on: May 22, 2017, 10:08:34 AM »
Hi,

this is not currently supported. None of the special keys (Page Up/Down, Home, Insert, cursor keys, numlock, scroll lock, tab etc) can be mapped to shortcuts at present. Function keys can be mapped.

This is something we will look at adding in a future version, but for now you will have to use another key.

Best regards,
Tony

4
Help and Support / Re: return assert message in excel
« on: May 04, 2017, 07:18:16 PM »
Hi,

oh yes, I didn't spot that in your code. You can only use default keyword arguments when not specifying a signature.

If you change your code to:

Code: [Select]
@xl_func
@format exceptions
def my_func(a, b=default_b):
    return b

then it should work. Otherwise, you are quite correct that the function is registered with Excel to have exactly two arguments and Excel calls it with its own default if one is missing.

Best regards,
Tony

5
Help and Support / Re: return assert message in excel
« on: May 04, 2017, 04:52:57 PM »
Hi,

I think all you need is to add kwargs to the function wrapper.

Code: [Select]
def format_exceptions(func):
    @wraps(func)
    def func_wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except Exception as e:
            return "#ERROR: " + str(e)
    return func_wrapper

"wraps" is part of the standard library and you can find more information about it here: https://docs.python.org/2/library/functools.html

Best regards,
Tony

6
Help and Support / Re: Excel Crash
« on: April 29, 2017, 05:55:39 PM »
Hi,

I found what was causing the problem you were having when upgrading PyXLL, and have fixed it version 3.2.3 (just released).

You will probably still have an issue upgrading from 3.2.2 to 3.2.3, but your workaround of disabling the COM addin and re-enabling it after should work again. Once you are on 3.2.3, upgrades to future versions shouldn't have this problem.

Apologies for any inconvenience,
Tony

7
Hi Alfredo,

the final 3.2.2 version didn't actually have any change to the async_call behaviour, in the end. Once I found the actual bug that was causing the freezing/crashing problem I fixed that and didn't use the change I tried in first beta build to call the async callbacks on the main thread.

The first and second error you're getting are related. The first is VBA_E_IGNORE, which usually means that Excel is doing something else at the time of your call. The third is RPC_E_CALL_REJECTED, which is also caused by Excel being busy at the time of the call. I'm not sure what the second one could be, but I suspect it's related.

The async callback is called on a background thread after Excel returns control to the windows message loop (this hasn't changed since 2.x and 3.2.2). It sounds to me like what's happening is because the async callback is triggering more calculations, which are then triggering more async calls the callbacks end up running in the background thread at the same time Excel is calculating, and so that's why you will frequently see these errors.

Previously when people have asked about these errors I've suggested disabling automatic calculations during the async callback, but even doing that will fail if excel is already busy so that will have to be retried until it succeeds. That is usually sufficient as once automatic recalculations are disabled Excel is free to process the calls made in the async callback on the background thread.

There's some code here that shows what I mean (I think this might be the one you originally replied to):
https://www.pyxll.com/_forum/index.php?topic=353.msg1127#msg1127

The problem would also be solved (perhaps better than just disabling automatic calculations) by wrapping the callback code in a macro, as once Excel is running the macro it won't allow other calls or calculations to interrupt it. While investigating the crashing/freezing problem I did experiment with replacing async_call with a new version that does that, although that didn't make it into 3.2.2. I will look at including that change in a future release, as I think it may get rid of the need for you to add any retry/macro code in Python - but in the meantime I hope that the work arounds I've suggested are enough to get your code working.

Best regards,
Tony

8
Help and Support / Re: return assert message in excel
« on: April 20, 2017, 07:14:38 PM »
Hi,

you need to convert the exception to a string in order to return it to Excel as a full error message. Exceptions that aren't caught get translated to error codes as outlined here: https://www.pyxll.com/docs/userguide/udfs.html#passing-errors-as-values

You could use a decorator to catch exceptions and convert them to strings, something along the lines of:

Code: [Select]
from functools import wraps

def format_exceptions(func):
    @wraps(func)
    def func_wrapper(*args):
        try:
            return func(*args)
        except Exception as e:
            return "#ERROR: " + str(e)
    return func_wrapper

@xl_func
@format_exceptions
def my_func():
    raise Exception("error")

Note that you will want to use "var" as your return type (if specified) so that you can return strings as well as the normal return type of your function.

I hope this helps.

Best regards,
Tony

9
Hi Alfredo,

great, I'm glad that's working for you.

With regard to those errors, unfortunately retrying a number of times (although not elegant) I think will be necessary.

A possible improvement could be wrap what you want to do in a macro, and in the async_call you can try to call that macro. Once in the macro, I think you will have exclusive access to Excel and so shouldn't get those errors (but you can still get them when trying to run the macro!), eg:

Code: [Select]
from pyxll import xl_macro, xl_app

@xl_macro
def my_macro(args):
    xl = xl_app()
    # do stuff here (no need to retry)

def async_call_callback(tries_left):
    try:
        xl = xl_app()
        xl.Run("my_macro", args)
    except:
        # retry
        if tries_left <= 0:
            raise
        async_call(async_call_callback, tries_left-1)

I hope that helps. If you find this approach is good let me know, as I am considering making async_call run the callback in a macro in the same way described above.

Best regards,
Tony

10
Help and Support / Re: Excel Crash
« on: April 19, 2017, 09:08:44 AM »
Hi,

that's great! Glad you were able to find a workaround.

This latest 3.2.2 release fixes some stability problems, like the one you've described, introduced in 3.1 - so hopefully this will not be a problem for you again.

Please let me know if you have any more problems.

Kind regards,
Tony

11
Help and Support / Re: Excel Crash
« on: April 18, 2017, 11:51:25 AM »
Hi,

has this problem started after upgrading to 3.2.2?

Please can you try un-installing PyXLL by removing it from the list of addins in Excel, then closing Excel, re-opening and re-adding PyXLL?

It's important that you close all instances of Excel after removing pyxll from the list of addins before re-opening to try again.

You can also try using this script to completely uninstall pyxll before re-installing it:
https://github.com/pyxll/pyxll-examples/tree/master/scripts

Kind regards,
Tony

12
Hi Alfredo,

I've just released 3.2.2, including the fix for the problems you were seeing with async_call and automatic array resizing.

I also fixed that last issue you were having when exiting Excel would sometimes crash because of the background Python thread not being shutdown cleanly.

I hope this has resolved all your issues now (except the one where Excel is sometimes busy when you try to call into it from a background thread, for which hopefully your workaround is satisfactory). If you do have any more problems when testing with your users please let me know.

Kind regards,
Tony

13
Help and Support / Re: Error opening COM addin
« on: April 12, 2017, 11:20:29 AM »
Great! That's for letting me know :)

14
Hi Alfredo,

yes that first problem is quite common. When calling into Excel if it's busy it will give you an error like that. In your cases, as you're calling back into Excel potentially while other calculations are going on you will probably see it quite often. You should add a small sleep (e.g. "time.sleep(10)") into your solution so whatever thread is causing Excel to be busy is able to continue.

I had an idea to change async_call to invoke the user function from inside an Excel macro, ensuring that your call would never be called until Excel was ready. This is a bigger change than the previous bug fix, but I will try it out and if it looks like a good solution I will include it in a future version.

That second problem is more concerning... I suspect that some background thread is not being shutdown correctly when Excel is closing, and then later tries to destroy the Python thread state after Python has been shutdown. I will investigate that and get it fixed.

Best regards,
Tony

15
Hi Alfredo,

that's great :) I have to do a release today or tomorrow anyway. I'm confident the changes I ended up making to fix this issue are an improvement and haven't introduced new problems, so I will include them in that release. If you find with your testing that there are still problems I can do another patch release soon after, it's not a problem.

Best regards,
Tony

Pages: [1] 2 3 ... 39