- 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