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 ... 45
16
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


17
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

18
Feedback and Suggestions / Re: Bug when using object_cache
« on: February 02, 2018, 04:44:55 PM »
Hi,

this looks similar to a problem you had previously with your pywin32 installation:
https://www.pyxll.com/_forum/index.php?topic=436.msg1564

I would recommend looking at your previous post again and try to resolve the problem in the same way as before.

Best regards,
Tony

19
General Discussion / Re: Custom formula description on cell
« on: February 02, 2018, 02:35:16 PM »
Hi,

yes, PyXLL has integration with another addin, ExcelDNA.IntelliSense.

If you install ExcelDNA.IntelliSense it will automatically detect your PyXLL functions and give you auto-completion as you enter the formula.

For more information please see https://excel-dna.net/2016/11/24/excel-udf-intellisense-for-excel-dna-and-vba/

Best regards,
Tony

20
Help and Support / Re: RTD not reconnecting after reopening excel
« on: February 01, 2018, 10:42:17 PM »
Hi Gurrt and Tony,

I was thinking about this some more, and I've come up with a way of doing what you want without having to resort to writing your own COM code - you can do it all just using PyXLL.

PyXLL automatically detects if an RTD function has already been called in a particular cell with the same arguments, and if it has it doesn't call the function again. This is so that once an RTD value has been set up it's not reset by re-calling the function, unless one of the arguments actually changes, even if something causes the cell to be recalculated (eg a volatile input).

To make your function automatically calculate when the sheet's opened, making the function volatile will do that - however, it will also disable the behaviour described above... So, the trick is to tell Excel to make the function volatile, while making PyXLL still treat it as a non-volatile function.

You can do this with the pyxll.xlfVolatile function. For example, taking the example rtd_current_time function and changing it as follows results in it automatically starting up when the workbook is opened (you have to open the sheet, calculate it and save it after making this change, but from then on whenever it's opened it will start ticking straight away).

Code: [Select]
from pyxll import xl_func, xlfVolatile

@xl_func("var format: rtd", macro=True)
def rtd_current_time(format="%Y-%m-%d %H:%M:%S"):
    """Return the current time as 'real time data' that
    updates automatically.

    :param format: datetime format string
    """
    xlfVolatile(True)
    return CurrentTimeRTD(format)

Note that xlfVolatile should be the first thing called in the function, and the function must also have macro=True added to the xl_func decorator for it to work.

This seems like the best solution to your problem to me. Please let me know how you get on; if it works well for you I will think about how to make it more of a standard feature so you don't need this workaround in a future version of PyXLL.

Best regards,
Tony

21
Help and Support / Re: RTD not reconnecting after reopening excel
« on: February 01, 2018, 05:27:18 PM »
Hi,

there are a couple of possible solutions to make Excel do what you want.

You can use a COM server as your RTD data source. Because the RTD function references the prog id of the RTD server there is then no problem with it resuming when Excel restarts. There's an example of how to do that in the pywin32 package, and some extra code required for it to work with versions after Excel 2007 can be found here: https://github.com/pyxll/exceltypes. This is what you (Tony) will most likely have done in .net and what the bloomberg addin does.

Alternatively, you can use COM or VBA to recalculate the sheet (or specific cells) when the workbook is opened. For an example of handling Excel COM events in Python you can take a look at the object cache example included with PyXLL.

PyXLL RTD functions do not require any COM registration (unlike the first suggestion above) and instead rely on the function being called to create the RTD object. It's possible that the PyXLL RTD object could somehow be modified so that it could be resurrected after restarting Excel, but for now by far the simplest solution is to recalculate the sheet.

If you want to go the COM server route this post from 2010 may help: https://www.pyxll.com/_forum/index.php?topic=27.0

Best regards,
Tony

22
Help and Support / Re: RTD not reconnecting after reopening excel
« on: February 01, 2018, 08:41:37 AM »
Hi Gurrt,

that's the expected behaviour. The RTD object only gets created when your function is called, and that doesn't happen until the worksheet is recalculated after starting Excel. You can recalculate everything by pressing Ctrl+Alt+F9 after opening your workbook, and that will start any RTD functions.

Best regards,
Tony

23
Hi,

the address of the xl_cell will be for a range of cells. The NumberFormat property on an Excel Range covering multiple cells doesn't return an array of number formats for each cell, so you will have to get them cell by cell yourself.

You can find the docs for the Excel COM API on MSDN. Here's the page for the Range class:
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel

The code you're after is something like this:

Code: [Select]
@xl_func("xl_cell cell: string[]", macro=True, auto_resize=True)
def cell_format(cell):
    xl = xl_app()
    xl_range = xl.Range(cell.address)
    result = []
    for i in range(xl_range.Rows.Count):
        row = []
        for j in range(xl_range.Columns.Count):
            cell = xl_range.Cells(i+1, j+1) # Note Excel indexes from 1, not 0
            row.append("%s (%s)" % (cell.Value, cell.NumberFormat))
        result.append(row)
    return result

Best regards,
Tony

24
Help and Support / Re: xl_func help_topic path
« on: January 28, 2018, 04:32:39 PM »
Hi Algirdas,

you have to specify a numeric help context id in addition to the filename in the form "filepath!HelpContextID" (e.g. "C:/folder/help.chm!0"). If you only want to open the chm file, you still have to provide a help context id but it can be 0. The file path should be an absolute path and must be less that 255 characters long, so you may have to use a windows short path (this is a limitation of Excel).

To add context ids to your chm file you have to add aliases to your hpp file used to make the chm file, e.g.:

Code: [Select]
---- MYHELP.HHP ----
[FILES]
index.html
myfunc1.html
myfunc2.html
myfunc3.html

[ALIAS]
IDH_topic_1 = myfunc1.html

[MAP]
#define IDH_topic_1 1001
-------------------------

I'm sorry these details were missing from the documentation. I will update them with this information. If you have any more problems or questions please let me know.

Best regards,
Tony

25
Hi,

it's to be expected that the value is the same, regardless of the formatting. It would be pretty hard to write reliable code if that wasn't the case :)

