How to import pandas into the Python COM object for VBA

Question

I have to pass data from VBA to Python and back.

I also need the Python Obj in VBA to use Pandas library (and other libraries such as numpy, ...).

• I have successfully used the SOLUTION 2 provided by Florent B.: Moving numpy arrays from VBA to Python and back
• If I don't try to import pandas at all, the Object is created successfully and works normally (but no pandas is used). Any VBA-Array passed to the Python Object is recognized as tuple of tuples
• I need to convert the data input (tuple of tuples) to pandas DataFrames. So in MyLibrary.py file I have then added "pandas" to the import list (on the top of the file - see Python code below)
• I have then successfully registered the module (with pandas) in Python shell

In VBA, when the Object is created...

Set Py = CreateObject("Python.MyModule")

...I get an error message related to:

return retObj.CreateInstance(clsid, reqID)

ALTERNATIVELY...

• If I import pandas from within MyFunction (without declaring at the top of the file), the Python object is created successfully (this is how pandas is imported from MyFunction in Python):

  def MyFunction(self, data) :
  import pandas
  

• but when I call MyFunction from VBA (which is when pandas is imported)...

  ArrayOutput = Py.MyFunction(ArrayInput)
  

  ...I receive an error message:

  return self._invoke(dispid, lcid, wFlags, args)

The code is below (I report the version where pandas is imported at the top of the file):

VBA code:

Sub UsePythonObj(ArrayInput)

''''''''''''''''''''''''''''''
' Create Python Object
Dim ArrayOutput As Variant
Dim Py As Object
Set Py = CreateObject("Python.MyModule")


''''''''''''''''''''''''''''''''''''''
' Run Python 
ArrayOutput = Py.MyFunction(ArrayInput)

End Sub

Python code:

import sys, os, win32api, win32com.server.localserver, win32com.server.register, pandas

    class MyModule(object):


      _reg_clsid_ = "{5B4A4174-EE23-4B70-99F9-E57958CFE3DF}"
      _reg_desc_ = "My Python COM Server"
      _reg_progid_ = "Python.MyModule"
      _public_methods_ = ['MyFunction']


      def MyFunction(self, data) :
          ProcessingData = pandas.DataFrame(list(data))
          return ProcessingData


    def register(*classes) :
      regsz = lambda key, val: win32api.RegSetValue(-2147483647, key, 1, val)
      isPy = not sys.argv[0].lower().endswith('.exe')
      python_path = isPy and win32com.server.register._find_localserver_exe(1)
      server_path = isPy and win32com.server.register._find_localserver_module()

      for cls in classes :
        if isPy :
          file_path = sys.modules[cls.__module__].__file__
          class_name = '%s.%s' % (os.path.splitext(os.path.basename(file_path))[0], cls.__name__)
          command = '"%s" "%s" %s' % (python_path, server_path, cls._reg_clsid_)
        else :
          file_path = sys.argv[0]
          class_name = '%s.%s' % (cls.__module__, cls.__name__)
          command = '"%s" %s' % (file_path, cls._reg_clsid_)

        regsz("SOFTWARE\\Classes\\" + cls._reg_progid_ + '\\CLSID', cls._reg_clsid_)
        regsz("SOFTWARE\\Classes\\AppID\\" + cls._reg_clsid_, cls._reg_progid_)
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_, cls._reg_desc_)
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\LocalServer32', command)
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\ProgID', cls._reg_progid_)
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOM', class_name)
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOMPath', os.path.dirname(file_path))
        regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\Debugging', "0")

        print('Registered ' + cls._reg_progid_)


    if __name__ == "__main__":
      if len(sys.argv) > 1 :
        win32com.server.localserver.serve(set([v for v in sys.argv if v[0] == '{']))
      else :
        register(MyModule)