phillipsK phillipsK - 1 year ago 191
Python Question

xlwings Calling python from VBA

This is the error I receive after attempting to call a python script from within Excel VBA []

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 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
as a module into the Visual Basic editor, I have python 2.7.8 on anaconda 2.1.0

$ which python

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

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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download