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 ... 41
16
Bugs and Suggestions / Re: Freeze excel on loading
« on: September 01, 2017, 08:34:20 AM »
Hi,

I've not come across that issue before, and so I don't know whether it would only affect the built in evaluation license or not... I'll email you a trial license key so you can see.

However, I would like to understand more about what's happening in case it's affecting other users too. Could you set your log level to debug in your pyxll.cfg file and send me the complete log please? See https://www.pyxll.com/docs/userguide/config.html#logging for details of how to change the logging settings.

Best regards,
Tony

17
General Discussion / Re: Setting the format of a cell from pyxll
« on: August 22, 2017, 10:14:38 AM »
Hi,

yes, you can use the Excel API to change cell styles. Get the Excel Application object using pyxll.xl_app, and then you can get the range you want and change its style, eg

Code: [Select]
xl = xl_app()
r = xl.Range("A1")
r.Font.Bold = True
r.NumberFormat = "General"

You can find more details about the Excel Range object here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel

Note that to call back into Excel like this you will have to do it from a macro, macro equivalent function or menu function. See the automation_example (https://www.pyxll.com/docs/examples/automation.html) to see how to call back into Excel from a worksheet function.

Best regards,
Tony

18
Help and Support / Re: Multiprocesssing.Pool freezes Excel
« on: August 20, 2017, 04:48:22 PM »
Hi David,

try setting "executable" in the PYTHON section of the pyxll.cfg to the full path to pythonw.exe (e.g. C:\PythonXX\pythonw.exe). pythonw.exe is the same as python.exe, except it doesn't open a console window.

Best regards,
Tony


19
Help and Support / Re: Debugging with PyCharm
« on: August 13, 2017, 05:41:49 PM »
Hi David,

pydevd.settrace should be all you need to call. Is there anything in the pyxll log file?

Best regards,
Tony

20
Help and Support / Re: Simple question about writing to a cell
« on: August 09, 2017, 10:34:21 PM »
Hi,

any exceptions thrown will be written to the log file. Anything you print will also get written to the log file, so you could add some print statements to help figure out what's going wrong.

You can configure the logging in the config file (https://www.pyxll.com/docs/userguide/config.html#logging).

Best regards,
Tony

21
Help and Support / Re: Simple question about writing to a cell
« on: August 09, 2017, 05:13:12 PM »
Also, to answer your question about arrays - worksheet functions can also return arrays of data, see:

https://www.pyxll.com/docs/userguide/udfs.html#using-arrays
https://www.pyxll.com/docs/userguide/udfs.html#resizing-array-formulas

It is also possible to set the value of a range of cells using the COM API from a macro function, e.g.:

Code: [Select]
@xl_macro
def test_macro():
    xl = xl_app()
    range = xl.Range("A1:B2")
    range.Value = [[1,2], [3,4]]

22
Help and Support / Re: Simple question about writing to a cell
« on: August 09, 2017, 05:08:31 PM »
Hi,

you can write a function that you can use as an Excel formula, and simply call it from cell A1 and its return value will become the cell's value. If you've not read the quick start section of the docs already, you should have a look at https://www.pyxll.com/docs/quickstart.html#calling-a-python-function-in-excel.

If you want to write a macro to modify the worksheet, you need to use the Excel COM API - which is basically the same API exposed to VBA, so if you've done any VBA programming it will be familiar.

You can get the Excel Application object using the pyxll.xl_app function. Once you have the Application object you can access everything else in Excel, like the current sheet, selection or a range of cells. See https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-object-excel for more details (although not Python specific, the Excel object model or API is the same in Python as it is in other languages).

For example,

Code: [Select]
from pyxll import xl_macro, xl_app

@xl_macro
def test_macro():
    xl = xl_app()
    range = xl.Range("A1")
    range.Value = 1

The XLCell class you're referring to is used in Excel functions (using xl_func) to pass a cell reference into a Python function - not to modify a cell from a macro.

In the zip file you got when downloading PyXLL you'll see there are some example modules and an example spreadsheet showing how to use many of PyXLL's features. If you have a look at automation.py that should help.

Best regards,
Tony

ps. There is another package, xlwings, that provides a higher level API to the COM API (it wraps it to make it simpler). While the COM API is exactly the same as the VBA API, and so you can find plenty of non-Python specific documentation (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/object-model-excel-vba-reference) you might want to look at xlwings too. The pyxll.xl_app function can return an xlwings Application wrapper object, instead of the plain Application object, if you prefer to use that (https://www.pyxll.com/docs/api/utils.html?highlight=xl_app#pyxll.xl_app).

23
Help and Support / Re: Runtime Error R6034 C++
« on: August 07, 2017, 09:40:31 AM »
Hi,

I understand this problem was caused by an incorrect version of Python 3 being pushed out to your PC, and has now been resolved?

It is possible to use PyXLL with multiple versions of Python installed at once, it just needs a change to your config to set where to look for Python: https://www.pyxll.com/docs/userguide/config.html#python-settings

Please let me know if you are still having problems.

Best regards,
Tony

24
Help and Support / Re: How to reload without recalculate?
« on: August 07, 2017, 09:37:12 AM »
Hi,

unfortunately when an Excel function is re-registered Excel marks the old value as invalid - there's nothing you can do from PyXLL to change that.

However, if you switch to manual calculation mode those cells won't automatically recalculate - only when you next recalculate by pressing F9.

Another thing you could perhaps do is introduce a flag in your code to disable all your database functions. You could then set that before reloading and unset it after. You could have a pyxll menu function or macro that sets it and calls pyxll.reload, and unset it in the on_reload callback (https://www.pyxll.com/docs/api/eventhandlers.html#xl-on-reload). Note that pyxll.reload doesn't reload synchronously so unsetting the flag immediately after calling pyxll.reload wouldn't work.

Best regards,
Tony

25
Help and Support / Re: Error getting Excel Window instance
« on: July 31, 2017, 07:59:53 AM »
Hi,

hmm ok, well I'm glad it's working now - but I'll still do some digging and see if it's something that might be improved...

Can you tell me the version of Excel and Window you're using please? And are you using 32 bit or 64 bit Excel?

thanks,
Tony

26
Help and Support / Re: Error getting Excel Window instance
« on: July 30, 2017, 06:34:30 PM »
Hi,

do you have at least one workbook open? If you've closed all your workbooks then that can cause that error - it's a quirk of how the Excel COM API works (which the xl_app function uses) that requires a workbook to be open.

If it's not that, have you tried closing Excel and killing any remaining EXCEL.EXE process in the task manager and restarting Excel? Perhaps the Excel COM control has got into a weird state...

Best regards,
Tony

27
Help and Support / Re: Runtime Error R6034 C++
« on: July 19, 2017, 04:21:08 PM »
Hi,

this used to be a problem with some Python extensions that embedded a manifest. If any of the Python extensions you are using have a manifest embedded then you will get this error.

To fix it you will either need to install a newer build of Python that doesn't have any extensions with this problem, or identify the modules that have an embedded manifest and remove the manifest from them (with something like CFFExplorer - http://www.ntcore.com/exsuite.php).

The Python bug is documented here: https://bugs.python.org/issue4120 (it affected Python versions prior to 3.1, despite the version on the bug report).

Best regards,
Tony

28
Help and Support / Re: Assign Shortcut to PageUp Key
« on: July 04, 2017, 09:21:26 AM »
Quick update - this will be included in the next release of PyXLL.

Best regards,
Tony

29
Hi Steve,

yes, that's exactly the problem. You can't mix 64 and 32 bit versions of Python and Excel as the Excel process can't load dlls compiled for a different bitness.

You will need to either install the 32 bit version of Canopy/Python and PyXLL, or the 64 bit version of Excel.

Best regards,
Tony

30
Help and Support / Re: Error while loading script with pandas
« on: June 15, 2017, 03:35:09 PM »
Hi,

thanks, I got that.

It seems that the problem occurs with the latest version of the 'py' package, 1.4.34.

If you download to a previous version (I was using 1.4.31) then it should work.

I'll look into why this latest version isn't working, but in the meantime please try downgrading that package.

Best regards,
Tony

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