Tableauワークブック解析マクロ

Tableauのワークブック（TWBファイル）について、ワークシートごとのレイアウトの内容と、データソースごとの項目の一覧を抽出できるマクロです。

' カラム名を整形する関数
Private Function FormatColumnName(inputStr As String) As String
    Dim parts() As String
    Dim secondPart As String
    
    ' 「.」で分割
    parts = Split(inputStr, ".")
    
    ' 分割した要素が存在する場合
    If UBound(parts) >= 0 Then
        ' 最後の要素を取得
        secondPart = parts(UBound(parts))
        
        ' 「:」で分割
        Dim colonParts() As String
        colonParts = Split(secondPart, ":")
        
        ' 「:」で分割した2要素目を返却
        If UBound(colonParts) >= 1 Then
            FormatColumnName = Replace(Replace(colonParts(1), "[", ""), "]", "")
        Else
            ' 「:」がない場合は「[」と「]」を除去して返却
            FormatColumnName = Replace(Replace(secondPart, "[", ""), "]", "")
        End If
    Else
        ' 「.」で分割できない場合は「[」と「]」を除去して返却
        FormatColumnName = Replace(Replace(inputStr, "[", ""), "]", "")
    End If
End Function

Sub ExtractWorksheetNames()
    Dim xmlDoc As Object
    Dim worksheetNodes As Object
    Dim worksheetNode As Object
    Dim ws As Worksheet
    Dim outputSheet As Worksheet
    Dim i As Long
    
    ' 「メイン」シートを参照
    Set ws = ThisWorkbook.Worksheets("メイン")
    
    ' XMLファイルのパスを取得
    Dim xmlFilePath As String
    xmlFilePath = ws.Range("C2").Value
    
    ' XMLドキュメントを作成
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    
    ' ファイルが存在するか確認
    If Dir(xmlFilePath) = "" Then
        MsgBox "指定されたXMLファイルが見つかりません: " & xmlFilePath, vbCritical
        Exit Sub
    End If
    
    ' XMLファイルを読み込む
    xmlDoc.Load (xmlFilePath)
    
    ' <worksheet>ノードを取得
    Set worksheetNodes = xmlDoc.getElementsByTagName("worksheet")
    
    ' 出力用のシートを作成（既存の場合は削除）
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("ワークシート解析結果").Delete
    ThisWorkbook.Worksheets("データソース解析結果").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set outputSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    outputSheet.Name = "ワークシート解析結果"
    
    ' ヘッダー追加
    outputSheet.Range("A1").Value = "ワークシート名"
    outputSheet.Range("B1").Value = "カラム名"
    outputSheet.Range("C1").Value = "配置場所"
    outputSheet.Range("D1").Value = "マーク種類"
    
    ' ワークシートごとにループ
    Dim outputRow As Long
    outputRow = 2
    
    For i = 0 To worksheetNodes.Length - 1
        Set worksheetNode = worksheetNodes(i)
        Dim worksheetName As String
        worksheetName = worksheetNode.getAttribute("name")
        
        ' <table>ノードを取得
        Dim tableNodes As Object
        Set tableNodes = worksheetNode.selectNodes("table")
        
        If tableNodes.Length > 0 Then
            Dim tableNode As Object
            Set tableNode = tableNodes(0)
            
            ' <view>ノードを取得
            Dim viewNodes As Object
            Set viewNodes = tableNode.selectNodes("view")
            
            If viewNodes.Length > 0 Then
                Dim viewNode As Object
                Set viewNode = viewNodes(0)
                
                ' <filter>ノードをすべて取得
                Dim filterNodes As Object
                Set filterNodes = viewNode.selectNodes("filter")
                
                ' フィルターノードをループ
                Dim filterNode As Object
                For Each filterNode In filterNodes
                    Dim filterColumnAttribute As String
                    filterColumnAttribute = filterNode.getAttribute("column")
                    
                    ' カラム属性がある場合のみ出力
                    If filterColumnAttribute <> "" Then
                        outputSheet.Cells(outputRow, 1).Value = worksheetName
                        outputSheet.Cells(outputRow, 2).Value = FormatColumnName(filterColumnAttribute)
                        outputSheet.Cells(outputRow, 3).Value = "フィルター"
                        outputSheet.Cells(outputRow, 4).Value = ""
                        outputRow = outputRow + 1
                    End If
                Next filterNode
            End If
            
            ' <panes>ノードを取得
            Dim panesNodes As Object
            Set panesNodes = tableNode.selectNodes("panes")
            
            If panesNodes.Length > 0 Then
                Dim panesNode As Object
                Set panesNode = panesNodes(0)
                
                ' <pane>ノードを取得
                Dim paneNodes As Object
                Set paneNodes = panesNode.selectNodes("pane")
                
                If paneNodes.Length > 0 Then
                    Dim paneNode As Object
                    Set paneNode = paneNodes(0)
                    
                    ' <encodings>ノードを取得
                    Dim encodingsNodes As Object
                    Set encodingsNodes = paneNode.selectNodes("encodings")
                    
                    If encodingsNodes.Length > 0 Then
                        Dim encodingsNode As Object
                        Set encodingsNode = encodingsNodes(0)
                        
                        ' <encodings>の子要素をループ
                        Dim childNode As Object
                        For Each childNode In encodingsNode.childNodes
                            ' 要素ノードのみ処理
                            If childNode.nodeType = 1 Then  ' 要素ノード
                                Dim columnAttribute As String
                                columnAttribute = childNode.getAttribute("column")
                                
                                ' カラム属性がある場合のみ出力
                                If columnAttribute <> "" Then
                                    outputSheet.Cells(outputRow, 1).Value = worksheetName
                                    outputSheet.Cells(outputRow, 2).Value = FormatColumnName(columnAttribute)
                                    outputSheet.Cells(outputRow, 3).Value = "マーク"
                                    outputSheet.Cells(outputRow, 4).Value = childNode.nodeName
                                    outputRow = outputRow + 1
                                End If
                            End If
                        Next childNode
                    End If
                End If
            End If
            
            ' <rows>ノードを取得
            Dim rowsNodes As Object
            Set rowsNodes = tableNode.selectNodes("rows")
            
            If rowsNodes.Length > 0 Then
                Dim rowsNode As Object
                Set rowsNode = rowsNodes(0)
                
                ' innerTextを取得
                Dim rowsText As String
                rowsText = rowsNode.Text
                
                ' 「(」と「)」を除去
                rowsText = Replace(Replace(rowsText, "(", ""), ")", "")
                
                ' 「/」で分割
                Dim rowParts() As String
                rowParts = Split(rowsText, "/")
                
                ' 分割した要素数分ループ
                Dim j As Long
                For j = 0 To UBound(rowParts)
                    outputSheet.Cells(outputRow, 1).Value = worksheetName
                    outputSheet.Cells(outputRow, 2).Value = FormatColumnName(rowParts(j))
                    outputSheet.Cells(outputRow, 3).Value = "行シェルフ"
                    outputSheet.Cells(outputRow, 4).Value = ""
                    outputRow = outputRow + 1
                Next j
            End If
            
            ' <cols>ノードを取得
            Dim colsNodes As Object
            Set colsNodes = tableNode.selectNodes("cols")
            
            If colsNodes.Length > 0 Then
                Dim colsNode As Object
                Set colsNode = colsNodes(0)
                
                ' innerTextを取得
                Dim colsText As String
                colsText = colsNode.Text
                
                ' 「(」と「)」を除去
                colsText = Replace(Replace(colsText, "(", ""), ")", "")
                
                ' 「/」で分割
                Dim colParts() As String
                colParts = Split(colsText, "/")
                
                ' 分割した要素数分ループ
                Dim k As Long
                For k = 0 To UBound(colParts)
                    outputSheet.Cells(outputRow, 1).Value = worksheetName
                    outputSheet.Cells(outputRow, 2).Value = FormatColumnName(colParts(k))
                    outputSheet.Cells(outputRow, 3).Value = "列シェルフ"
                    outputSheet.Cells(outputRow, 4).Value = ""
                    outputRow = outputRow + 1
                Next k
            End If
        End If
    Next i
    
    ' <datasources>ノードを取得
    Dim datasourcesNodes As Object
    Set datasourcesNodes = xmlDoc.getElementsByTagName("datasources")
    
    If datasourcesNodes.Length > 0 Then
        Dim datasourcesNode As Object
        Set datasourcesNode = datasourcesNodes(0)
        
        ' <datasource>ノードを取得
        Dim datasourceNodes As Object
        Set datasourceNodes = datasourcesNode.selectNodes("datasource")
        
        ' 出力用のシートを作成（既存の場合は削除）
        On Error Resume Next
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets("データソース解析結果").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        Set outputSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        outputSheet.Name = "データソース解析結果"
        
        ' ヘッダー追加
        outputSheet.Range("A1").Value = "データソース名"
        outputSheet.Range("B1").Value = "カラム名"
        outputSheet.Range("C1").Value = "カラム名（計算フィールド）"
        outputSheet.Range("D1").Value = "計算式"
        
        ' データソース出力の開始行をリセット
        outputRow = 2
        
        ' データソースごとにループ
        Dim datasourceNode As Object
        For Each datasourceNode In datasourceNodes
            Dim datasourceName As String
            
            ' caption属性を取得（なければname属性）
            If datasourceNode.getAttribute("caption") <> "" Then
                datasourceName = Replace(Replace(datasourceNode.getAttribute("caption"), "[", ""), "]", "")
            Else
                datasourceName = Replace(Replace(datasourceNode.getAttribute("name"), "[", ""), "]", "")
            End If
            
            ' <column>ノードを取得
            Dim columnNodes As Object
            Set columnNodes = datasourceNode.selectNodes("column")
            
            ' カラムごとにループ
            Dim columnNode As Object
            For Each columnNode In columnNodes
                Dim columnName As String
                Dim calculatedColumnName As String
                Dim calculationFormula As String
                
                ' caption属性がない場合はname属性を使用
                If columnNode.getAttribute("caption") <> "" Then
                    columnName = columnNode.getAttribute("caption")
                Else
                    columnName = Replace(Replace(columnNode.getAttribute("name"), "[", ""), "]", "")
                End If
                
                ' name属性を確認
                Dim columnNameAttribute As String
                columnNameAttribute = columnNode.getAttribute("name")
                
                ' 初期化
                calculationFormula = ""
                calculatedColumnName = ""
                
                ' 「Calculation_」から始まる場合
                If Left(Replace(Replace(columnNameAttribute, "[", ""), "]", ""), 12) = "Calculation_" Then
                    calculatedColumnName = Replace(Replace(columnNameAttribute, "[", ""), "]", "")
                    
                    ' <calculation>要素のformula属性を取得
                    Dim calculationNode As Object
                    Set calculationNode = columnNode.selectSingleNode("calculation")
                    If Not calculationNode Is Nothing Then
                        calculationFormula = calculationNode.getAttribute("formula")
                    End If
                End If
                
                ' 出力
                outputSheet.Cells(outputRow, 1).Value = datasourceName
                outputSheet.Cells(outputRow, 2).Value = columnName
                outputSheet.Cells(outputRow, 3).Value = calculatedColumnName
                outputSheet.Cells(outputRow, 4).Value = calculationFormula
                outputRow = outputRow + 1
            Next columnNode
        Next datasourceNode
    End If
    
    MsgBox "ワークブックの解析が完了しました。", vbInformation
End Sub