Creating dictionary within dictionary for DDL and DML schemas

[jaryszek]

hi Guys,

i have 2 worksheets - ExternalDDL and ExternalDML where i have:

EcternalDDL:
Screenshot_24.png

ExternalDML:
Screenshot_23.png

As you can see thanks to these 2 tables we can recreate whole schema DDL and values DML for each table in workbook.
The idea is go with workbook tables and use DDL and DML data in dictionary (checking if table column exists) to recreate each table.

My code is looking like:

Sub test()

Dim DicDdlDml As Object
Dim ArrayDDL As Variant
Dim ArrTest As Variant
Dim e As Variant
Dim i, y As Long

Set DicDdlDml = CreateObject("Scripting.dictionary")

With Worksheets(1)

    ArrayDDL = .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1)
    
      For i = 1 To UBound(ArrayDDL)
      
            ReDim ArrTest(1 To UBound(ArrayDDL, 2) - 1)
      
            If Not DicDdlDml.exists(ArrayDDL(1, i)) Then
                For y = 2 To UBound(ArrayDDL, 2)
                    ArrTest(y - 1) = ArrayDDL(i, y)
                Next y

            DicDdlDml("DDL") = ArrTest

            End If

        Next i
        
        For Each e In DicDdlDml("DDL").keys
            Debug.Print e
            Debug.Print DicDdlDml("DDL")(e)
        Next e

End With

End Sub

I stopped here because i do not know how to get:
DicDdlDml("DDL")(ColumnName) and DicDdlDml("DML")(ColumnName) with characteristic attributes for each of them.

So generaly i want to loop through existing table ("Table1" in TableSource worksheet) and if column from DicDdlDml("DDL") and DicDdlDml("DML") exists - use attributes.
For DML it will be just resizing array to range but for DDL i have to loop through each case (If it has formula) and apply to table.cells(1,columnNumber).

How to do this?
How to nest dictionaries to get:
DicDdlDml("DDL")("Name") = array(ColumnType,HasFormula,FormulaValue)
DicDdlDml("DDL")("Name") = array(allValues in ColumnName)

In other words:
How to loop through table and find specific array within dictionary for DML and DDL worksheets data?

If you have any questions for me - please ask.

Please help,
Jacek

[jaryszek]

Hi,

anyone?

Jacek

[jaryszek]

I added also tableName column and now i am trying to hve 3 nested dictionaries keys:

Sub test()

Dim DicDdlDml As Object
Dim ArrayDDL As Variant
Dim ArrTest As Variant
Dim e As Variant
Dim i, y As Long
Dim InnerDic As Object

Set DicDdlDml = CreateObject("Scripting.dictionary")
'Set DicDdlDml("DDL") = CreateObject("Scripting.dictionary")
'Set DicDdlDml("DML") = CreateObject("Scripting.dictionary")

With Worksheets(1)

    ArrayDDL = .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1)
    
      For i = 1 To UBound(ArrayDDL)
      
      Set InnerDic = CreateObject("Scripting.dictionary")
            ReDim ArrTest(1 To UBound(ArrayDDL, 2) - 1)
      
            If Not DicDdlDml.exists(ArrayDDL(i, 1)) Then
                For y = 2 To UBound(ArrayDDL, 2)
                    ArrTest(y - 1) = ArrayDDL(i, y)
                Next y

                
    If Not DicDdlDml.exists(ArrTest(4)) Then
       Set DicDdlDml("table1") = CreateObject("Scripting.Dictionary")
       Set DicDdlDml("table1")("DDL") = CreateObject("Scripting.Dictionary")
       DicDdlDml("table1")("DDL").Add ArrayDDL(i, 1), ArrTest
       
        For Each e In DicDdlDml("DDL").keys
            Debug.Print e
            Dim arrTest2 As Variant
            arrTest2 = DicDdlDml("DDL")(e)
            Debug.Print
        Next e
       
       
       DicDdlDml("table1")("DDL").Add ArrayDDL(i, 1), ArrTest
    End If
    If Not DicDdlDml("DDL").exists(ArrayDDL(i, 1)) Then
        DicDdlDml("DDL").Add ArrayDDL(i, 1), ArrTest
    End If

    End If

        Next i

End With

End Sub

but this is not working.
It would be awesome to have hierarchy like:

dic(tableName) --> Dic(DDL) or Dic(DML) --> dic(ColumName)

Modified workbook in attachment,
best,
Jacek