Saturday, 24 July 2021

JavaScript Notes and Queries: JavaScript's Prototypical Inheritance is simpler than TypeScript implies

In other posts on this blog I can write with authority rooted in deep experience. Lately, I have been revisiting JavaScript and cannot write on this topic with such confidence but I am keen to master web development. So I begin a new series called JavaScript Notes and Queries and the first topic is prototypical inheritance...

TypeScript, harbinger of change

I cannot write about all the previous versions of JavaScript nor give key milestones, I can only write as to my previous encounters. Decades ago, I remember JavaScript being the language used in web pages to handle mouse clicks etc. and little more. At my clients (Banks etc.) nobody was writing large scale projects in JavaScript. But Microsoft were and they found it wanting, so Microsoft invented TypeScript. Here's John Papa, Principal Developer Advocate with Microsoft ...

TypeScript is a language for application-scale JavaScript development. It’s a typed superset of JavaScript that compiles to plain JavaScript and was originally created out of a need for a more robust tooling experience to complement JavaScript language developers.

So, Microsoft concluded that ordinary JavaScript couldn't cope with large scale projects. The TypeScript tooling, specifically the online transpilers, that arose could be used to compare and contrast syntax patterns between TypeScript and JavaScript, of most interest to me was classes and inheritance.

TypeScript transpilers reveal JavaScript prototypical equivalence of TypeScript classes

Typescript 'transpiles' to plain JavaScript, and you can find tools on line to demonstrate such as TypeScript playground. I find this to be the best tool because you can change the target version of JavaScript. When I first encountered these JavaScript was still at ECMAScript 5, i.e. before the class keyword had arrived. We can play a game of time travel and restrict the target language to ECMAScript 5 by using the options menu. Then, we can write a TyepScript class and see the old school JavaScript equivalent.

Below is a simple class in Typescript (on the left) and its transpiled into old school JavaScript, specifically ECMAScript 5 (because ECMAScript 6 has its own class keyword) equivalent (on the right). Here is the link which you must paste in full because the source code is encoded in the url.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}
"use strict";
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());

The JavaScript on the right is cryptic compared to the Typescript on the left but if you play around (add some more methods etc.) you'll discover the syntax pattern. The pattern is that methods are appended to the prototype property of the Animal object, this means all instances of Animal created will get a move() method, just like it was a class. Other lines in the JavaScript implement the contructor function. A class's modularity is reproduced in JavaScript by using the 'module pattern', this is established with an IIFE (Immediately Invoked Function Expression).

JavaScript's Prototypical Inheritance is simpler than TypeScript implies

So far so good. Now that we know how to append methods to the prototype we could just skip the Transcript and write the JavaScript directly. But what about inheritance? In Typescript you use the extends keyword. I've given a code listing example below and this link will take you again to Typescript playground but brace yourself for the transpiled equivalent for it is very, very scary.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}

class Dog extends Animal {
  bark() {
    console.log("Woof! Woof!");
  }
}

So the transpiled listing is given below in all its scariness. Don't forget, we're deliberately choosing to target a version of JavaScript before the class keyword arrived. We're doing this in the name of investigation! All the really scary code is in the top code block which defines the __extends function.

"use strict";
var __extends = (this && this.__extends) || (function () {
    var extendStatics = function (d, b) {
        extendStatics = Object.setPrototypeOf ||
            ({ __proto__: [] } instanceof Array && function (d, b) { d.__proto__ = b; }) ||
            function (d, b) { for (var p in b) if (b.hasOwnProperty(p)) d[p] = b[p]; };
        return extendStatics(d, b);
    };
    return function (d, b) {
        extendStatics(d, b);
        function __() { this.constructor = d; }
        d.prototype = b === null ? Object.create(b) : (__.prototype = b.prototype, new __());
    };
})();
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());
var Dog = /** @class */ (function (_super) {
    __extends(Dog, _super);
    function Dog() {
        return _super !== null && _super.apply(this, arguments) || this;
    }
    Dog.prototype.bark = function () {
        console.log("Woof! Woof!");
    };
    return Dog;
}(Animal));

I took this code and then I started to remove lines to see what breaks, you might like to do the same as an exercise. I believe the variable b stands for base, d stands for derived and p stands for property. Much of this code is 'polyfill' code which acts to retofit modern features, so a lot could be removed.

My classes are simpler than others and so I could remove loads of code. My classes hold no state which is my preference these days. Without state, my classes have parameterless constructors and the need to call base class constructors is obviated; this also simplified matters.

I had thought that I had completely boiled down the __extends function to one line that uses the Object.create method ...

Dog.prototype = Object.create(Animal.prototype);

An alternative line of code is to the use

Object.setPrototypeOf( Animal.prototype, Creature.prototype )

I asked a pertinent Stack Overflow question while constructing a deeper class hierarchy. That question links to other relevant SO questions.

All this means we can arrive at a much less scary code listing below.

    var Animal = (function () {

        function Animal() {
            return this;
        }

        Animal.prototype.move = function (distanceInMeters) {
            if (distanceInMeters === void 0) {
                distanceInMeters = 0;
            }
            console.log("Animal moved " + distanceInMeters + "m.");
        };

        return Animal;
    }());

    var Dog = (function () {
        
        Object.setPrototypeOf( Dog.prototype, Animal.prototype )

        function Dog() {
            return this;
        }
        Dog.prototype.bark = function () {
            console.log("Woof! Woof!");
        };
        return Dog;
    }());

    var dog = new Dog();
    dog.bark();
    dog.move(10);
    dog.bark();

If we look in the Console in the Chrome Developer Tools then we can see out program's output. If we also type console.info(dog) and expand the nodes then we can see our desired inheritance tree ...

Our target inheritance tree

Speed Warning

Unfortunately that is not the end of the story because during my research I cam across this MDN article which says that the Object.setPrototypeOf technique is ...

Ill-performing. Should be deprecated. Many browsers optimize the prototype and try to guess the location of the method in memory when calling an instance in advance; but setting the prototype dynamically disrupts all those optimizations. It might cause some browsers to recompile your code for de-optimization, to make it work according to the specs.

Clearly, I need to do more research. I will return to this topic...

Links

Other links that maybe useful...

Wednesday, 21 July 2021

Use Python to write file tags from Excel VBA

This post was prompted by a bounty on a Stack Overflow question which details VBA code that can read file tags; questioner wants to know if VBA can write these tags as well. Sorry to say that I do not know of any current VBA referencable type libraries (there was once a DSOFiles.dll but not on my current machine). Instead we can leverage the comprehensive Python ecosystem and create a Python class callable from Excel VBA, below is a Python listing for the FilePropertiesSetter.py script.

