【Excel/VBA】既存のXMLファイル要素の取得・編集・保存方法

スポンサーリンク
Excel/VBA

JSONに取って代わりつつあるものの、未だ根強く残り続けるXMLファイル。本日はVBAでXMLファイルを開き、中身を書き換えて名前を付けて保存を行うマクロを作ったので備忘録。

スポンサーリンク

MSXMLを利用する


まず今回XMLを操作するにあたって、Microsoft XML v6.0の参照設定を追加を行う。

で、これでMSXML2.DOMDocumentが使えるようになる…ってのはWin7迄のお話で、Win8.1以降はMSXML2.DOMDocument60とバージョン指定をして宣言しないとエラーになる模様。(無駄に嵌った…)
※Microsoft XML v3.0ならセーフらしい

読み込むXMLデータ


今回のマクロ作成の経緯としては、IP電話機Grandstream(GXP16系)のプロビジョニングファイルをエクセルから一括で作りたかったのです。(最近は画面チラ見しただけでファームのバージョンが分かる位Grandstreamいじってる…)


実際にいじる元のXMLファイルの中身はこんな感じ↓

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Grandstream XML Provisioning Configuration -->
<gs_provision version="1">
<mac>000b82xxxxxx</mac>
  <config version="1">
    <!-- # Generated on Tue Feb 4 14:49:50 TZZ 2020 by GXP1625 [00:0B:82:XX:XX:XX] -->
    <!-- # boot 1.0.4.5; core 1.0.4.101; base 1.0.4.140; prog 1.0.4.152 -->
    <P2981>0</P2981>
    <P26050>0</P26050>
    <P26051>60</P26051>
　|-----
　| 省略
　|-----
    <P2339>0</P2339>
    <!-- # End of exported configuration -->
  </config>
</gs_provision>

このP値それぞれにSIPやらSRVやらプレフィックス設定が入ってるので、シート側に書き換えたい値を持たせてそれらを元にごにょごにょして行きます。

シート側のイメージはこんな感じ↓

プロビジョニング設定しとくと、いちいち1台1台ブラウザでアクセスして設定しなくても、 rebootさせれば勝手にファイルを読み込んで設定が書き変わってくれるので超絶便利なので、Grandstream愛好家は是非使うべし。

プロビジョニング用XML作成マクロ


下記コピペでOK

Option Explicit
CST_COL_SIP = 3
CST_COL_SRV = 6
CST_COL_PREFIX = 9

Private Sub CommandButton_Click()

    Dim strMacAdress As String  ' MACアドレス
    Dim strModelName As String  ' 機種名
  
    Dim nLine As Integer           ' 行
    Dim nColumn As Integer         ' 列
    
    For nLine = 3 To 100
        ' MACアドレス設定
        strMacAdress = Cells(nLine, 1).Value
        
        ' MACアドレスが設定されている場合
        If strMacAdress <> "" Then
            ' 機種名取得
            strModelName = Cells(nLine, 2).Value
            ' 機種毎の雛形XMLを開く
            Dim XMLDocument As MSXML2.DOMDocument
            Set XMLDocument = New MSXML2.DOMDocument
            ' 同期処理 ※機種毎にフォルダ分けして雛形置いてます
            XMLDocument.Load (ActiveWorkbook.Path & "\" & strModelName & "\雛形.xml")
            XMLDocument.async = False
            ' MACアドレス
            XMLDocument.SelectNodes("gs_provision/mac").Item(0).Text = strMacAdress
            
            ' SIP1～SIP3
            For nColumn = 0 To CST_COL_SIP
               ' SIPが設定されている場合
               If Cells(nLine, CST_COL_SIP + nColumn).Value <> "" Then
                   ' 設定するP値はGXP1620/1625/1630共通
                   Select Case nColumn
                       ' SIP1
                       Case 0
                         ' SIP1 SIP
                         XMLDocument.SelectNodes("gs_provision/config/P3").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P35").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P36").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P270").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         ' SIP1 SRV
                         XMLDocument.SelectNodes("gs_provision/config/P47").Item(0).Text = Cells(nLine, CST_COL_SRV + nColumn)
                         ' SIP1 PREFIX
                         XMLDocument.SelectNodes("gs_provision/config/P290").Item(0).Text = "{ <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">xxxxxxxxxx+ | <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">1xx | 5x | 7xxx | 8xxx | 9xxx }"
                       ' SIP2
                       Case 2
                         ' SIP2 SIP
                         XMLDocument.SelectNodes("gs_provision/config/P404").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P405").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P407").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P417").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         ' SIP2 SRV
                         XMLDocument.SelectNodes("gs_provision/config/P402").Item(0).Text = Cells(nLine, CST_COL_SRV + nColumn)
                         ' SIP2 PREFIX
                         XMLDocument.SelectNodes("gs_provision/config/P459").Item(0).Text = "{ <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">xxxxxxxxxx+ | <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">1xx | 5x | 7xxx | 8xxx | 9xxx }"
                       ' SIP3
                       Case 3
                         ' SIP3 Extension
                         XMLDocument.SelectNodes("gs_provision/config/P504").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P505").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P507").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         XMLDocument.SelectNodes("gs_provision/config/P517").Item(0).Text = Cells(nLine, CST_COL_SIP + nColumn)
                         ' SIP3 SRV
                         XMLDocument.SelectNodes("gs_provision/config/P502").Item(0).Text = Cells(nLine, CST_COL_SRV + nColumn)
                         ' SIP3 PREFIX
                         XMLDocument.SelectNodes("gs_provision/config/P559").Item(0).Text = "{ <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">xxxxxxxxxx+ | <=" & Cells(nLine, CST_COL_PREFIX + nColumn) & ">1xx | 5x | 7xxx | 8xxx | 9xxx }"
                   End Select
               End If
            Next
            ' 保存
            XMLDocument.Save (ActiveWorkbook.Path & "\" & strModelName & "\cfg" & strMacAdress & ".xml")
        End If
    Next
    
    ' 完了通知
    MsgBox "コンフィグファイル作成" & vbCrLf & "および更新が完了しました。"
    
End Sub

シートに配置したボタンによりキック。やってる事は非常に単純。
Loadで開き、P値で直接エレメント指定SelectNodesで取得＆書き換え、Saveで保存するだけ！

是非参考にしてみてください~(ᵔᴥᵔ)

スポンサーリンク

コメント