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] 4 5 ... 39
31
Hi Alfredo,

I've been trying to reproduce this problem with your latest spreadsheet, but so far I've not been able to I'm afraid :(

I tried modifying your sheet so that the calculations were chained, as before, but that worked fine for me too. To try and make it fail I did a lot of clicking around and modifying cells (including the input cell A6) while it was still calculating, but I couldn't make it crash or freeze.

I've attached my worksheet and the function used, but neither this nor your original sheet failed for me :(

I wonder if it's something that's been fixed in an update to Excel, and I'm using a more recent version? I've been testing using Excel 2016 version 1609, build 7369.2120. You can check your version under 'product information' on the right hand side of the account screen in Excel. If your version is older, try getting the latest version and see if that makes any difference.

Best regards,
Tony


32
Ok thanks - I will continue to investigate

33
Hi Alfredo,

hmm that's odd, the following code worked for me:

Code: [Select]
@xl_func(macro=True)
def automation_example(rows, cols, value):
    """copies value to a range of rows x cols below the calling cell"""
    # get the address of the calling cell using xlfCaller
    caller = pyxll.xlfCaller()
    address = caller.address

    # the update is done asynchronously so as not to block some
    # versions of Excel by updating the worksheet from a worksheet function
    def update_func():
        xl = xl_app()

        from win32com.client import constants
        if xl.CalculationState != constants.xlDone:
            print("Still calculating...")
            return

        range = xl.Range(address)

        # get the cell below and expand it to rows x cols
        range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))

        # and set the range's value
        range.Value = value

    # kick off the asynchronous call the update function
    pyxll.async_call(update_func)

    return address

Is that the same as what you were trying?

thanks,
Tony

34
Hi Alfredo,

I think the problem is that the callback is being called while Excel is still calculating... I'll see if I can put something into async_call to defer calling the callback until the calculation is fully complete, and I think that will fix it.

You can try adding this check:

        from win32com.client import constants
        if xl.CalculationState != constants.xlDone:
            print("Still calculating...")
            return

If you add that, I think it will fix the problem.. but note that you shouldn't call async_call again there as that will result in a tight loop that will look like Excel's freezing (but it will just be busy processing and re-queuing all those callbacks!)

Let me know if that helps, and I'll let you know once I've had a chance to look at doing something to async_call to try and deal with this case.

Best regards,
Tony

35
Hi Alfredo,

that's great! :) I can get a new release done pretty quickly (I just need to do some more testing myself and make sure the docs are up to date). I'll aim to have it done before the end of next week, but if you need something sooner than that let me know and I will try to expedite the process.

Kind regards,
Tony

36
Help and Support / Re: activating addinn
« on: March 29, 2017, 02:50:11 PM »
Hi,

that can happen if you are trying to use the 64 bit version of PyXLL and Python with the 32 bit version of Excel, or vice versa.

You will need to install a 64 bit version of Excel if you are using 64 bit Python (or you can switch to a 32 bit version of Python if you prefer, so long as Python, PyXLL and Excel all match!).

Best regards,
Tony

37
Hi Alfredo,

yes, I have been looking at this issue :)

I found that it would sometimes cause Excel to freeze, and sometimes it would work fine. From what I can tell, this is an occasional problem with calling the Excel COM API from a background thread in the Excel process. I can't think of any reason why this should be more noticeable in PyXLL 3 vs 2... the only thing I can think is that now the xl_app/get_active_object functions work slightly differently and are more robust, previously they may have failed before this freeze could occur.

Anyway, I have uploaded a new beta version for you to try to:

https://beta.pyxll.com/download.html
username: pyxll
password: pyxll

This changes the behaviour of async_call so that rather than schedule the call on a background thread, the call is scheduled on Excel's main thread. Your callback will still be called after the function calling async_call has completed and once Excel has completed calculating, so the usage of this function remains the same but the problem with Excel freezing should be resolved (and was in my testing!).

Please let me know if this solves your problem or not!

Kind regards,
Tony

38
Hi Alfredo,

thanks for the workbook - that's very helpful! I can see the problem now. I'll investigate and get back to you asap.

Kind regards,
Tony

39
Hi Alfredo,

yes it should still work.