The Python COM gateway class pattern is one I give dozens of times on this blog so I will skip details of that. The key magic is in the FilePropertiesSetter.changeSingleProperty() method which I give now without error handling ...

1 properties = propsys.SHGetPropertyStoreFromParsingName(self.filename, 
                            None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
2 propKey = propsys.PSGetPropertyKeyFromName(propName)
3 newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
4 propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
5 properties.SetValue(propKey,propVariantNewValue)
6 properties.Commit()

So, essentially this code opens up the file's tag property store (line 1). On line 2 we find the property's key from the property's friendly name (e.g. "System.Category"). Line 3 determines if we're dealing we an array or not to help construct the new property. Line 4 constructs the new value in a form acceptable to that particular library. Line 5 actually makes the change. Line 6 commits the change and closes the file store.

The rest of the script is essentially helper methods.

The scripts has a number of libraries and so be prepared for some installation, some pip install commands. One pitfall I had to code for is when user passes in a two dimensional variant array such as if they had lifted values off a block of cells (I think the StackOverflow questioner was wanting this), the code to handle this is in FilePropertiesSetter.ensureList() which also split a comma separated string into an array (list).

FilePropertiesSetter.py needs to run once, and will request Administrator privileges to register the class with the COM registry.

FilePropertiesSetter.py

import os
import pythoncom
from win32com.propsys import propsys, pscon
from win32com.shell import shellcon
from win32com.server.exception import COMException
import winerror
import numpy

class FilePropertiesSetter(object):	
    _reg_clsid_ = "{5ED433A9-C5F9-477B-BF0A-C1643BBAE382}"
    _reg_progid_ = 'MyPythonProject3.FilePropertiesSetter'
    _public_methods_ = ['setFilename','setTitle','setKeywords','setCategory','setSubject','setComment']
	
    def __init__(self):
        pass

    def setFilename(self, filename: str):
        if not os.path.exists(filename):
            raise COMException(description="Specified file '" + filename + "' does not exist!  Subsequent calls will fail!", 
                    scode=winerror.E_FAIL, source = "FilePropertiesSetter")
        else:
            self.filename = filename

    def ensureList(self, arg):
        # despite hinting that one pass a string it seems Python will accept a VBA array as a tuple
        # so I need to convert tuples to a list, and split csv strings to a list
        # and flatten any 2d tables to a list

        try:
            if type(arg) is tuple:
                list2 = list(arg)
                
                if numpy.ndim(list2)>1:
                    # flatten any two dimension table to a one dimensional list
                    return [item for sublist in list2 for item in sublist]
                else:
                    return list2
            else:
                if isinstance(arg,list):    
                    return arg
                else:
                    return arg.split(",")

        except Exception as ex:
            raise COMException(description="error in ensureList for arg '" + str(arg)  + "'\n" + (getattr(ex, 'message', repr(ex))) ,
                    scode=winerror.E_FAIL, source = "FilePropertiesSetter")

    def setTitle(self, title: str):
    	# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-title
    	self.changeSingleProperty( "System.Title", title)

    def setKeywords(self, keywords: str):

        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-keywords
        self.changeSingleProperty( "System.Keywords", self.ensureList(keywords))

    def setCategory(self, category: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-category
        self.changeSingleProperty( "System.Category", self.ensureList(category)  )

    def setSubject(self, subject: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-subject
        self.changeSingleProperty( "System.Subject", subject)

    def setComment(self, comment: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-comment
        self.changeSingleProperty( "System.Comment", comment)

    def changeSingleProperty(self,  propName: str, newValue):
        propKey = None 
        if hasattr(self,'filename') and self.filename is not None:      

                try:
                    properties = propsys.SHGetPropertyStoreFromParsingName(self.filename, 
                            None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
                except Exception as ex:
                    raise COMException(description="Could not open properties for file '" + self.filename + "'\n" + (getattr(ex, 'message', repr(ex))) ,
                            scode=winerror.E_FAIL, source = "FilePropertiesSetter")
            
                try:

                    propKey = propsys.PSGetPropertyKeyFromName(propName)
                except Exception as ex:
                    raise COMException(description="Could not find property key for property named '" + propName + "'\n" + (getattr(ex, 'message', repr(ex)))  , 
                            scode=winerror.E_FAIL, source = "FilePropertiesSetter")
                    
                if propKey is not None:
                    try: 
                        newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
                        propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
                        properties.SetValue(propKey,propVariantNewValue)
                        properties.Commit()
                    except Exception as ex:
                        raise COMException(description="Error whilst setting value ...'\n" + (getattr(ex, 'message', repr(ex)))  , 
                                scode=winerror.E_FAIL, source = "FilePropertiesSetter")

def RegisterThis():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(FilePropertiesSetter)

if __name__ == '__main__':
    RegisterThis()	
    pass

def Test():
    propsSetter=FilePropertiesSetter()
    propsSetter.setFilename(r"N:\FileProps\Newbook.xlsx") # use your own filename (obviously)
    propsSetter.setKeywords("python,keywords")
    propsSetter.setCategory("python,category")
    propsSetter.setTitle("python title")
    propsSetter.setSubject("python subject")
    propsSetter.setComment("python comment")

After FilePropertiesSetter.py is run once and correctly registered (don't forget you may need some installation) then it is scriptable from VBA with code like the following ...

Option Explicit

Sub TestPythonClass()

    Dim objPropsSetter As Object
    Set objPropsSetter = VBA.CreateObject("MyPythonProject3.FilePropertiesSetter")
    
    objPropsSetter.setFilename "N:\FileProps\Newbook.xlsx"
    objPropsSetter.setTitle "VBA Title"
    objPropsSetter.setSubject "VBA Subject"
    objPropsSetter.setComment "VBA Comment"
    objPropsSetter.setKeywords "Foo2,bar"
    'objPropsSetter.setKeywords Array("Foo1", "bar1")
    objPropsSetter.setCategory Sheet1.Range("A1:B2").Value2

End Sub

You will need to call setFilename because that is how the script knows which file to operate upon.

Enjoy!

Saturday, 31 October 2020

Internationalise Dates in VBA

VBA does Date Internationalisation! Here find VBA code to generate and interpret dates in foreign languages. In this post I give new code to interpret dates in foreign languages as well as signpost existing code to format dates in foreign languages written by Stack Overflow user GSerg.

A while back I blogged about how VBA can interpret foreign currencies by calling into the COM runtime like other Windows API calls. Dates are another internationalisation problem and the same trick works, we call into oleaut32.dll the COM runtime.

First, let's introduce GSerg's VBA code to write dates in foreign languages. We need this first to generate test data for my code which interprets foreign language dates.

I won't replicate the code to respect intellectual property rights but I give here some test code that calls in to GSerg's FormatForLocale function...

Public Function TestFormatForLocale() As String
    
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    
    '*
    '* FormatForLocale written by GSerg
    '* https://stackoverflow.com/users/11683/gserg
    '*
    '* Find source code at https://stackoverflow.com/questions/8523017/excel-format-value-mask/8523219#8523219
    '*
    Debug.Print FormatForLocale(CDate("12/May/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Oct/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Mar/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    
End Function

The code aboves prints out three dates in German...

12.Mai.2020
12.Okt.2020
12.Mrz.2020

GSerg's code cleverly calls into VarFormatFromTokens and VarTokenizeFormatString to format a date in a foreign language. You can read GSerg's explanation at his StackOverflow answer. For the moment I am content he has generated some good test input for my foreign language date interpretation code.

Next is code to interpret foreign dates, it's far fewer lines because I don't have to build a string buffer instead I supply a string and get a date back. There is some test code at the bottom demonstrating the German dates are being interpreted correctly (I have thrown in a French date as well).

Option Explicit

'* https://docs.microsoft.com/en-us/previous-versions/windows/embedded/aa519031(v=msdn.10)
Private Declare Function VarDateFromStr Lib "oleaut32" (ByVal strIn As Long, ByVal lcid As Long, _
            ByVal dwFlags As Long, ByRef pdateOut As Date) As Long

Public Function VarDateFromStr2(ByVal sInDate As String, ByVal lLCID As Long) As Date
    Dim hres As Long
    Dim pdateOut As Date
    hres = VarDateFromStr(StrPtr(sInDate), lLCID, 0, pdateOut)
    
    If hres = 0 Then
        VarDateFromStr2 = pdateOut
    Else
        Debug.Print "warning error: " & hres
    End If
End Function

Sub TestVarDateFromStr2()
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    Const FR_FR As Long = 1036

    Debug.Print VarDateFromStr2("12.Mai.2020", DE_DE) = CDate("12-May-2020")
    Debug.Print VarDateFromStr2("12.Okt.2020", DE_DE) = CDate("12-Oct-2020")
    Debug.Print VarDateFromStr2("12.Mrz.2020", DE_DE) = CDate("12-Mar-2020")
    Debug.Print VarDateFromStr2("12/mars/2020", FR_FR) = CDate("12-Mar-2020")
    
End Sub

I was inspired to write this post because of a StackOverflow question which uses the MonthName function which will be tied to the VBA installation language. With the couple of programs demonstrated it is possible to break away from VBA's installation language and truly go international.

Wednesday, 30 September 2020

ConnectToConnectionPoint offers low-level alternative wiring for VBA events

I really like when I chance across some low-level technical wizardy to help out when VBA hits its limits ( it is what this blog does best!) Today, on StackOverflow a reward bounty of 500 is being awarded to a cracking answer which uses the ConnectToConnectionPoint Win32 API function call to sink events without using the WithEvents keyword.

So, I came across this StackOverflow Q & A where the questioner is asking how to reduce WithEvent declarations and subs with VBA and ActiveX and the responder provides a solution which uses ConnectToConnectionPoint to acquire events without using WithEvents.

The responder says they found the original code on a Japanese website and indeed I believe they are referring to this from Keiichi Tsunoda: Implementation of the event handling by API : ConnectToConnectionPoint. ConnectToConnectionPoint is defined in shlwapi.h which is part of the Windows Shell API (so it's not part of the original COM runtime API).

Googling a little more and I found a VBFormus post, a Mr Excel post and a GitHub Gist which I have placed in the Links section below.

How significant is this for Excel VBA? I do believe it is already possible to reduce the number of WithEvent declarations by introducing a class and holding an array of instances of those classes. Each class instance would be instantiated with the reference to a ActiveX control acquired using OLEObjects() for a worksheet or Controls() for a UserForm. However, the fact that the implementation of ConnectToConnectionPoint is in the Windows Shell library which is what Windows Desktop and the Windows Explorer use suggests that its use for sinking events from other Windows processes may have a more dramatic potential.

However, Mathieu Guindon who runs the RubberDuck project thinks this is a key technology to solving a glitch that had been an obstacle in implementing MVVM for VBA, here is his blog post Making MVVM Work in VBA Part 2 - Event Propagation

Links

Soft Links assist with Hard Link Hell

If you build an Excel application of any size then you will probably use more than one workbook. To access information in another workbook the standard way is to link. However, having workbooks linked to one another often leads to problems managing the opening and closing of linked workbooks. In this post I offer a 'soft link' which aims to break the hard links which come as a default and let your code take control.

There is such a thing as dependency hell where it required to gather antecedent code or data. A specific instance on Windows is DLL Hell concerning the loading of correct executable libraries. In Excel, we have our own form which I am calling 'Hard Link Hell'.

I call Hard Link Hell the mess that VBA coders can encounter when we build a VBA application of size that spans multiple workbooks. In my opinion, a VBA coder ought to exercise as much control as possible over the opening and closing of workbooks. Have a cell in one workbook link to another raises the spectre of Excel opening linked workbooks when we were not expecting it.

Admittedly, there is some control over the behaviour. So from the Data ribbon if I select Edit Links then I get the following dialog box...

... where we can see in the bottom right corner the Startup Prompt button which if pressed raises the following dialog ...

Nevertheless, I have had Hard Link Hell in the past where I have had the break links and relink to a new workbook. I remember it being a nightmare. So, in this post I give some code called Soft Links which means VBA code can take control of when to open linked workbooks. The code ships two functions to be called from a worksheet, SoftLink(workbookName, sheetName, rangeName) which actually return an Excel.Range object but Excel is clever enough to call the Value property; but this works only for single cell references. So for multiple cells use SoftLinkValue(workbookName, sheetName, rangeName). Be aware that the source cell(s) must be named using a range name. Also in the listing are some test procedures

Note, you will have to write code to open the source workbooks or you will get a #VALUE!, but we wanted to take control and so comes the responsibility to ensure the source workbook is loaded when this function is calculated. Enjoy!

Option Explicit

'* Use this for a source comprising multiple cells
Public Function SoftLinkValue(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String)
    Dim rng As Excel.Range
    Set rng = SoftLink(sWorkbookName, sSheetName, sRangeName)
    SoftLinkValue = rng.Value
End Function

'* Use this for a source comprising single cell, also useful in other VBA code
Public Function SoftLink(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String) As Excel.Range
    Dim wb As Excel.Workbook
    Set wb = OernColItem(Application.Workbooks, sWorkbookName)

    If Not wb Is Nothing Then

        Dim ws As Excel.Worksheet
        Set ws = OernColItem(wb.Worksheets, sSheetName)
        
        If Not ws Is Nothing Then
            Set SoftLink = OernWorksheetRange(ws, sRangeName)
        End If
    End If
End Function

Private Function OernWorksheetRange(ByRef ws As Excel.Worksheet, ByVal sRangeName As String) As Excel.Range
    On Error Resume Next
    Set OernWorksheetRange = ws.Range(sRangeName)
End Function

Private Function OernColItem(ByRef col As Object, ByVal idx As Variant) As Object
    On Error Resume Next
    Set OernColItem = col.Item(idx)
End Function

'**** TEST ****

Sub TestVBACallingSoftLink_LocalSheet()

    Const csSHEET1 As String = "Sheet1"

    Dim rng As Excel.Range
    Set rng = SoftLink(ThisWorkbook.Name, csSHEET1, "A1")
    
    Debug.Assert Not rng Is Nothing
    If Not rng Is Nothing Then
        Debug.Assert rng.Address = "$A$1"
        Debug.Assert rng.Worksheet.Name = csSHEET1
    End If
End Sub


Sub TestVBACallingSoftLink_ExternalWorkbook()
    Const csSHEET1 As String = "Sheet1"

    '*** test setup: create new workbook, add a name
    Dim wbNew As Excel.Workbook
    Set wbNew = Application.Workbooks.Add
    
    Const csNAME_FOO As String = "Foo"
    Dim ws As Excel.Worksheet
    Set ws = wbNew.Worksheets.Item(1)
    ws.Names.Add Name:=csNAME_FOO, RefersToR1C1:="=Sheet1!R4C8"
    ws.Range(csNAME_FOO).Value2 = 42
    '*** end of test setup:

    '*** now we can call our function to get a link to an external workbook
    Dim rng As Excel.Range
    Set rng = SoftLink(wbNew.Name, csSHEET1, csNAME_FOO)
    
    Debug.Assert Not rng Is Nothing
    If Not rng Is Nothing Then
        Debug.Assert rng.Address = "$H$4"
        Debug.Assert rng.Worksheet.Name = csSHEET1
        Stop
    End If
    wbNew.Close False
    Stop
End Sub

Wednesday, 26 August 2020

VBA - Use Dom.SelectNodes and double slash XPath to jump in anywhere in an Xml Document

Don't be tempted to loop through an Xml structure algorithmically when you can jump in using some double slash prefixed XPath.

A question arose on StackOverflow which sadly had been closed by the moderators, so I solved it myself and published the answer here. The questioner says

I want to have flexible code so that I can just point to the nodepath of the financial value and then simply go up or down in the XML tree to find all the other data I need.

So the questioner would prefer not to write code for every Xml document structure instead find the a key node and expect to find the supplementary data in elements not far away. They have given some test data and I have given some code to handle both.

A key feature of the code is to use the SelectNodes() method of the DomDocument object which will give a list of multiple matches. To avoid specifying fixed paths use double slash!

Enjoy!

Option Explicit

Private Sub TestListSingleFinancialValueItems()
    ListSingleFinancialValueItems TestData1
End Sub

Private Sub TestListMultipleFinancialValues()
    ListMultipleFinancialValues TestData2
End Sub

Private Sub ListMultipleFinancialValues(ByVal dom As MSXML2.DOMDocument60)

    Dim nodesFinancialValues As MSXML2.IXMLDOMNodeList
    Set nodesFinancialValues = dom.SelectNodes("//financialvalues")
    
    Dim nodeFinVal As MSXML2.IXMLDOMElement
    For Each nodeFinVal In nodesFinancialValues
        
        Dim sCurrency As String: sCurrency = ""
        
        Dim nodsChildVals As MSXML2.IXMLDOMNodeList
        Set nodsChildVals = nodeFinVal.SelectNodes("value")
        
        If nodsChildVals.Length > 0 Then
            sCurrency = ReadCurrency(nodeFinVal.PreviousSibling)
            Debug.Assert sCurrency <> ""
            
            Dim vals As MSXML2.IXMLDOMElement
            For Each vals In nodsChildVals
                Debug.Print sCurrency & " " & vals.Text
            Next
        End If
    Next

End Sub


Private Sub ListSingleFinancialValueItems(ByVal dom As MSXML2.DOMDocument60)

    Dim nodesFinancialValues As MSXML2.IXMLDOMNodeList
    Set nodesFinancialValues = dom.SelectNodes("//financialvalue")
    
    Dim nodeFinVal As MSXML2.IXMLDOMElement
    For Each nodeFinVal In nodesFinancialValues
        
        Dim sCurrency As String: sCurrency = ""
        sCurrency = ReadCurrency(nodeFinVal.NextSibling)
        Debug.Assert sCurrency <> ""
        Debug.Print sCurrency & " " & nodeFinVal.Text
    Next
End Sub

Private Function ReadCurrency(ByVal xmlElement As MSXML2.IXMLDOMElement) As String
    If Not xmlElement Is Nothing Then
        If xmlElement.BaseName = "currency" Then
            ReadCurrency = xmlElement.Text
        End If
    End If
End Function

Function TestData1() As MSXML2.DOMDocument60
    Dim s
    s = _
    "<transactions>" & _
    "    <transaction>" & _
    "        <transactionID>5</transactionID>" & _
    "        <lines>" & _
    "            <line>" & _
    "                <financialvalue>100.00</financialvalue>" & _
    "                <currency>USD</currency>" & _
    "            </line>" & _
    "            <line>" & _
    "                <financialvalue>200.00</financialvalue>" & _
    "                <currency>USD</currency>" & _
    "            </line>" & _
    "         </lines>" & _
    "    </transaction>" & _
    "</transactions>"
    Dim dom As MSXML2.DOMDocument60
    Set dom = New MSXML2.DOMDocument60
    Debug.Assert dom.LoadXML(s)
    Set TestData1 = dom
End Function

Function TestData2() As MSXML2.DOMDocument60
    Dim s
    s = _
    "<transactions>" & _
    "    <transaction>" & _
    "        <currency>USD</currency>" & _
    "        <financialvalues>" & _
    "            <value>100.00</value>" & _
    "            <value>200.00</value>" & _
    "        </financialvalues>" & _
    "    </transaction>" & _
    "    <transaction>" & _
    "        <currency>USD</currency>" & _
    "        <financialvalues>" & _
    "            <value>300.00</value>" & _
    "            <value>400.00</value>" & _
    "        </financialvalues>" & _
    "    </transaction>" & _
    "</transactions>"
    Dim dom As MSXML2.DOMDocument60
    Set dom = New MSXML2.DOMDocument60
    Debug.Assert dom.LoadXML(s)
    Set TestData2 = dom
End Function


Friday, 24 July 2020

VBA - Writing code in the Immediate window

You can write mini-programs in the Immediate window but you'll need to change how you write code. If you want a VBA code challenge this morning this will challenge you.

So a Stack Overflow answer to this question is due to be awarded a bounty. The prize winning answer doesn't actually solve the question which is how to print a two dimensional array to the Immediate window; instead, the answer highlights the Locals window (it's a favorite of mine as well). Nevertheless this question and its answers caught my attention and amongst them was a mini-program meant to be run entirely from the Immediate window, the code and output is show below.

arr = [ {"A",1; "B",2; "C",3 } ]: _
For r = LBound(arr, 1) To UBound(arr, 1): _
        For c = LBound(arr, 2) To UBound(arr, 2): _
            Debug.Print arr(r, c): _
       Next c: _
Next
A
 1 
B
 2 
C
 3 

I have seen code for the Immediate window before and wondered 'Why bother?' when you can write a small function and so didn't register the pattern and syntax required but today for whatever reason I embraced this syntax.

I wasn't happy with the output of the given code and wanted to amend it and then I hit the syntactical challenges therein. A small list of bullet points is appropriate here.

  • You can't use Dim statements; so write code as if Option Explicit is commented out
  • You can't use a For statement with a Next statement so you are obliged to use multi-line statements
  • If statements cannot be multi-line version they must be single line version. I.e. Don't use End If
  • Feel free to use VBA.IIf instead of an If statement.

So I successfully amended the code to give output with which I'm happy.

    arr = [ {"A",1; "B",2; "C",3 } ]: _
    sAcc = "": _
    For r = LBound(arr, 1) To UBound(arr, 1): _
            For c = LBound(arr, 2) To UBound(arr, 2): _
                bRowEnd = (c = UBound(arr, 2)): _
                sAcc = sAcc & CStr(arr(r, c)) & VBA.IIf(bRowEnd, ";" & vbNewLine, ","): _
           Next c: _
    Next r: _
    Debug.Print sAcc
A,1;
B,2;
C,3;

Be mindful that your variables in the 'ether' of the Immediate window will hang around until either (a) you suffer a state loss or (b) you type End which deliberately causes a variables wipedown. This is why I clear down sAcc at the start of the mini-program, otherwise repeated execution makes it build up.

You can instantiate classes in the Immediate window but the Intellisense didn't work (Intellisense did work for standard modules).

This is all a long way from Python's REPL I must say. If you any comments, suggestions or tips for working with the Immediate window do please drop me a line below.

Monday, 8 June 2020

Python - Wireframe graphics on the worksheet leveraging SVG 3D library

In this post I leverage a brilliant Python library by Philip Rideout which draws wireframe graphics to SVG files and then I convert the SVG drawing directives to Shapes on an Excel worksheet.

This means I can take this SVG file of an octahedron

and convert it to this on the Excel worksheet.

Background

On StackOverflow, a question arose about drawing a wireframe box. I had looked into drawing on the worksheet using GDI before but I ruled out that approach. Instead, it is required to draw shapes on the worksheet. GDI still works for drawing on a Form as this Stars and Stripes example demonstrates. Using the macro recorder helps to understand how to build a free form shape but we'd need to write some 3D maths library in VBA to calculate all the vertices etc.

Luckily a brilliant library written by Philip Rideout exists and can do all the business of defining wireframe shapes in terms of vertices and also how the camera is pointing and it will do all the hard mathematics and draw to an SVG file. Then, I give code which parses that SVG file (it is XML after all) and I convert the polygon drawing directives to Excel (Freeform) Shapes.

The Setup

In Visual Studio give yourself a new Python project. Add the svg3d.py file from Github. Also add the example.py file from Github. Set the example.py file to be the file to run on startup. As it stands the code will generate an SVG file of an octahedron, you can see a rendering at the top of this page. It is very good, the fill on the front sides is set to 75% opacity so you can still see the rear faces. The rear faces are drawn first meaning I don't have to worry about which faces are hidden etc.

What is now needed is just a little more code to open an Excel workbook so add the following to the end of the example.py file

class ScreenUpdatingRAII(object):
    def __init__(self, app, visible:bool=False):
        self.app = app
        self.saved = app.ScreenUpdating
        app.ScreenUpdating = visible

    def restore(self):
        self.app.ScreenUpdating = self.saved
        self.app = None


def convertSvgToExcelShapes(filename):
    import xml.etree.ElementTree as ET
    from win32com.client  import GetObject,Dispatch

    # code below is highly dependent on the child
    # structure because xpath was not working for me (my bad)
    dom = ET.parse(filename)
    rootxml = dom.getroot()
    g = rootxml[1] # second child 
    wb = Dispatch(GetObject(r"C:\Users\Simon\source\repos\WireframeExcelShapes\WireframeExcelShapes\WireframeExcelShapes.xlsx"))
    app = Dispatch(wb.Parent)
    ws = Dispatch(wb.Worksheets.Item("WireFrame"))

    shps = Dispatch(ws.Shapes)

    for x in shps:
        Dispatch(x).Delete()
    idx =0
    scale, xoffset, yoffset = 500, 300,300
    
    screenUpdates = ScreenUpdatingRAII(app)

    for polygon in g:

        # triple nested list comprehension parsing the points by splitting 
        # first by space then by comma then converting to float
        points = [[float(z[0])*scale+xoffset, float(z[1])*scale+yoffset] for z in [y.split(',') for y in [x for x in polygon.attrib['points'].split()]]]

        #print(points)
        msoEditingAuto,msoSegmentLine, msoFalse, msoTrue = 0,0,0, -1 

        freeformbuilder=shps.BuildFreeform(msoEditingAuto, points[0][0] , points[0][1])
        freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[1][0] , points[1][1])
        freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[2][0] , points[2][1])
        freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[0][0], points[0][1])
        newShp = Dispatch(freeformbuilder.ConvertToShape())

        shpFill = Dispatch(newShp.Fill)

        shpFill.Visible = msoTrue
        shpFill.Transparency = 0.25
        shpFill.Solid
        shpFill.ForeColor.RGB = 0xFFFFFF 
        idx=+1

    screenUpdates.restore()
    pass

        

filename = "octahedron.svg" 
generate_svg(filename)
convertSvgToExcelShapes(filename)

First comes a class called ScreenUpdatingRAII() which I use to switch on screen updates whilst drawing. This speeds the code and also kills screen flicker.

Next comes the function convertSvgToExcelShapes() which loads the SVG file into Python's Element tree XML parser. Then using some COM calls will open an Excel workbook which you must have saved before hand, and then accesses a sheet called WireFrame which you must have created beforehand as well! The code deletes any Shapes from that sheet and then proceeds to draw an Excel free form shape for each Polygon element in the SVG file. I haven't really added much value here it was quite straightforward. The dramatic output is 99% to Philip's credit.

However, I am proud of a line of code I did contribute. My triple nested list comprehension parses the string of points co-ordinates, scales and translates (math) them ready for the worksheet...

points = [[float(z[0])*scale+xoffset, float(z[1])*scale+yoffset] for z in [y.split(',') for y in [x for x in polygon.attrib['points'].split()]]]

Links

Below is a link to Philip's blog and his Github repo.

Wednesday, 3 June 2020

VBA - Shell a process and acquire its StdIn, StdOut, StdErr pipes

Juts a quickie. On Stackoverflow some code posted which shells a process and reads the piped output purely using Window API calls, quite impressive but seems to be tripping up on some 64-bit issue. Actually VBA developers need not wrestle Windows API on this one and can in fact use the Windows Script Host Object Model library instead.

Option Explicit

Function ShellAndGetText() As String

    '* Tools -> References
    '* Windows Script Host Object Model

    Dim oWshShell As IWshRuntimeLibrary.WshShell
    Set oWshShell = New IWshRuntimeLibrary.WshShell


    Dim oWshExec As IWshRuntimeLibrary.WshExec
    
    Dim sComSpec As String
    sComSpec = Environ$("COMSPEC")
    
    Dim sReturnText As String
    Set oWshExec = oWshShell.Exec(sComSpec & " foo.exe")
    
    While oWshExec.Status = WshRunning
        DoEvents
    Wend
    If oWshExec.Status = WshFinished Then
        '* success
        sReturnText = oWshExec.StdOut
    Else
        '* failure
        sReturnText = oWshExec.StdErr
    End If

    ShellAndGetText = sReturnText

End Function

Wednesday, 27 May 2020

VBA, ADODB - Asynchronous Query Execution with ADODB.Connection Events

VBA doesn't have multiple threads but that's ok because network latent operations such as running queries are packed into libraries which do the multi-threading for you. The ADODB.Connection object that is used to connect to a database can run queries in asynchronous mode with notification of completion implemented with an event if you declare the Connection object with the WithEvents keyword and and supply adAsyncExecute to the Connection's Execute method.

What follows is a code pattern not actual code because I do not know what databases you have installed on your computer dear reader. But what must be stressed is that this is to be placed into a class module (not a standard module). I called my class AsyncQuery

Option Explicit

Private WithEvents cnAsynchronousConnection As ADODB.Connection

Public Sub RunAsyncQuery()
    
    Set cnAsynchronousConnection = New ADODB.Connection

    cnAsynchronousConnection.connectionString = "" '<---- Insert your connection string

    
    cnAsynchronousConnection.Open
    
    Debug.Print "Preparing to execute asynchronously: " & Now
    cnAsynchronousConnection.Execute "<select query>", adAsyncExecute  '<----- Insert you own query

    Debug.Print "Has begun executing asynchronously: " & Now
End Sub

Private Sub cnAsynchronousConnection_ExecuteComplete(ByVal RecordsAffected As Long, _
        ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, _
        ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print "The query has completed asynchronously: " & Now
End Sub

Then in a standard module place the following code.

Option Explicit

Sub Test()
    Dim oAsyncQuery As AsyncQuery
    Set oAsyncQuery = New AsyncQuery

    oAsyncQuery.RunAsyncQuery

End Sub

So without a database we can't take this any further. There are two key points working here, firstly there is the WithEvents keyword in the variable declaration which is only valid in a class module. Secondly there is the flag adAsyncExecute which must be passed to the Connection's Execute method. I have highlighted these key points in bold red.

Monday, 25 May 2020

A rather neat piece of plumbing, Chrome extension pushes byte array of jobs data to Excel via Python

Transcript

The United States is suffering from extremely high unemployment and in this post I give an application that harvests job leads from a leading jobs website. The application has numerous technical components, (i) a Chrome extension, (ii) a Python webserver housed as a COM component and (iii) a VBA deserialization component. Taken together they demonstrate transmitting binary data from the browser through to the Excel worksheet.

In the US, initial jobless claims are running at a 4-week average of 3 million and the non-farm payrolls are currently at 20 million. These figures are both depressing and staggering. Europe can expect suffering on similar terms. Hopefully the code in this post can assist some to find work.

Co-browsing vs Web-scraping

Websites depend upon ad revenue to survive and so they need humans to see the adverts placed. Every time a human sees an advert it is known as an impression. Web-scraping is the process of running code to fetch a web page and to scrape data from the HTML; this typically involves the automation of a hidden web browser and as such any adverts on a hidden web page are no longer viewable but rendering ad impression statistics false. Eventually, this means that ad revenue is debased and devalued. As such, I disapprove of web scraping.

Instead, I give a ‘co-browsing’ application where code captures job leads from a web page that a human user is browsing. So this application is only active when a human browses a web page. This means any advert impressions are genuine and website’s revenue is not threatened.

The code

There are three separate parts to this application, (i) the chrome extension, (ii) the Python web server (housed as a COM component) and (iii) the VBA deserialization component. They are all in Github, https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/

The Chrome Extension

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/Chrome%20Extension/

The chrome extension will wait for a jobs page to load and then read the jobs data, it builds a JavaScript array of jobs and when complete it will convert the single dimensioned array of jobs into a two-dimensional grid array where each row is one job and the attributes are spread across the columns.

I convert to a grid because ultimately it will be sent to an Excel session where it is to be pasted onto a worksheet. The grid is then persisted to a byte array instead of JSON to take advantage of a data interchange format native to VB6, VBA that I have re-discovered and that allows a byte array to be deserialized to a VBA (OLE Automation) Variant (two dimensional).

Once converted to a byte array we make an XMLHttpRequest() to the Python web server (see next component). If you are experimenting then you might need to change port number in the code here.

There are two main JavaScript files, content.js and JavaScriptToVBAVariantArray.js. The former houses logic specific to this application whilst the latter is the array conversion code library file which I intend to use across a number of projects.

Python Web Server housed as a COM component

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/PythonWebSeverCallsBackToExcel

I have previously written about and given code as to how to write a Python web server housed as a COM component and instantiable from VBA. I have also previously written about and given code as to how to call back into Excel VBA from a Python class.

But there is something new in this Python web server which needs detailing, in short one cannot simply call back into Excel with an interface pointer passed in a different threading apartment; instead the interface pointer has first to be ‘marshalled’. I have encapsulated the plain vanilla callback code in the Python class CallbackInfo and the special marshalling case in its derived class MarshalledCallbackInfo.

In the context of the application, the Python web server is part of the pipeline that passes the byte array from the Chrome extension into Excel VBA. It calls into Excel VBA by calling Application.Run on a (marshalled) Excel.Application pointer. The name of the procedure called by Application.Run is configurable, and passed in. Time to look into the VBA code.

Excel VBA

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/ExcelVBA

I do not check into whole workbooks, I check in the individual code modules instead. Thus to build the Excel VBA workbook code base one needs to import the modules. Luckily, I wrote one module called devBuild to import the rest of them. I intend to follow this pattern when placing code in GitHub. Look at the README.md file for more detail. From here, I’ll assume you’ve built a workbook codebase.

I have written about the serialization and deserialization of Variants to byte arrays and back again so I’ll refer you to that post for the details. In short we take the byte array passed from the Chrome extension via the Python web server and deserialize this to a two dimensional variant array which can then be pasted onto the worksheet.

I guess I could write some more code to build a cumulative list but the point of this project was to show binary data being passed from browser to Excel, to demonstrate (a) the plumbing and (b) the binary data interface format (i.e. no JSON).

Monday, 18 May 2020

Returning to GitHub - this time at the command line

So my blog posts have become large and full of code, best to start using GitHub to store the code. Last time I used to use the Win32 desktop application but this time around I'm going to learn to use the command line. This post is about my efforts to configure and check in code from the command line. I hope to abstract a cheat sheet at some point.

So I started with the Hello World and execute Step 1. Create a Repository (this is still web-page based) but after that I was presented with a new web page of steps reproduced below and so I started from the command line to follow these instructions...

echo "# ExcelDevelopmentPlatform" >> README.md
git init
git add README.md
git commit -m "first commit"
git remote add origin https://github.com/smeaden/ExcelDevelopmentPlatform.git
git push -u origin master

So I executed the first three lines and whilst there is not much feedback at the command line, the first command git init creates a .get subfolder in the current selected folder, this is the local git repository.

C:\Users\Simon>git init
Initialized empty Git repository in C:/Users/Simon/.git/

C:\Users\Simon>echo "# ExcelDevelopmentPlatform" >> README.md

C:\Users\Simon>git add README.md

After I executed the first three lines I got slowed up on the fourth. Initially, I got the error below but it turned out that I had put a space between the dash and the em.

C:\Users\Simon>git commit - m "first commit"
error: pathspec '-' did not match any file(s) known to git
error: pathspec 'm' did not match any file(s) known to git
error: pathspec 'first commit' did not match any file(s) known to git

So trying again with corrected line I then got

C:\Users\Simon>git commit -m "first commit"

*** Please tell me who you are.

Run

  git config --global user.email "you@example.com"
  git config --global user.name "Your Name"

to set your account's default identity.
Omit --global to set the identity only in this repository.

fatal: unable to auto-detect email address (got 'Simon@xxxx.(none)')

So it seems I have to configure credentials; so I do but not globally ...

C:\Users\Simon>git config user.email "xxxx@xxxx.com"

C:\Users\Simon>git config user.name "S Meaden"

then I try (yet) again with the commit which gives...

C:\Users\Simon>git commit -m "first commit"
[master (root-commit) adca8f5] first commit
 1 file changed, 1 insertion(+)
 create mode 100644 README.md

So now over that hurdle I can continue with the instructions, the next line below is what ties the local repository to the one on GitHub...

C:\Users\Simon>git remote add origin https://github.com/smeaden/ExcelDevelopmentPlatform.git

C:\Users\Simon>git push -u origin master

Upon executing the second line from above there was a delay and then the following dialog box appeared which I then completed and clicked login.

which then progressed that line command's execution thus ...

C:\Users\Simon>git push -u origin master
Enumerating objects: 3, done.
Counting objects: 100% (3/3), done.
Writing objects: 100% (3/3), 245 bytes | 245.00 KiB/s, done.
Total 3 (delta 0), reused 0 (delta 0)
To https://github.com/smeaden/ExcelDevelopmentPlatform.git
 * [new branch]      master -> master
Branch 'master' set up to track remote branch 'master' from 'origin'.

And now checking the web page I have a ReadMe.Md file checked in.

Moving the Git Repository

Next I ran a command git status which listed all the files not yet tracked and there were too many and also made me realise I could locate the git repository better such as in the directory where Microsoft Visual Studio creates projects. After a quick StackOverflow surf it looked like I could just move the directory and I tried at the command line with no success but then used Windows Explorer with drag and drop to take it to it new location. The command git status then failed which meant I needed to change the current working directory to where I relocated the .git folder. Once there I could run git status and git log again so it looks like this move succeeded.

C:\Users\Simon>git status
warning: could not open directory 'Application Data/': Permission denied
...
On branch master
Your branch is up to date with 'origin/master'.

Untracked files:
  (use "git add <file>..." to include in what will be committed)
        .Neo4jDesktop/
        .cargo/
        .conda/
    ...
nothing added to commit but untracked files present (use "git add" to track)

C:\Users\Simon>git log
commit adca8f54ee85ed4395b1df6b35eae5ecb70d7c5e (HEAD -> master, origin/master)
Author: S Meaden <simonmeaden@xxxx.com>
Date:   Fri May 15 12:31:29 2020 +0100

    first commit


C:\Users\Simon>move README.md C:\Users\Simon\source\repos
        1 file(s) moved.

C:\Users\Simon>move \.git C:\Users\Simon\source\repos
The system cannot find the file specified.

C:\Users\Simon>explorer .

At this point I used Windows explorer to drag the .git folder down to .\source\repos

C:\Users\Simon>git log
fatal: not a git repository (or any of the parent directories): .git

C:\Users\Simon>cd source

C:\Users\Simon\source>cd repos

C:\Users\Simon\source\repos>git log
commit adca8f54ee85ed4395b1df6b35eae5ecb70d7c5e (HEAD -> master, origin/master)
Author: S Meaden <simonmeaden@xxxx.com>
Date:   Fri May 15 12:31:29 2020 +0100

    first commit

C:\Users\Simon\source\repos>git status
On branch master
Your branch is up to date with 'origin/master'.

Untracked files:
  (use "git add ..." to include in what will be committed)
        ATLProject1/
        ATLProject2/
        ...
nothing added to commit but untracked files present (use "git add" to track)

C:\Users\Simon\source\repos>

I found the following video and discovered that the first 12-15 minutes more or less cover the same territory as my notes. So for more information, I recommend this video.

Finally, it would appear GitHub is now free both public and private repositories with unlimited collaborators

Links

  • Markdown cheat sheet
  • Friday, 8 May 2020

    Python COM Component to get windows handles hierarchy

    In this blog I give a Python COM component that returns a windows handle hierarchy as found in Spy++. It returns the details in one large table.

    So I had cause to poke around the windows hierarchy for Excel and I had previously written code to query the Windows API and get all the windows handles in a tree just like Spy++ but I chose to revisit the code with Python. Also, I chose to return the results in tabular form.

    Here is the Python listing

    import pythoncom 
    import os
    import logging
    import win32gui
    import win32con 
    
    class LocalsEnhancedErrorMessager(object):
        @staticmethod
        def Enhance(ex, localsString):
            locals2 = "n Locals:{ " + (",n".join(localsString[1:-1].split(","))) + " }"
            if hasattr(ex,"message"):
                return "Error:" + ex.message + locals2
            else:
                return "Error:" + str(ex) + locals2
    
    
    class PythonFindWindow(object):
        _reg_clsid_ = "{490784B6-5174-4794-8888-769DE4688B2C}"
        _reg_progid_ = 'PythonInVBA.PythonFindWindow'
        _public_methods_ = ['FindAllXlMainWindows','FindXlMainWindowWithCaptionFragment','FindChildWindows']
        _reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER ## uncomment this for a separate COM Exe server instead of in-process DLL
    
        def FindAllXlMainWindows(self):
            try:
                logging.basicConfig(filename =  (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', 
                            format="%(asctime)s: %(message)s", 
                            level=logging.INFO, datefmt="%H:%M:%S")
    
                windows = []
    
                hwnd = win32gui.FindWindowEx(0,0,"XLMAIN",None)
                while hwnd != 0:
                    windows.append(hwnd)
                    hwnd = win32gui.FindWindowEx(0,hwnd,"XLMAIN",None)
    
                logging.info('PythonFindWindow.FindAllXlMainWindows completed')
                return windows
            except Exception as ex:
                msg = "PythonFindWindow.FindAllXlMainWindows error:" + LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
                logging.info(msg)
                return msg
    
        def FindXlMainWindowWithCaptionFragment(self, captionStringFragment):
            try:
                logging.basicConfig(filename =  (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', 
                            format="%(asctime)s: %(message)s", 
                            level=logging.INFO, datefmt="%H:%M:%S")
    
                windows = []
    
                hwnd = win32gui.FindWindowEx(0,0,"XLMAIN",None)
                while hwnd != 0:
                    caption = win32gui.GetWindowText(hwnd)
                    if captionStringFragment in caption:
                        windows.append(hwnd)
                    hwnd = win32gui.FindWindowEx(0,hwnd,"XLMAIN",None)
    
                logging.info('PythonFindWindow.FindXlMainWindowWithCaptionFragment completed')
                return windows
            except Exception as ex:
                msg = "PythonFindWindow.FindXlMainWindowWithCaptionFragment error:" + LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
                logging.info(msg)
                return msg
    
    
        def FindChildWindows(self, parentHandle, selectStyles):
            try:
                logging.basicConfig(filename =  (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', 
                            format="%(asctime)s: %(message)s", 
                            level=logging.INFO, datefmt="%H:%M:%S")
    
                windows = []
                hwnd = parentHandle
                row = [hwnd,0,"{0:#0{1}x}".format(hwnd,8), 
                                    win32gui.GetWindowText(hwnd), 
                                    win32gui.GetClassName(hwnd),
                                    win32gui.GetWindowLong(hwnd, win32con.GWL_STYLE)]
                windows.append(row)
    
                self.FindChildWindowsInner(parentHandle,windows, selectStyles,0)
                
                logging.info('PythonFindWindow.FindChildWindows completed')
                return windows
            except Exception as ex:
                msg = "PythonFindWindow.FindChildWindows error:" + LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
                logging.info(msg)
                return msg
    
        def FindChildWindowsInner(self, parentHandle, windows, selectStyles, depth):
            try:
    
                hwnd = win32gui.FindWindowEx(parentHandle,0,None,None)
                while hwnd != 0:
                    style = win32gui.GetWindowLong(hwnd, win32con.GWL_STYLE)
                    stylesSelected = True if selectStyles is None else (style & selectStyles)!=0
                    if stylesSelected:
                        row = [hwnd,parentHandle,"{0:#0{1}x}".format(hwnd,8), 
                                         win32gui.GetWindowText(hwnd), 
                                         win32gui.GetClassName(hwnd),
                                         style]
                        windows.append(row)
                        self.FindChildWindowsInner(hwnd, windows, selectStyles, depth+1)
                    hwnd = win32gui.FindWindowEx(parentHandle,hwnd,None,None)
                
                
                return windows
            except Exception as ex:
                msg = "PythonFindWindow.FindChildWindowsInner error:" + LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
                logging.info(msg)
                return msg
    
    
    
    def run():
        # this code is to be run in Microsoft Visual Studio by pressing F5
        # it is a developer's entry.  for production instantiate the COM component
        try:
    
            print("Executing run")
            print((os.path.dirname(os.path.realpath(__file__))))
    
            logging.basicConfig(filename = (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', 
                            format="%(asctime)s: %(message)s", 
                            level=logging.INFO, datefmt="%H:%M:%S")
    
            fw = PythonFindWindow()
    
            xlMains = fw.FindAllXlMainWindows()
            
            windowList = fw.FindChildWindows(xlMains[0], win32con.WS_VISIBLE)
    
            logging.info('called PythonFindWindow.FindChildWindows ...n')
    
            logging.info('finishing run()n')
        except Exception as ex:
            print(ex)
    
    def RegisterCOMServers():
        print("Registering COM servers...")
        import win32com.server.register
        win32com.server.register.UseCommandLine(PythonFindWindow)
    
    if __name__ == '__main__':
        
        RegisterCOMServers()
        run()

    and here is some sample VBA client code...

    Option Explicit
    
    Const WS_VISIBLE As Long = &H10000000   'WS_VISIBLE = 0x10000000
    
    Sub Test()
        Dim obj As Object
        Set obj = VBA.CreateObject("PythonInVBA.PythonFindWindow")
        
        Dim vXlMains
        vXlMains = obj.FindXlMainWindowWithCaptionFragment("MyWorkbook")
        
        Dim vWindows
    
        vWindows = obj.FindChildWindows(vXlMains(0), Empty)  '* No styles to select with, so selects all
        vWindows = obj.FindChildWindows(vXlMains(0), WS_VISIBLE)  '* Only shows those that are visible (and whose ancestors are visible)
        
        Dim lRow As Long
        For lRow = LBound(vWindows, 1) To UBound(vWindows, 1)
            If vWindows(lRow, 4) = "EXCEL7" Then
                Stop
            End If
        Next
        Stop
    End Sub

    Once the table is returned one can dig in and find what you want, much better to take a whole snapshot recursing down through the hierarchy then to piece together separate calls to FindWindow in my humble opinion.