Looking for a list of dictionaries

[VisioForHVAC]

I have a custom shape that identifies a part that needs to be ordered and added to the bill of material for the project. The shape has the following shape data fields, TAG, PART NO, QTY, MANUFACTURER, and DESCRIPTION. I am working on a VBA script that reads through all of these shapes and combines it into one list. Eventually I will want to export that list to Excel. I'm probably thinking a csv for that, but I haven't gotten there yet.

Right now, I use a separate dictionary for each shape data field. I use the "PART NO" entry as the key to keep them organized. I feel like this isn't very efficient and there is probably a better solution that I'm not thinking of. What I really want is a list of dictionaries with multiple key value pairs. Below is what I would do in python. Is there any way to do something similar to this in VBA? Does anyone have advice on a better approach? I'm still fairly new to VBA so any input is welcome.

Code Select Expand

ls = [
    {"tag": "A", "partNO": "a", "qty": "1", "manuf": "Manuf A", "description": "the letter a"},
    {"tag": "B", "partNO": "b", "qty": "1", "manuf": "Manuf B", "description": "the letter b"},
    {"tag": "C", "partNO": "c", "qty": "1", "manuf": "Manuf C", "description": "the letter c"},
    ]

print(ls)

Code Select Expand

Public Sub test()

Dim vPage As Visio.Page
Set vPage = ActivePage
   
Dim shp As Visio.Shape
Dim tag As String
Dim partNo As String
Dim qty As Integer
Dim manuf As String
Dim desc As String

Dim tagDict As New Dictionary
Dim partNoDict As New Dictionary
Dim qtyDict As New Dictionary
Dim manufDict As New Dictionary
Dim descDict As New Dictionary

For Each shp In vPage.Shapes
   
    If shp.CellExistsU("Prop.testTag" & THePropertyName, 0) Then             
       
        tag = UCase(shp.CellsU("Prop.tag").ResultStr(visNone))
        partNo = UCase(shp.CellsU("Prop.PartNo").ResultStr(visNone))
        qty = shp.CellsU("Prop.qty.Value").ResultInt(visNone, 0)
        manuf = UCase(shp.CellsU("Prop.manuf").ResultStr(visNone))
        desc = UCase(shp.CellsU("Prop.descr").ResultStr(visNone))

        If partNoDict.Exists(partNo) Then
            qtyDict(partNo) = qtyDict(partNo) + qty
        Else           
            tagDict.Add partNo, tag
            partNoDict.Add partNo, partNo
            qtyDict.Add partNo, qty
            manufDict.Add partNo, manuf
            descDict.Add partNo, descDict           
        End If   
    End If
Next
   
End Sub


[Yacine]

You're pointing to a very vast aspect of Visio. Data handling and exchange with external apps.
There are so many possibilities.

Just to name some.
- reports - they export directly to Excel if wished
- linking shapes to an external DB or Excel file with refresh and so on.
- opening data sources by code for reading and writing
- and probably many others ...

Your method, writing data in individual dictionaries looks indeed clunky. A collection would make more sense???

Very cheap, but efficient: build up a string with defined separators.