VBA - Python - Console culture part 2 - working through install issues

After having set up my Python environment variables in the previous post, I here document working through some typical errors and how to resolve them.

Hello World Python COM Class

Below is a program which currently returns a hello world message but which in a later blog post will do much more hence I've given it a different name and COM class id.

class QuadEqFactorizer(object):
    _reg_clsid_ = "{CAAA6723-6A89-4BCD-A375-43BF4C06B01B}"
    _reg_progid_= 'SciPyInVBA.QuadEqFactorizer'
    _public_methods_ = ['Greeting']

    def Greeting(self):
        return ("Hello world")

if __name__=='__main__':
    print ("Registering COM server...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(QuadEqFactorizer)

Executing __main__ (first attempt)

Many language permit executables to have a canonical entry point called Main() (C++,C#,Java). Excel VBA has Sub AutoExec() or Sub Workbook_Open() to run code upon load, so they missed the opportunity to call it main. Nevertheless, Python calls it __main__ .

In the Python program above you can see the final block of code handling the __main__ entry point and it registers the script as a COM class which we can later invoke from Excel VBA. Once registered, a different mechanism (Python's COM plumbing) is used to instantiate the Python class and call method on it. But first let us get __main__ working. I saved the script as QuadEqFactorizer.py. In a command window, I switched to the script's home directory and then ran the script with the Python interpreter, python.exe

C:\WINDOWS\system32>cd C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer

C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer>dir
 Volume in drive C is OS
 Volume Serial Number is D6D5-B454

 Directory of C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer

02/06/2018  13:04    <DIR>          .
02/06/2018  13:04    <DIR>          ..
02/06/2018  13:04               402 QuadEqFactorizer.py
               1 File(s)            402 bytes
               2 Dir(s)  2,635,715,633,152 bytes free

C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer>python QuadEqFactorizer.py

But this threw an error on the penultimate line import win32com.server.register ModuleNotFoundError: No module named 'win32com' which was strange as a similar program worked a while back. Nevertheless, googling the issue it looked like I needed to install the package pypiwin32

Installing pypiwin32 with pip (first attempt)

So I next tried to install pypiwin32 with pip.

C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer>pip install pypiwin32
Collecting pypiwin32
  Downloading https://files.pythonhosted.org/packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
Collecting pywin32>=223 (from pypiwin32)
  Downloading https://files.pythonhosted.org/packages/9f/9d/f4b2170e8ff5d825cd4398856fee88f6c70c60bce0aa8411ed17c1e1b21f/pywin32-223-cp36-cp36m-win_amd64.whl (9.0MB)
    100% |████████████████████████████████| 9.0MB 145kB/s
Installing collected packages: pywin32, pypiwin32
Exception:
Traceback (most recent call last):
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\basecommand.py", line 215, in main
    status = self.run(options, args)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\commands\install.py", line 342, in run
    prefix=options.prefix_path,
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\req\req_set.py", line 784, in install
    **kwargs
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\req\req_install.py", line 851, in install
    self.move_wheel_files(self.source_dir, root=root, prefix=prefix)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\req\req_install.py", line 1064, in move_wheel_files
    isolated=self.isolated,
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\wheel.py", line 345, in move_wheel_files
    clobber(source, lib_dir, True)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\site-packages\pip\wheel.py", line 323, in clobber
    shutil.copyfile(srcfile, destfile)
  File "c:\program files (x86)\microsoft visual studio\shared\python36_64\lib\shutil.py", line 121, in copyfile
    with open(dst, 'wb') as fdst:
PermissionError: [Errno 13] Permission denied: 'c:\\program files (x86)\\microsoft visual studio\\shared\\python36_64\\Lib\\site-packages\\pythoncom.py'
You are using pip version 9.0.1, however version 10.0.1 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.

So I got a permission error but also told to consider upgrading pip

Upgrading pip

Opening a new command window with admin rights (to ease permission issues) and ran the following to upgrade pip

C:\WINDOWS\system32>python -m pip install --upgrade pip
Collecting pip
  Using cached https://files.pythonhosted.org/packages/0f/74/ecd13431bcc456ed390b44c8a6e917c1820365cbebcb6a8974d1cd045ab4/pip-10.0.1-py2.py3-none-any.whl
Installing collected packages: pip
  Found existing installation: pip 9.0.1
    Uninstalling pip-9.0.1:
      Successfully uninstalled pip-9.0.1
Successfully installed pip-10.0.1

So that worked well, so now I can try again with pypiwin32 install

Installing pypiwin32 with pip (second attempt)

C:\WINDOWS\system32>pip install pypiwin32
Collecting pypiwin32
  Using cached https://files.pythonhosted.org/packages/d0/1b/2f292bbd742e369a100c91faa0483172cd91a1a422a6692055ac920946c5/pypiwin32-223-py3-none-any.whl
Collecting pywin32>=223 (from pypiwin32)
  Using cached https://files.pythonhosted.org/packages/9f/9d/f4b2170e8ff5d825cd4398856fee88f6c70c60bce0aa8411ed17c1e1b21f/pywin32-223-cp36-cp36m-win_amd64.whl
Installing collected packages: pywin32, pypiwin32
Successfully installed pypiwin32-223 pywin32-223

So that worked this time, and I'm guessing was admin rights rather than pip upgrade. So now I can push on to re-attempt executing main in the python script.

Executing __main__ (second attempt)

C:\Users\Simon\source\repos\QuadEqFactorizer\QuadEqFactorizer>python QuadEqFactorizer.py
Registering COM server...
Registered: SciPyInVBA.QuadEqFactorizer

Great! that worked this time and we can see a new entry in the registry. Here is a before and after of the class registry.

Client Excel VBA code

And finally we can test with some client Excel VBA code

Option Explicit

Sub Test()

    Dim obj As Object
    Set obj = CreateObject("SciPyInVBA.QuadEqfactorizer")
    
    Debug.Print obj.greeting

End Sub

Running the above prints Hello world in the Immediate Window

Final Thoughts

Many VBA programmers are anchored in the land of Windows and Visual Basic with its windowed GUIs and are less comfortable working with command line tools. But Google is your friend and one can resolve these issues simply as shown in this post.