EzDevInfo.com

xlWings issue in Citrix Xen Environment

I have setup xlWings to use Python in Microsoft Excel in my Citrix server. I have tested by running the excel and wrote a simple Python script and it works fine; however, after I have published the excel sheet and opened the excel spreadsheet through remote Citrix website. Ran same Python script, but gave me an error, "#ERR: Could not Create Python process." Can anyone provide solution for this?

Many thanks in advance,

Bill

Does xlwings only work with the first instance of Excel?

Is this a known limitation that will be addressed at some point, or is this just something that I need to accept?

If this is not possible with xlwings, I wonder if any of the other alternatives out there supports connecting to other instances.

I'm specifically talking about the scenario where you are calling python from within Excel, so the hope is that the getCaller() function will be able to figure out which instance of the Excel is actually calling it.

Does xlwings support "Wrap Text" property?

Can I control the "Wrap Text" property of a cell using Python+xlwings?

python xlwings - copy and paste ranges

My first question on this site.

I am using xlwings with python 2.7. I want to copy the values in range 'A1:A6' from Sheet1 to cells 'A1:A6' in Sheet2. My code is as follows:

> my_range = Range('Sheet1','A1:A6').value
> 
> Range('Sheet2','A1:A6').value = my_range

When I run this code, however, it puts the values of my_range into cells 'A1:F1' on Sheet 2 instead of cells 'A1:A6' in Sheet 2.

importing xlwings module into python 3.4

I'm trying to use the new excel integration module xlwings It works like a charm under Anaconda 2.0 for python 2.7 but I'm getting this error under Anaconda 2.0 for python 3.4 the xlwings file does contain class Workbook so I don't understand why it can't import it when I simply use the xlwings file in my project for 3.4 it works just fine

File "C:\Users\xxxxx\AppData\Local\Continuum\Anaconda3\lib\site-packages\xlwings__init__.py", line 1, in from xlwings import Workbook, Range, Chart, version

ImportError: cannot import name 'Workbook'

Is there a way to call python with xlwings without reopening the Excel file?

I am calling python from Excel using xlwings. I find that when running my macro, Excel closes and reopens in order to run the code. It functions correctly but it slows things down. In addition, if the Excel file is unsaved a dialog will mention that the file is already open and that I will lose unsaved changes.

Is there a way to call python without reopening the Excel file?

This is my python code (in loaddf.py):

from xlwings import Workbook, Range, Sheet

def my_macro():
    wb = Workbook.caller()
    Range('A1').value = Range('A1').value + 1

And the VBA code in my Excel file:

Sub loaddfsub()
    RunPython ("import loaddf; loaddf.my_macro()")
End Sub

Thanks for the help.

Refresh Pivot Table with XLWINGS

I am trying to refresh a pivot table in excel upon data written by XLWINGS. As I don't know how to do it directly from XLWINGS, I tried to use VBA.

Let's split my process in 2 steps:

Step1

