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 ... 44
1
Help and Support / Re: Error resizing across sheets
« on: February 16, 2018, 05:22:00 AM »
Hi,

you would have to use the 'var' type and do the type conversion yourself (you can use pyxll.get_type_converter('var', 'date') to get the var->date type converter, which would convert a number to a date).

Alternatively you could create your own date type the parses a string if it's a string, or uses the standard converter otherwise. See https://www.pyxll.com/docs/userguide/udfs.html#type-conversion for details.

Best regards,
Tony

2
Help and Support / Re: Error resizing across sheets
« on: February 15, 2018, 05:27:28 PM »
Hi,

thanks! I was able to reproduce the problem using your example.

The problem is that the array formula is too long to be set using the Excel API. Unfortunately this is a limitation of Excel, and I'm not sure at the moment if it is something I will be able to work around.

I think your best options are either to reference some of the arguments in cells on the same sheet where the array function is and reference them from there, or maybe shortening the name of the sheet name will be enough. I know this isn't ideal, but it should work.

Best regards,
Tony

ps. The array formula maximum length is 255 characters, and your formula is shorter than that so you would think it should work. But, that 255 limit applied after the formula is converted into R1C1 style cell references by Excel, which makes it longer :(

3
Help and Support / Re: Qt platform plugin "windows" error
« on: February 15, 2018, 04:10:18 PM »
Hi,

it looks like QApplication has moved to the QtWidgets sub-package:
https://stackoverflow.com/questions/29259923/pyqt5-cannot-import-name-qapplication

Unfortunately I've not had a chance to look into PyQt5 so I can't say what other changes might be necessary - but, as long as there's a way to poll its event loop then it should be possible to have a non-blocking window. If you manage to get anywhere please do report back and I'll add your findings to the blog post.

Best regards,
Tony

4
Help and Support / Re: Error resizing across sheets
« on: February 15, 2018, 09:31:25 AM »
Hi,

I've tried to reproduce this problem you've described (see attached spreadsheet, which uses the example code included with PyXLL) but I've not been able to I'm afraid.

Could you provide me with a minimal example that shows the problem? Maybe if you could make some changes to the attached spreadsheet I could use that to help figure out what the problem is.

Best regards,
Tony

5
Help and Support / Re: Qt platform plugin "windows" error
« on: February 15, 2018, 09:19:11 AM »
Hi,

try installing PySide (https://pypi.python.org/pypi/PySide/1.2.4). PySide is a Qt4 wrapper, supported by matplotlib. Note that it doesn't support Qt5, that is being done by the PySide2 project and as far as I know that's still a work in progress.

Take a look at the second part of this blog:
https://www.pyxll.com/blog/plotting-in-excel-with-pyxll-and-matplotlib/

That shows how to use PySide and the qt4agg backend to do non-blocking windows.

If you want to side-step everything involved with plotting to a window you could just plot the charts as images and display them directly in Excel, as per the second part of that blog post:
https://www.pyxll.com/blog/plotting-in-excel-with-pyxll-and-matplotlib-part-2/

Best regards,
Tony

6
Just came across this old post and thought I'd update it.

Since PyXLL 3.3 pyxll.async_call now runs the callback in the main thread in an Excel macro context. This means it is safe to do Excel COM operations like writing back to the sheet without having to code complicated retry logic yourself.

Best regards,
Tony

7
News / Version 3.3.2 released
« on: February 14, 2018, 10:38:44 AM »

8
This forum is monitored by PyXLL support staff and usually we try to answer any questions as they come in.

Occasionally we will take a little longer than usual to reply to posts, however.

If you require support you can also email pyxll.support@enthought.com and someone will get back to you.

If you have bought your PyXLL license directly from PyXLL Ltd and not through Enthought please contact us at info@pyxll.com.

9
Hi,

a volatile function is called every time the worksheet is calculated, so setting volatile=True would give you the behaviour you describe and not what you want.

If an argument to a function is volatile then because that argument will be calculated every time the sheet is calculated then the function will be called too, even if the function itself if not volatile. If you have automatic calculation turned on in Excel then changing any cell will cause a recalculation.

Is one of the arguments you are passing to your function =TODAY() or something like that? TODAY is a volatile function, and so if you are using that as an argument then that will be why your function is getting called every time you calculate the sheet.

You can read more about volatile functions here:
https://msdn.microsoft.com/en-us/library/office/bb687891.aspx#sectionSection2

If you can't avoid passing in the result of a volatile function then I would suggest you cache the result of your function using something like functools.lru_cache to avoid repeating any expensive operations (https://docs.python.org/3/library/functools.html).

Best regards,
Tony

10
Help and Support / Re: Qt platform plugin "windows" error
« on: February 14, 2018, 09:57:53 AM »
Hi,

you've not mentioned the specific error you're seeing so I can't help with the exact problem you're having I'm afraid.

[edit]
Ah, I see the error is actually the subject. That error is staying that you don't have Qt installed. I expect matplotlib is failing to load the Tk backend and trying to fall back to the Qt backend and that's failing too -- the suggestions below to get Tk working should help.
[/edit]

If you're using the Tk matplotlib backend (which is the default) you may need to set the Tk/Tcl environment variables, which you can do by adding the following to your pyxll.cfg (with the paths updated to wherever your Python install is):

Code: [Select]
[ENVIRONMENT]
TCL_LIBRARY=C:\Python36\tcl\tcl8.6
TK_LIBRARY=C:\Python36\tcl\tcl8.6

Usually this is done when Python is installed, but depending on how Python was installed or if you are using a virtualenv it's possible they are set correctly when running your code from Spyder but not when running in Excel.

If you're still having problems after setting these environment variables please could you send me your complete log file with the log level set to 'debug',

Best regards,
Tony

11
Help and Support / Re: ModuleNotFoundError: No module named 'pyxll'
« on: February 06, 2018, 04:56:45 PM »
Hi,

install the whl file that comes as part of the pyxll download.

See the optional step 5 on this page: https://www.pyxll.com/docs/quickstart.html#installing-with-other-python-distributions

Best regards,
Tony

12
Help and Support / Re: RTD not reconnecting after reopening excel
« on: February 05, 2018, 09:12:28 AM »
Hi Gurrt,

after making this code change did you recalculate your workbook and re-save it? Try fully recalculating it by pressing Ctrl+Alt+F9 and then save the workbook. Excel remembers if a function is volatile or not, so if the saved version of your workbook doesn't have the function marked as being volatile it won't recalculate when opened.

The repeated connect/disconnect messages in the log for CurrentTimeRTD are a bug that was fixed in 3.3.2 that affects RTD functions with default arguments (https://www.pyxll.com/changelog.html#pyxll-3-3-2-2018-01-03). Since your function doesn't have any default arguments it's not affected by this bug.

Best regards,
Tony

13
Feedback and Suggestions / Re: Bug when using object_cache
« on: February 02, 2018, 05:36:03 PM »
hmm yeah could be... maybe they're both trying to write to the same gen_py, or producing incompatible versions of the wrappers or something.

Maybe it's worth uninstalling whichever one you're not using?

Glad you're up and running again.

cheers,
Tony

14
Feedback and Suggestions / Re: Bug when using object_cache
« on: February 02, 2018, 05:07:28 PM »
:(

If you've re-installed pywin32 and you still can't call the following from a normal Python prompt:

Code: [Select]
from win32com.client.gencache import EnsureDispatch
import sys
xl = EnsureDispatch("Excel.Application")

then something is still wrong with your pywin32 installation and it's not anything to do with PyXLL. I'm not sure why deleting those gen_py files wouldn't resolve it, unless something is going wrong when generating them.

I guess seeing as it's a COM issue it could be worth restarting your PC, otherwise I'm not sure what else to suggest that I haven't already I'm afraid.

Best regards,
Tony


15
Feedback and Suggestions / Re: Bug when using object_cache
« on: February 02, 2018, 04:57:18 PM »
Have you tried deleting the generated gen_py wrappers? I believe last time you found them in C:\Users\ranthero\AppData\Local\Temp\gen_py\3.6

Pages: [1] 2 3 ... 44