Windchill & VBA 05) Create WTPart #2 - 작업중

□ Code to convert excel table to JSON file

[엑셀 Table]

Caution) 

The "PhantomManufacturingPart" value can only be "yes" or "no"

▷ VBA Code

Option Explicit
Public jsonString As String
Public Sub ExcelToNestedJson()

    Dim rng As Range
    Dim myitemName As String
    Dim myitem As Dictionary
    Dim subitem As Dictionary
    Dim BooleanValue As Variant

    Dim i As Integer

    '// Initialize myitem dictionary
    Set myitem = New Dictionary

    '// Set data range
    Set rng = Worksheets("CreateWTPart").Range("A7", Cells(Rows.Count, "A").End(xlUp))

    '// Iterate over each row in the data range
    For i = 0 To rng.Count - 1
        myitemName = Worksheets("CreateWTPart").Cells(i + 7, "A").value
                
        If Worksheets("CreateWTPart").Cells(i + 7, "B") = "no" Then
                BooleanValue = False
        ElseIf Worksheets("CreateWTPart").Cells(i + 7, "B") = "yes" Then
                BooleanValue = True
        Else
                BooleanValue = Worksheets("CreateWTPart").Cells(i + 7, "B").value
        End If
        
                
        If myitemName = "AssemblyMode" Then
                
                '//ACreate sub-item dictionary for AssemblyMode
                 Set subitem = New Dictionary
                 subitem("Value") = BooleanValue
                 subitem("Display") = BooleanValue
                        
                 '// Add subitem dictionary to myitem
                 myitem(myitemName) = subitem
                
         Else
                 '// 일반 항목의 경우 myitem에 값 추가
                 myitem(myitemName) = BooleanValue
                
             End If

    Next i

    '// JSON 문자열로 변환 및 결과 출력
    jsonString = ConvertToJson(myitem, Whitespace:=2)

    Debug.Print jsonString
    jsonString = Mid(jsonString, 2, Len(jsonString) - 2)
    Worksheets("CreateWTPart").Range("E11").value = jsonString

End Sub