You can use the Excel API to get a cell's number format, and you can use the "xl_cell" PyXLL argument type to get the address of the cell being passed to a function.

For example:

Code: [Select]
from pyxll import xl_func, xl_app

@xl_func("xl_cell cell: string", macro=True)
def cell_format(cell):
    xl = xl_app()
    format = xl.Range(cell.address).NumberFormat
    value = cell.value
    return "%s (%s)" % (value, format)

Note that this requires the pywin32 package to be installed to use the Excel COM API, and for the function to be registered as a macro-sheet equivalent function (set macro=True in the xl_func decorator as in the example above).

Best regards,
Tony

ps. docs for the xl_cell type are here if you need them: https://www.pyxll.com/docs/api/classes.html#xlcell

26
General Discussion / Re: Custom Ribbon customization
« on: January 19, 2018, 07:09:20 PM »
Hi again,

I've figured out how to do it, and it's actually very simple!

You can reference any of the standard controls by an id (idMso). To add the cell styles control you mentioned you just need the following:

Code: [Select]
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
                 loadImage="pyxll.load_image">
    <ribbon>
        <tabs>
            <tab id="pyxll_example_tab" label="PyXLL Example Tab">
                <group id="Tools" label="Tools">
                    <control idMso="CellStylesGallery"/>
...

There's a list of the available idMsos here: https://msdn.microsoft.com/en-us/library/dd909393(v=office.12).aspx

Best regards,
Tony

27
General Discussion / Re: Custom Ribbon customization
« on: January 19, 2018, 06:57:26 PM »
Oh I see. Hmm, I don't know if that's possible I'm afraid...

If you know how to do it using VBA you should be able to do the same using COM (win32com), and maybe doing that after your ribbon has been initialised would work. You can use the "onLoad" callback in your ribbon xml (https://msdn.microsoft.com/en-us/library/office/hh300584), although you might need to let excel finish building the ribbon before you can change it (in which case using pyxll.async_call from the onLoad callback might work).

Otherwise you would have to duplicate the functionality you require in Python to add it to your ribbon I think.

Best regards,
Tony

28
General Discussion / Re: Custom Ribbon customization
« on: January 19, 2018, 03:32:55 PM »
Hi,

yes, you can create your own tabs in the Excel ribbon with PyXLL.

Please see this section of the docs for details:
https://www.pyxll.com/docs/userguide/ribbon.html

Best regards,
Tony

29
Help and Support / Re: Exceptions freeze excel
« on: January 19, 2018, 10:45:29 AM »
Hi Yufei,

sorry, I've not seen this problem before :( I tried to reproduce it with the following code (I tried with and without thread_safe=True in case there was some issue there):

Code: [Select]
import threading

@xl_func(thread_safe=True)
def exception_test(x):
    thread_id = threading.get_ident()
    raise RuntimeError("%s %s" % (thread_id, x))

and created a spreadsheet with several thousand of these calls, using get_last_error to check they were running across multiple threads, and I couldn't find any problems.

What's the last thing you see in the log file when Excel hangs? Is it possible a particular exception is actually causing a dead-lock somewhere in your code? Can you narrow it down to a particular function that is causing you this problem? If you could put together a minimal example that demonstrates the problem I can take a look.

Best regards,
Tony

30
Feedback and Suggestions / Re: xl_app not working
« on: January 17, 2018, 09:02:30 PM »
Great! I'm glad you were able to get it working again :)

Pages: 1 [2] 3 4 ... 45