1. 파일 선택창
1편에서는 불러들일 xml파일을 경로를 포함하여 입력했는데,
파일 선택창을 이용해 xml파일을 선택할 수 있도록 하겠습니다.
Option Explicit
Sub XML_Basic()
'xml parsing을 위한 변수 설정
Dim xDoc As MSXML2.DOMDocument60
Dim xNodes As MSXML2.IXMLDOMNodeList, yNodes As MSXML2.IXMLDOMNodeList, zNodes As MSXML2.IXMLDOMNodeList
Dim xNode As MSXML2.IXMLDOMNode, yNode As MSXML2.IXMLDOMNode, zNode As MSXML2.IXMLDOMNode
'Declare a variable as a FileDialog object.
Dim FD As FileDialog
Dim S_filename As String
Dim i As Integer, j As Integer, k As Integer
'A열에서 G열 지우기
Columns("A:G").Clear
' Call ClearImmediateWindow
'xml doc 문서 생성
Set xDoc = New MSXML2.DOMDocument60
'XML File 선택
'Create a FileDialog object as a File Picker dialog box.
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
' .Filters.Clear
.Filters.Add "xml file", "*.xml"
'초기 폴더 지정
' .InitialFileName = "d:\naver blog"
' .Show
' 파일이 선택됐으면 파일명을 변수에 저장하고
If .Show = True Then
' If .SelectedItems.Count > 0 Then
S_filename = .SelectedItems(1)
' MsgBox S_filename
' 취소, 창닫기 버튼을 누르면 종료
Else
End
End If
End With
xDoc.Load (S_filename)
'nodeName을 직접 입력
' Set xNodes = xDoc.SelectNodes("catalog")
'nodeName을 xDoc을 이용해 입력
Set xNodes = xDoc.SelectNodes(xDoc.ChildNodes.Item(1).nodeName)
Sheets("sheet2").Select
i = 1: j = 1: k = 1
' For Each xNode In xNodes
' Debug.Print ("xNode의 노드명: " & xNode.nodeName)
'yNodes 설정
Set yNodes = xNodes.Item(0).ChildNodes
For Each yNode In yNodes
' Debug.Print ("yNode의 값: " & yNode.Text)
'zNodes 설정
Set zNodes = yNode.ChildNodes
For Each zNode In zNodes
' Debug.Print zNode.Text
If i = 1 Then
Select Case k
Case 1:
Cells(1, k) = yNode.Attributes.Item(0).nodeName
Cells(1, k + 1) = zNode.nodeName
k = k + 1
Case 2 To 7:
Cells(1, k) = zNode.nodeName
End Select
If k = 2 Then
Cells(i + 1, k - 1) = yNode.Attributes.Item(0).Text
Cells(i + 1, k) = zNode.Text
Else
Cells(i + 1, k) = zNode.Text
End If
k = k + 1
Else
If j = 1 Then
Cells(i, j) = yNode.Attributes.Item(0).Text
j = j + 1
Cells(i, j) = zNode.Text
Else
Cells(i, j) = zNode.Text
End If
End If
If i > 1 Then j = j + 1
Next
j = 1
If i = 1 Then
i = i + 2
Else
i = i + 1
End If
Next
' Next
Range("a1:g1").HorizontalAlignment = xlCenter
Cells.EntireColumn.AutoFit
End Sub
Sub ClearImmediateWindow()
Application.SendKeys "^g", True
Application.SendKeys "^a", True
Application.SendKeys "{DEL}", False
End Sub
| 'Declare a variable as a FileDialog object. Dim FD As FileDialog 'XML File 선택 'Create a FileDialog object as a File Picker dialog box. Set FD = Application.FileDialog(msoFileDialogFilePicker) With FD ' .Filters.Clear .Filters.Add "xml file", "*.xml" |
가. Application.FileDialog property
o 구문 : expression.FileDialog (fileDialogType)
- fileDialogType : 아래와 같은 네 종류가 있어 FilePicker를 선택했습니다.
msoFileDialogFilePicker. Allows user to select a file.
msoFileDialogFolderPicker. Allows user to select a folder.
msoFileDialogOpen. Allows user to open a file.
msoFileDialogSaveAs. Allows user to save a file.
Set FD = Application.FileDialog(msoFileDialogFilePicker) :
Set을 이용해 파일 다이얼로그 객체를 생성합니다.
o 파일 확장자 제한
.Filters.Add "xml file", "*.xml"
With 문을 사용했으므로 . 앞에는 FD, 다시 말해 Application.FileDialog(msoFileDialogFilePicker) 가 있어야 하는데 생략된 것입니다.
.Filters.Clear 를 사용하지 않고,
.Filters.Add "xml file", "*.xml"
문만 있으면 아래와 같이 모든 파일 아래에 표시되고, 모든 파일이 기본 상태로 표시되므로 불편합니다.
o 초기 폴더 지정
.InitialFileName = "d:\naver blog"
해당 폴더가 열리고, 파일 이름에도 naver blog라고 표시됩니다.
o 파일 선택 또는 취소 버튼 등 클릭시 제어
파일 선택된 것은 .Show 값이 -1이므로, 이 때는 선택된 파일명을 변수 S_filename에 저장하고, 메시지 박스에 표시하도록 했습니다.
취소 또는 창닫기 버튼을 누르면 파일 선택창을 닫을 뿐만 아니라, 아래 프로그램이 실행되지 않도록 end 문을 넣었습니다.
o 실행 결과
initialFileName을 지정하지 않으면 맨 마지막에 열렸던 폴더가 열리고, 지정하면 그 폴더가 열립니다.
book.xml을 클릭하면 열기 버튼이 확인 버튼으로 변경됩니다.
확인 버튼을 누르면, 드라이브와 폴더명을 포함해서 파일명이 메시지 박스에 표시됩니다.
그리고, 확인버튼을 누르면 1편에서와 같이 직접 실행 창(보기 메뉴- 직접실행창 또는 Ctrl + G 클릭 )에 debug. print로 지정한 xml 파일의 내용이 표시됩니다.
이제 msgbox 줄은 필요없으므로 주석 처리하면 됩니다. 해당 줄 맨 앞에 '(작은 따옴표) 를 추가하던가, '주석 블록 설정' 아이콘을 누르면 됩니다.
o .show = -1 대신 .SelectedItems.Count > 0 사용
-1 대신 True로 바꿔도 되고,
이해하기 쉽게 .SelectedItems.Count를 사용하려면
주석으로 처리된
.show 와 .SelectedItems.Count 가 있는 줄의 주석을 해제하고,
.show = -1 이 있는 줄은 주석 처리하고 실행하면 됩니다.
주석 해제는 '주석 블록 설정' 오른쪽의 '주석 블록 해제' 아이콘을 누르면 됩니다.
2. For Each 반복문
가. For Each 반복문
Set xNodes 아래에 있던 내용은 모두 지우고 아래 내용을 붙여 넣어 아래와 같이 만듭니다.
'nodeName을 xDoc을 이용해 입력
Set xNodes = xDoc.SelectNodes(xDoc.ChildNodes.Item(1).nodeName)
For Each xNode In xNodes
Debug.Print ("xNode의 노드명: " & xNode.nodeName)
'yNodes 설정
Set yNodes = xNode.ChildNodes
For Each yNode In yNodes
Debug.Print ("yNode의 값: " & yNode.Text)
'zNodes 설정
Set zNodes = yNode.ChildNodes
For Each zNode In zNodes
Debug.Print "zNode의 값: " & zNode.nodeName
Debug.Print "zNode의 값: " & zNode.Text
Next
Next
NextFor Each xNode In xNodes :
xNodes 리스트의 요소를 하나씩 실행한다는 의미입니다.
book.xml에서 author는 catalog > book 아래에 있어 3단계 구조이므로 For Each 반복문도 3번 사용했습니다.
그리고, F5키 또는 매크로 실행 아이콘을 눌러 실행하고,
book.xml을 선택한 후 확인버튼을 누릅니다.
그리고, 직접 실행창을 보면
xNode의 노드명, yNode의 값이 보이고, yNode의 값을 author, title 등으로 구분해서 zNode의 값으로 표시하고, yNode와 zNode의 값을 반복 출력합니다.
그러나, xNodes의 갯수는 catalog 하나이므로
첫번째 For Each 반복문은 주석처리하고,
yNodes를 xNodes.Item(0).ChildNodes 로 설정하면 2번에 처리할 수 있습니다.
나. 엑셀에 쓰기
book의 id와 author 등을 셀에 입력하려면
Cells(1, k) = yNode.Attributes.Item(0).nodeName
식으로 셀 주소 = 값 으로 입력하면 됩니다.
Sheets("sheet2").Select
i = 1: j = 1: k = 1
' For Each xNode In xNodes
' Debug.Print ("xNode의 노드명: " & xNode.nodeName)
'yNodes 설정
Set yNodes = xNodes.Item(0).ChildNodes
For Each yNode In yNodes
' Debug.Print ("yNode의 값: " & yNode.Text)
'zNodes 설정
Set zNodes = yNode.ChildNodes
For Each zNode In zNodes
' Debug.Print zNode.Text
If i = 1 Then
Select Case k
Case 1:
Cells(1, k) = yNode.Attributes.Item(0).nodeName
Cells(1, k + 1) = zNode.nodeName
k = k + 1
Case 2 To 7:
Cells(1, k) = zNode.nodeName
End Select
If k = 2 Then
Cells(i + 1, k - 1) = yNode.Attributes.Item(0).Text
Cells(i + 1, k) = zNode.Text
Else
Cells(i + 1, k) = zNode.Text
End If
k = k + 1
Else
If j = 1 Then
Cells(i, j) = yNode.Attributes.Item(0).Text
j = j + 1
Cells(i, j) = zNode.Text
Else
Cells(i, j) = zNode.Text
End If
End If
If i > 1 Then j = j + 1
Next
j = 1
If i = 1 Then
i = i + 2
Else
i = i + 1
End If
Next
' Nextbook의 id는 yNode에 있고, author 등은 zNode에 있어 셀의 행과 열을 넣는 것이 좀 복잡하네요.
보기 좋게 1행은 가운데 정렬하고, 열너비를 자동으로 맞추는 구문을 추가합니다.
Range("a1:g1").HorizontalAlignment = xlCenter : 수평 가운데 정렬
Cells.EntireColumn.AutoFit : 모든 열 너비 자동 맞춤
최종 결과는 아래와 같습니다.
