XMLをエクセルに取り込むマクロその２

質問

以下のtest2.xmlを

<?xml version="1.0" encoding="UTF-8" ?>
<McXMLRoot>
<McXMLData>
<McXMLPageInfo>
<page>1</page>
<overLay></overLay>
<partition>PAGE</partition>
</McXMLPageInfo>
<McXMLPageData>
<現頁>
<value>0001</value>
</現頁>
<作成日付>
<value>平成21年 6月 1日現在</value>
</作成日付>
<Group0001>
<氏名>
<value>あああ</value>
</氏名>
<生年月日>
<value>昭和48年 2月21日</value>
</生年月日>
</Group0001>
<Group0002>
</Group0002>
<Group0003>
</Group0003>
</McXMLPageData>
<McXMLPageInfo>
<page>2</page>
<overLay></overLay>
<partition>PAGE</partition>
</McXMLPageInfo>
<McXMLPageData>
<現頁>
<value>0004</value>
</現頁>
<作成日付>
<value>平成21年 6月 1日現在</value>
</作成日付>
<Group0001>
<氏名>
<value>いいい</value>
</氏名>
<生年月日>
<value>昭和55年 12月5日</value>
</生年月日>
</Group0001>
<Group0002>
<法人名>
<value>ＡＡＡ株式会社</value>
</法人名>
<住所>
<value>ＡＡ市1丁目1番地</value>
</住所>
</Group0002>
<Group0003>
<支店名>
<value>ＢＢＢ営業所</value>
</支店名>
</Group0003>
</McXMLPageData>
</McXMLData>
</McXMLRoot>

を読むマクロ↓

Public Const XmlPass = "D:\temp\test2.xml"
Public y As Integer
Public ctr As Long
Public Sub Auto_Open()
'On Error Resume Next

Workbooks.OpenXML Filename:= _
XmlPass _
, LoadOption:=xlXmlLoadImportToList
Range("A1").Select

Call RetsuSakujo

Call parseXML

End Sub

Private Sub RetsuSakujo()
ctr = 1
Do Until "" = Trim(Worksheets("sheet1").Cells(1, ctr))
If "eform" = Trim(Worksheets("sheet1").Cells(1, ctr)) Or _
"page" = Trim(Worksheets("sheet1").Cells(1, ctr)) Or _
"overLay" = Trim(Worksheets("sheet1").Cells(1, ctr)) Or _
"partition" = Trim(Worksheets("sheet1").Cells(1, ctr)) Then

Worksheets("sheet1").Columns(ctr).EntireColumn.Delete
ctr = 0
End If
ctr = ctr + 1
Loop

End Sub

Private Sub parseXML()

Dim objDOM, rtResult
y = 1

Set objDOM = CreateObject("MSXML2.DOMDocument")
rtResult = objDOM.Load(XmlPass)
If rtResult = True Then
Call setTitle(objDOM.childNodes)
End If
Set objDOM = Nothing

End Sub

Sub setTitle(objNode)
Dim obj

For Each obj In objNode
If obj.hasChildNodes Then
If obj.parentNode.nodeName <> "McXMLRoot" And _
obj.parentNode.nodeName <> "McXMLData" And _
obj.parentNode.nodeName <> "McXMLPageData" And _
obj.parentNode.nodeName <> "McXMLPageInfo" _
Then
If SearchChild(obj.childNodes) = False Then
If y >= ctr Then
Exit Sub
End If
Cells(1, y).Value = obj.parentNode.nodeName
y = y + 1
End If
End If
Call setTitle(obj.childNodes)
End If
Next

End Sub

Private Function SearchChild(objNode) As Boolean
Dim obj

For Each obj In objNode

If obj.hasChildNodes Then
SearchChild = True
Else
SearchChild = False
End If
Next

End Function

を作成してエクセルマクロを試したところ、
タイトルが
現頁,作成日付,氏名,生年月日,現頁2,作成日付2,氏名2,生年月日2,法人名
となります。が、本当は
現頁,作成日付,氏名,生年月日,法人名,住所,口座番号,口座名義人,支店名
としたいのです。(エクセルでXMLソースを表示した時の順序)

なにかよい方法があれば教えてください。
よろしくお願いします。

回答No.1

yyr446

提示のコードだと、各要素(node)を階層関係に関係なく順次に読み込んで、IF文で要素名(nodeName)該当する要素名のみ列記しているからそうなるのです。そもそもXMLをシートにインポートで貼り付け、タイトル行のみ
消しこんで、別途XMLアクセスしてセットしているから、ややこしくなり、汎用性をそこねているのです。どちらかのやり方に統一しましょう。
まず、
－XMLの構造を理解して下さい。
－次にDOM(ドキュメントオブジェクトモデル)のアクセス方法も理解して下さい。

XMLの構造はスキーマー定義(XSD)に記述するのですが、無い場合は自分で構造を把握して下さい。提示のXMLの構造だと
<McXMLRoot>要素がルート（一番上の親要素）となり、<McXMLData>要素があり、
<McXMLData>要素の下に<McXMLPageInfo>要素と<McXMLPageData>要素が
ページ数分あります。
<McXMLPageData>要素の下に<現頁>、<作成日付>、<Group0001>、<Group0002>、<Group0003>の要素が一つづつあります。
<Group0001>の要素の下が<氏名>要素と<生年月日>要素で、
<Group0002>の要素の下が<法人名>要素と<住所>要素で、
<Group0003>の要素の下が<支店名>要素
となっています。つまり
<McXMLData>
　<McXMLPageInfo>
　　<page>
　　<overLay>
　　<partition>
　<McXMLPageData>
　　<現頁>
　　<作成日付>
　　<Group0001>
　　　<氏名>
　　　<生年月日>
　　<Group0002>
　　　<法人名>
　　　<住所>
　　<Group0003>
　　　<支店名>
の階層構造で、<McXMLPageData>要素が複数繰り返されてます。
この構造に沿って、要素の名前を取り出し1行目にセットし、中身のデータは、変数(配列)に保持させ、<McXMLPageData>要素毎に行ブレークして各列にセットするようにした方がよいと思います。

ＸＭＬの理解については
http://www6.airnet.ne.jp/manyo/xml/
あたりがわかりやすいです。
VBAでのXML解析は参考サイトがなかなかありませんが、
http://msdn.microsoft.com/ja-jp/library/aa468547.aspx
とか
http://msdn.microsoft.com/ja-jp/library/ms256471(VS.80).aspx
とか
http://www.kanaya440.com/contents/tips/vbs/003.html
http://blog.goo.ne.jp/xmldtp/e/c2d0c185fbd25cbae4a59adff625ce43
ですかね。