I launch the python code from vba (my module name is "PosRep", the python code writtes back a range of data in a specified sheet thanks to xlwings.

Sub launchPython()
RunPython ("import PosRep; PosRep")
End Sub

Step 2

But as I don't know in advance the size of my newly created Range in Excel, I want to select it, add a new Name (NamedRange) and refresh my pivot (already linked to the NamedRange).

Sub SelectRange()
Worksheets("GPODump").Range("A1").Select
'...
End Sub

Both Subs work independently well. But I cannot manage to make them work in a raw. The following code:

Sub Main()
launchPython
SelectRange
End Sub

produces a VBA error "Select method of Range class failed" on the statement:

Worksheets("GPODump").Range("A1").Select

I presume there is a conflict with the XLWINGS VBA module but I can't figure out what it can be... Anyone's help would be more than welcome ! Thx

Why does a checkbox gets updated only after the code is executed in xlwings?

I have a xslm file with a macro assigned to a button and a checkbox. Pushing the button the macro which is a python script runs and inserts the data into a sheet. The problem is that in the macro I disable the checkbox, but it doesn't affect the checkbox not until the macro has finished.

Below it is an example macro:

def insert_data():
    wb = Workbook.caller()

    wb.xl_workbook.ActiveSheet.OLEObjects("Checkbox1").Object.Enabled = False

    data = pd.read_csv("data.csv")
    Range("A1").value = data

Count rows in excel sheet in Python with xlwings

I have a script in Python that uses xlwings to open up an Excel file, and read and process the values of a certain column row by row. Here is the for statement:

for row in range(2, rownum):

I would like to repeat this function over every row in the sheet that actually contains something. It starts at 2 and ends at 'rownum'. My question is how to automatically count the number of rows and pass that value to 'rownum'. I'm sure xlwings has a way to do this, but I can't figure it out- perhaps the Autofit tool?

Thanks for the help!

xlwings module.py call python from excel

I keep receiving this error while trying to call the module.py file from excel

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "F:\ana\module.py", line 6, in rand_numbers
    wb = Workbook.caller()  # Creates a reference to the calling Excel file
AttributeError: type object 'Workbook' has no attribute 'caller'

When I replace wb = Workbook.caller() with wb = Workbook() I receive this error

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "F:\ana\module.py", line 11, in rand_numbers
    rand_num = np.random.randn(n, n)
  File "mtrand.pyx", line 1341, in mtrand.RandomState.randn (numpy\random\mtrand\mtrand.c:11537)
  File "mtrand.pyx", line 1454, in mtrand.RandomState.standard_normal (numpy\random\mtrand\mtrand.c:11839)
  File "mtrand.pyx", line 142, in mtrand.cont0_array (numpy\random\mtrand\mtrand.c:1867)
TypeError: an integer is required

Alternatively [scenario 2], I am able to call a python file from excel while using this sample code

from xlwings import Workbook, Sheet, Range, Chart
wb = Workbook()  # Creates a connection with a new workbook
#wb = Workbook.caller()
Range('A1').value = 'Foo 1'
Range('A2').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
Range('A13').table.value  # or: Range('A1:C2').value
Sheet(1).name
chart = Chart.add(source_data=Range('A2').table)

However the call in excel only works with wb = Workbook() and not wb = Workbook.caller()

I am aware of this API documentation update

module.py

import numpy as np
from xlwings import Workbook, Range

def rand_numbers():
    """ produces std. normally distributed random numbers with shape (n,n)"""
    wb = Workbook.caller()  # Creates a reference to the calling Excel file
    n = Range('Sheet1', 'B1').value  # Write desired dimensions into Cell B1
    rand_num = np.random.randn(n, n)
    Range('Sheet1', 'C3').value = rand_num

VBA code

Sub MyMacro()
    RunPython ("import module; module.rand_numbers()")
End Sub

testing.py (test example code - scenario 2)

from xlwings import Workbook, Sheet, Range, Chart
wb = Workbook()  # Creates a connection with a new workbook
#wb = Workbook.caller()
Range('A1').value = 'Foo 1'
Range('A2').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
Range('A13').table.value  # or: Range('A1:C2').value
Sheet(1).name
chart = Chart.add(source_data=Range('A2').table)

VBA code

Sub MyMacro()
    RunPython ("import testing") 
End Sub

How to solve import error for pandas using iPython Notebook on Windows?

I'm trying to import pandas while using iPython. My overall purpose is to use XLwings.

I'm on Windows 7 and have used Anaconda to install Python, pandas and all the dependencies.

Here is my code:

from pandas import DataFrame

Which gives:

ImportError                               Traceback (most recent call last)
<ipython-input-7-26dfcabfb474> in <module>()
----> 1 from pandas import DataFrame

C:\Users\Accounting\Anaconda\lib\site-packages\pandas\__init__.py in <module>()
     11                       "pandas from the source directory, you may need to run "
     12                       "'python setup.py build_ext --inplace' to build the C "
---> 13                       "extensions first.".format(module))
     14 
     15 from datetime import datetime

ImportError: C extension: hashtable not built. If you want to import pandas from the source directory, you may need to run 'python setup.py build_ext --inplace' to build the C extensions first.

Can anyone with experience of using Python/pandas in iPython on Windows help me understand how to solve this error?

Specifically where do I run "python setup.py build_ext --inplace"?

Thanks!

How do I save a workbook using xlwings?

I have an excel worksheet, some buttons and some macros. I use xlwings to make it work. Is there a way to save the workbook through xlwings ? I want to extract a specific sheet after doing an operation, but the saved sheet is the extracted sheet before the operation without the generated data.

My code for extracting the sheet I need is the following:

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
sheet_name = Wscript.Arguments.Item(1)
dir_name = Wscript.Arguments.Item(2)
file_name = Wscript.Arguments.Item(3)

Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False

Dim objWorkbook
Set objWorkbook = objExcel.Workbooks(src_file)

objWorkbook.Sheets(sheet_name).Copy
objExcel.DisplayAlerts = False

objExcel.ActiveWorkbook.SaveAs dir_name + file_name + ".xlsx", 51
objExcel.ActiveWorkbook.SaveAs dir_name + file_name + ".csv", 6

objWorkbook.Close False
objExcel.Quit

How can I insert a dataframe in Excel using xlwings without pywintypes.com_error?

