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 ... 37
1
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

2
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

3
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

4
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

5
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

6
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

7
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

8
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


9
Help and Support / Re: auto resize function called multiple times
« on: February 14, 2017, 09:16:49 AM »
Hi,

can you try setting pythonhome or executable in the pyxll config please? e.g.

[PYTHON]
executable = c:\pythonXX\python.exe

I'm not sure why it would have worked in 3.1 without this, and fails with 3.2... but that config change should hopefully work.

Best regards,
Tony

10
Help and Support / Re: auto resize function called multiple times
« on: February 13, 2017, 10:39:32 PM »
Hi,

I think I've found the cause of the problem. Please could you try the latest beta build to confirm that it's working for you? You can download it from:

http://beta.pyxll.com/download.html#standalone
username: pyxll
password: pyxll

Thanks for your help finding this problem! Please let me know if this resolves it for you or not.

Best regards,
Tony

11
Help and Support / Re: auto resize function called multiple times
« on: February 13, 2017, 11:27:13 AM »
Hi,

thanks, I'll try and reproduce the problem and get back to you.

Best regards,
Tony

12
Help and Support / Re: auto resize function called multiple times
« on: February 13, 2017, 10:38:43 AM »
Hi,

when the resized contents are written to Excel it's done after Excel has finished calculating, and so will trigger a recalculation of anything that depends on the affected cells. As long as the inputs to your function don't depend on the outputs this shouldn't matter and your function shouldn't get called again...

There is a very simple auto_resize example included with pyxll. Do you see the same behaviour if you add a print statement to that?

Best regards,
Tony

13
Hi,

great, thanks for letting me know!

Ah, the problem with the pandas example is that it doesn't explicitly import pandastypes, and so when reloaded pandas_examples is reloaded before the pandastypes module and so the types haven't yet been declared. I'll fix that :)

Best regards,
Tony

14
Hi,

I think that may be the same problem with deep reloading that was reported recently. It sounds as if it could be caused by the module that CookieJar is declared in being reloaded after the module that uses it.

If it is the same problem, it should be fixed in the next release. You can try the beta version that already has this fix, which can be downloaded from:
http://beta.pyxll.com/download.html#standalone (username: pyxll  password: pyxll)

Please can you let me know if that solves your problem or not?

Best regards,
Tony

15
Help and Support / Re: Reloading causes failure of catching exceptions
« on: January 06, 2017, 12:08:23 PM »
Great, thanks for the help identifying and fixing this problem!

Best regards,
Tony

Pages: [1] 2 3 ... 37