phillipsK phillipsK - 3 months ago 21
Python Question

xlwings Calling python from VBA

This is the error I receive after attempting to call a python script from within Excel VBA [http://docs.xlwings.org/quickstart.html]

Traceback (most recent call last):
File "<string>", line 1, in <module>
ImportError: No module named mymodule

Press Ctrl+C to copy this message to the clipboard.


Where do I save down the module.py file which contains:

import numpy as np
from xlwings import Workbook, Range
def rand_numbers():
""" produces standard 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


I have imported the
xlwings.bas
as a module into the Visual Basic editor, I have python 2.7.8 on anaconda 2.1.0

$ which python
/c/ana/python

$ pip show xlwings
---
Name: xlwings
Version: 0.2.2
Location: c:\ana\lib\site-packages
Requires:


I am assuming the xlwings.bas file is linked to my
pythonpath
but how does the sub procedure know how to call the module.py file and/or how does the sub procedure know where the module.py file is located?

Sub RandomNumbers()
RunPython ("import mymodule; mymodule.rand_numbers()")
End Sub

Answer

By default, the xlwings VBA module expects the Python file in the same directory as the Excel file. This is the PYTHONPATH setting that can be changed in the xlwings VBA module.