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.

ActiveDirectory with VBA Part 1 - Installing AD LDS

So in various roles I've written code in production to query a corporate active directory to query for users and check group membership to drive permission logic. In those times, I always had a corporate instance of Windows Server to program against. What happens if you don't have access to a Windows Server using a ordinary desktop edition of Windows? I am using Windows 8.1 Professional Edition.

It might be tempting to download ApacheDS which is free but that cannot be accesed using VBA's GetObject("LDAP://CN=BillGates,DC=microsoft,DC=com").

You will need to use something like a development edition of Active Directory. Fortunately, a lightweight edition does exists, here is an overview of Active Directory Lightweight Directory Services (AD LDS) and here is a quote highlighting the development use case...

Providing a development environment for AD DS and AD LDS

Because AD LDS uses the same programming model and provides virtually the same administration experience as AD DS, it can be a good fit for developers who are staging and testing various Active Directory-integrated applications. For example, if an application under development requires a different schema from the current server operating system AD DS, the application developer can use AD LDS to provide the application with a tailored schema that works for business needs, data requirements, and workflow processes, without altering the configuration of the corporate Active Directory deployment. Developers can work with an AD LDS instance without the need for a complicated setup and later move the application to AD DS. Developers may want a directory that they can easily program to without requirements for extensive setup or hardware support during the development process. This can be achieved through AD LDS as it can easily be installed and uninstalled on any Windows Server 2008 computer. This allows rapid restoration to a clean state during the application prototyping and development process.

Luckily Active Directory Lightweight Directory Services (AD LDS) is an optional component if you are using Windows 8.1 (and hopefully other desktop editions of Windows), from the Metro interface search box type "Windows features" and then select "Turn Windows features on or off" which should be top hit. Then you should see the following dialog box where you can check "Active Directory Lightweight Directory Services"

Once installed, I followed a Technet tutorial about creating an AD LDS Instance and in Part 2. I'll show you my screenshots as I went through the steps.