Sub Impor_XML_Data1()
'Code By Al
Application.ScreenUpdating = False
Dim FSO As Object
Dim objFs As Object
Dim objFolder As Object
Dim TargetSheet As Worksheet
Dim ChooseFIle As Variant
Dim TargetSheetName As String
Dim TargetCellAddress As String
Dim NextRow As Long
Dim XName As String
Dim Maps As XmlMaps
Dim XMap As XmlMap
Set objFs = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFs.GetFolder("C:\Users\Owner\Documents\Documents\PITCO\EDI\Assessment\")
Set TargetSheet = ThisWorkbook.Sheets("Sheet1")
Set Maps = ActiveWorkbook.XmlMaps
TargetCellAddress = "A1"
TargetSheet.UsedRange.Clear
NextRow = 1
Worksheets("Sheet1").Select
For Each file In objFolder.Files
XName = file.Name
If objFs.GetExtensionName(file) = "xml" Then
GoTo UDoIt
Else: GoTo GetNext
End If
UDoIt:
Set XMap = "Invoice_Map"
If NextRow = 1 Then
TargetCellAddress = "A1"
Else
'
TargetCellAddress = "A" & NextRow
End If
'
ChooseFIle = "C:\Users\Owner\Documents\Documents\PITCO\EDI\Assessment\" & XName
' If ChooseFIle = vbNullString Then Exit Sub
ThisWorkbook.XmlImport Url:=ChooseFIle, ImportMap:=XMap, Overwrite:=True, Destination:=TargetSheet.Range(TargetCellAddress)
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Set ImportMap = Nothing
GetNext:
Next
' MsgBox "Import Done"
UDone:
Set XMap = Nothing
Application.ScreenUpdating = True
End Sub
Display More
Hello All:
Firstly, I am still learning VBA. I am trying to import many xml files into a single Excel worksheet, and there are subtle difference between the xml files. These are EDI Invoice files from a single EDI provider, and the data schema is pretty much identical, but in some cases some vendors do not include certain variable, such as discounts or notes and the files are excluded from the xml files.
What I would like to do is build a Master schema (what I call "Invoice_Map" in the attached Source Code) and write a VBA script which loops through all the XML files and have the data map to the Excel Worksheet based on the master schema fields, which are available within each of the xml files. I have tried to accomplish this without success.
The best I have been able to accomplish is to get VBA to import all the xml files to a single Excel worksheet an load based on each of the xml maps for each xml file.
Please help or provide some sample code which I can modify for my purpose. I really like this forum and I am grateful for any help.
Many thanks in advance