Can you try the "automation_example" from the included examples? (it's in the examples.xlsx file in the download, and the code is in examples\automation.py).

That example uses "xl_app" instead of the old/deprecated "get_active_object" function, but both functions should work. Perhaps you could try changing your code to use xl_app instead, just to check?

Best regards,
Tony

40
Hi Alfredo,

I think this is different from the problem referenced as that was a specific error when trying to use xl_app, not a problem with Excel freezing or crashing.

Problems with Excel freezing/crashing when updating cells from a worksheet function as you describe are usually caused by an infinite loop in the calculation somewhere.

For example, if your worksheet function updates a cell that then either directly or indirectly causes your function to be called again this results in an infinite loop and so Excel never returns from its calculation cycle. This can either result in Excel freezing or crashing.

When not using xl_app (or get_active_object) then Excel is usually able to detect these cycles before it starts calculating, but when updating cells directly using xl_app/get_active_object in response to a worksheet function being called Excel can't detect these cycles and that results in the problematic behaviour you describe.

The solution is to somehow break this cycle. One way to do that is to check the value of the cell, and only set it if it is different (perhaps checking within some tolerance if the type is a floating point number). That way, when your function is called the second time round the cycle it won't re-set the cell value, breaking the cycle.

Best regards,
Tony

41
Help and Support / Re: Memory limits on 32bit Excel 2010
« on: March 03, 2017, 03:25:55 PM »
Hi,

no, no additional limit set by Python or PyXLL - the full memory available to the process will be available to Python. When I've seen something like this in the past it's been some bug that results in trying to construct something massive - like multiplying a list by a large number, resulting in another list that's too large to be allocated. I hope that helps...

Best regards,
Tony

42
Help and Support / Re: Freeze a Spreadsheet
« on: March 03, 2017, 03:20:38 PM »
Hi,

if everything is calculated when you save the sheet then the values will get saved. If you set the calculation mode in Excel to Manual, it shouldn't recalculate when you open the workbook and so you should see the saved values.

If you don't need to keep the formulas you could replace them with their calculated values before saving a copy to send. You could do that with a menu function or macro, eg:

Code: [Select]
from pyxll import xl_app, xl_menu
from win32com.client import constants

@xl_menu("Replace Formulas with Values")
def copy_values():
    xl = xl_app()
    active_sheet = xl.ActiveSheet
    for sheet in xl.ActiveWorkbook.Sheets:
        sheet.Activate()
        selection = sheet.Range("A1", sheet.Cells.SpecialCells(constants.xlLastCell))
        selection.Copy()
        selection.PasteSpecial(Paste=constants.xlPasteValues,
                               Operation=constants.xlNone,
                               SkipBlanks=True,
                               Transpose=False)
        sheet.Range("A1").Select()
    xl.CutCopyMode = False
    active_sheet.Activate()

Just make sure you have a copy with the formulas saved before you run that!

Best regards,
Tony

43
Help and Support / Re: Memory limits on 32bit Excel 2010
« on: March 01, 2017, 11:36:50 PM »
Hi,

you should be good up to 2Gb for a 32bit process, but make sure you are looking at the virtual memory size and not just the resident bytes (one being what's actually been allocated, the other being just what's currently swapped in). At 350Mb usage it sounds like it could be something else causing your problem (perhaps trying to create a *massive* object or list?).

Best regards,
Tony

44
Help and Support / Re: auto resize function called multiple times
« on: February 14, 2017, 11:16:46 AM »
Hi Yufei,

great, thanks for confirming. Yes, the Ctrl+Shift+R macro example just updates the sheet and so if you have automatic calculation turned on that will cause Excel to recalculate - the built-in auto_resize feature was designed not to do that, and so I'm glad it's working for you now :)

The 3.2 release should be ready in the next few weeks, and will include this fix.

Best regards,
Tony

45
Help and Support / Re: auto resize function called multiple times
« on: February 14, 2017, 10:44:11 AM »
Apologies, I hadn't realised you were using Canopy.

Have you copied pyxll.xll in the same place the original pyxll.xll was installed? If so, there should be a file named 'pyxll-startup.py' in the same folder, and that's what should be setting up the python path when pyxll starts (it's specific to Canopy). That gets added as part of the Canopy packaging, it's not included in the standalone pyxll download.

Alternatively, another way to get it to work is to start a new canopy python prompt and do:

Code: [Select]
import sys
for x in sys.path:
    print(x)

and copy that list of paths to your config (and indent them), ie.:

Code: [Select]
[PYTHON]
pythonpath =
    all paths from sys.path, printed above
    usual python path stuff

I know that's not ideal, but it might get it working again... I'll check the pyxll-startup.py Canopy file from 3.1 should work with 3.2, but that will also be checked as part of the Canopy packaging before the proper 3.2 release (which we don't do for beta releases).

Best regards,
Tony


Pages: 1 2 [3] 4 5 ... 39