I am using Excel and xlwings. I have a book.xlsm, on the first sheet there is a button assigned to the following vba code:

book.xlsm!ThisWorkbook.get_data

On the VBA I have added this which when the button is called and the vba code executed, it runs:

Sub get_data()
    RunPython ("import my_script; my_script.get_data()")
End Sub

The my_script is the following:

import pandas as pd
from xlwings import Workbook, Range

def get_data():
    wb = Workbook.caller()

    df = pd.read_csv("data.csv")
    Range("Sheet2", "A1").value = df

The problem I encounter is the following:

pywintypes.com_error: (-2147024882, 'Not enough storage is available to complete this operation.', None, None)

The data.csv file has 150000 rows and 120 rows. Using less data it runs without error.

Update: Currently there is not a solution, but there is a workaround as provided in the comments: https://github.com/ZoomerAnalytics/xlwings/issues/77

I use the following:

df = pd.read_csv(csv_file, na_values={"", " ", "-"})
df.fillna("-", inplace=True)
startcell = 'A1'
chunk_size = 2500
if len(df) <= (chunk_size + 1):
    Range(sheet_name, startcell, index=False).value = df
else:  # chunk df and and dump each (default is 10k)\n",
    c = re.match(r"([a-z]+)([0-9]+)", startcell, re.I)
    cL = c.group(1)
    cN = int(c.group(2))
    n = 0
    for i in (df[rw:rw + chunk_size] for rw in xrange(0, len(df), chunk_size)):
        if n == 0:
            Range(sheet_name, cL + str(cN+n), index=False).value = i
            cN += chunk_size
        else:
            Range(sheet_name, cL + str(cN+n)).value = i.values
            cN += chunk_size
        n += 1

The problem I encounter is that when I insert the data in the sheet there is an empty line at 5002, again at 7503, 10004.... I realize there's a mistake in my code but I can't find it.

xlwings Range Error on Yosemite 10.10.3

I just installed xlwings and was trying one of the examples and I am getting a Range error. Other commands seem to work fine. This is on Yosemite 10.10.3

Any Ideas what might be wrong?

Python 2.7.6 (default, Sep  9 2014, 15:04:36) 
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.39)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from xlwings import Workbook, Sheet, Range, Chart
>>> wb = Workbook()
>>> Sheet(1).name
u'Sheet1'
>>> Range('A1').value = 'Foo 1'
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Python/2.7/site-packages/xlwings/main.py", line 622, in __init__
    self.row2 = self.row1 + xlplatform.count_rows(self.xl_sheet, range_address) - 1
  File "/Library/Python/2.7/site-packages/xlwings/_xlmac.py", line 145, in count_rows
    return xl_sheet.cells[range_address].count(each=kw.row)
  File "/Library/Python/2.7/site-packages/aeosa/appscript/reference.py", line 431, in __call__
    raise TypeError('Unknown keyword argument %r.' % name)
TypeError: Unknown keyword argument 'each'.
>>> 

wb = xlwings.Workbook() fails on mac

I'm just tinkering with xlwings on a mac to write values to cells. However, when I initialize a new workbook, I get this:

import xlwings as xl
wb = xl.Workbook()


Error in atexit._run_exitfuncs:
Traceback (most recent call last):
  File "/Developer/anaconda/lib/python2.7/atexit.py", line 24, in _run_exitfuncs
    func(*targs, **kargs)
  File "/Developer/anaconda/lib/python2.7/site-packages/xlwings/_xlmac.py", line 30, in clean_up
    app('Microsoft Excel').run_VB_macro('CleanUp')
  File "/Developer/anaconda/lib/python2.7/site-packages/aeosa/appscript/reference.py", line 579, in __getattr__
    raise AttributeError("Unknown property, element or command: %r" % name)
AttributeError: Unknown property, element or command: 'run_VB_macro'
Error in sys.exitfunc:
Traceback (most recent call last):
  File "/Developer/anaconda/lib/python2.7/atexit.py", line 24, in _run_exitfuncs
    func(*targs, **kargs)
  File "/Developer/anaconda/lib/python2.7/site-packages/xlwings/_xlmac.py", line 30, in clean_up
    app('Microsoft Excel').run_VB_macro('CleanUp')
  File "/Developer/anaconda/lib/python2.7/site-packages/aeosa/appscript/reference.py", line 579, in __getattr__
    raise AttributeError("Unknown property, element or command: %r" % name)
AttributeError: Unknown property, element or command: 'run_VB_macro' 

Excel does open and creates a new file, but then the exception happens. I don't understand why it would be trying to run macros when running on a mac. I know the author is here. Hopefully, he can weigh in.