Firstartemis
New Member
- Joined
- Jun 17, 2015
- Messages
- 10
Good Afternoon,
I'm using the following code to scan a folder of all *.XFDF files and inport the results in excel. The macro works on a single file, but will not work in a loop. I'm open to suggestions. Thanks.
I'm using the following code to scan a folder of all *.XFDF files and inport the results in excel. The macro works on a single file, but will not work in a loop. I'm open to suggestions. Thanks.
Code:
Sub GetFormData()
Dim XDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xEmployee As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.xfdf", vbNormal)
While strFile <> ""
i = i + 1
Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load (strFile)
Set xEmpDetails = XDoc.DocumentElement
Set xEmployee = xEmpDetails.FirstChild
j = 0
For Each xEmployee In xEmpDetails.ChildNodes
For Each xChild In xEmployee.ChildNodes
j = j + 1
WkSht.Cells(i, j) = xChild.Text
Next xChild
Next xEmployee
strFile = Dir()
Wend
Application.ScreenUpdating = True
End Sub
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").Browseforfolder(0, "Choose a folder ", 0) ''
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function