How to import multiple xml file into excel

JIMZCE,

I have added some line in macro for you. I am not sure how you want your data to be, but as per standard format I have added some lines.

In case you have some different requirement you could let me know with some example and I will help you to automate the whole process.

Example of xml data :

<name>Ebcd</name>

<address>Indiana</address>

<phoneno>111-111-1110</phoneno>

<name>Efgh</name>

<address>Kansas</address>

<phoneno>989-999-9990</phoneno>

Example of data after execution of macro:

Name	Address	Phone no
Ebcd	Indiana	111-111-1110
Efgh	Kansas	989-999-9990
Mnop	Utah	777-777-7770

Note: Add "Name" where "********"

It should be in the quot Ex : "Name"

So that it will filter with "Name" heading. if your heading name is "ID" then add "ID" if "lastModified"

add "lastModified".

don't worry it just for formatting after execution it will give you complete data then you can apply your own filters.

I hope this will help you.

• I just wanted to suggest you test this macro in a test file, before use it in production.

---

Option Explicit

Sub GetLmsDtata()

On Error Resume Next

Dim xRow As Long

Dim path As String

Dim strSheetName As String

Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = "C:\Users%username%"

With Application.FileDialog(msoFileDialogFolderPicker)

.InitialFileName = Application.DefaultFilePath & ""

.Title = "Please select a folder contains xml Files"

.InitialFileName = InitialFoldr$

.Show

If .SelectedItems.Count <> 0 Then

xDirect$ = .SelectedItems(1) & ""

xFname$ = Dir(xDirect$ & "*.xml")

Do While xFname$ <> ""

path = (xDirect$ & xFname$)

ActiveWorkbook.XmlImport URL:= _

      path, _

     ImportMap:=Nothing, Overwrite:=True, Destination:=ActiveCell

     Selection.End(xlDown).Offset(1, 0).Select

xFname$ = Dir()

Loop

End If

End With

Application.ScreenUpdating = False

ActiveSheet.UsedRange.Select

Selection.Copy

Worksheets(2).Activate

ActiveSheet.Range("A1").Select

Selection.PasteSpecial Paste:=xlValues, _

Operation:=xlNone, SkipBlanks:=False, _

Transpose:=False

Worksheets(2).Activate

ActiveSheet.Range("A1").Select

ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy Destination:=Sheet3.Range("A1")

ActiveSheet.UsedRange.Select

Selection.AutoFilter

    ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:= _

        "**********"

      ActiveSheet.UsedRange.Select

      Selection.Delete Shift:=xlUp

      Selection.Copy Destination:=Sheet3.Range("A2")

      Worksheets(3).Activate

      ActiveSheet.Range("A1").Select

      ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select

    Selection.Font.Bold = True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent1

        .TintAndShade = -0.249977111117893

        .PatternTintAndShade = 0

    End With

    With Selection.Font

        .ThemeColor = xlThemeColorDark1

        .TintAndShade = 0

    End With

    Worksheets(2).Activate

    Cells.Select

    Selection.Delete Shift:=xlUp

    Worksheets(1).Activate

    Cells.Select

    Selection.Delete Shift:=xlUp

    Worksheets(3).Activate

    ActiveSheet.UsedRange.Copy Destination:=Sheet1.Range("A1")

    ActiveSheet.Cells.Select

    Selection.Delete Shift:=xlUp

    Worksheets(1).Activate

        Cells.Select

    Selection.ColumnWidth = 10.14

    Selection.ColumnWidth = 13.29

    Application.ScreenUpdating = True

    ActiveSheet.Range("A1").Select

    MsgBox ("Completed")

End Sub

---

yes i had to reinstall office after my laptop suffered a virus attack.

the macro worked well. i found some issue in the data format. i corrected it manually...

Thanks you so much for your help!!! surathsarkar

Hello JIMZCE,

Thank you for your reply.

Here is  some step you can perform. please let me know the result.

if u are using Excel 2007 or 2010

Step : Click on Data from ribbon > From other sources > from XML data import > navigate to your .xml file and click on open. >ok >ok

this is the manual way to import a .xml file.

if it work for you then the macro should work.

please reply me with results. :)

Thanks a lot surath!!! I still have a problem !!!!!

i typed the code in an excel macro enabled spread sheet, in an inserted  module in sheet1 (developer visual basic)....

after i run the code i found the sheet1 to contain several copies of records of the one xml file only in cells (data replicated). i had four xml files in the folder but actually found more replicated cells.... of one xml file.

This is what my xml file looks like......

<?xml version="1.0" encoding="UTF-8"?><project>

<id>4cbcaeaa-398b-4ccf-8a8a-40ad4eae01b9-1</id>

<lastModified>2014-02-03 06:24:44</lastModified>

<label>motorvehicleacc</label>

<participantsIds>

<string>cfdabbd1-860e-423b-bcf3-1b05b856cf0d</string>

</participantsIds>

<adminsIds>

<string>cfdabbd1-860e-423b-bcf3-1b05b856cf0d</string>

</adminsIds>

<productOwnersIds>

<string>cfdabbd1-860e-423b-bcf3-1b05b856cf0d</string>

</productOwnersIds>

<scrumMastersIds>

<string>cfdabbd1-860e-423b-bcf3-1b05b856cf0d</string>

</scrumMastersIds>

<teamMembersIds>

<string>cfdabbd1-860e-423b-bcf3-1b05b856cf0d</string>

</teamMembersIds>

<currentSprintId>b230844f-f0ee-4927-9406-43ce8d6932cd</currentSprintId>

<nextSprintId>7f228b2f-b620-43a4-a197-501abbecfa33</nextSprintId>

<requirementsOrderIds/>

<urgentIssuesOrderIds/>

<lastSprintNumber>2</lastSprintNumber>

<lastTaskNumber>0</lastTaskNumber>

<lastRequirementNumber>0</lastRequirementNumber>

<lastQualityNumber>0</lastQualityNumber>

<lastRiskNumber>0</lastRiskNumber>

<lastIssueNumber>1</lastIssueNumber>

<lastImpedimentNumber>0</lastImpedimentNumber>

<lastFileNumber>0</lastFileNumber>

<lastSubjectNumber>0</lastSubjectNumber>

<lastEventNumber>0</lastEventNumber>

<lastReleaseNumber>0</lastReleaseNumber>

<lastBlogEntryNumber>0</lastBlogEntryNumber>

<punishmentUnit>EUR</punishmentUnit>

<punishmentFactor>1</punishmentFactor>

<autoUpdateHomepage>false</autoUpdateHomepage>

<issueReplyTemplate>Hello ${issuer.name},

thank you very much for your feedback.

Your issue is now known as ${issue.reference}. You can view it on our homepage: ${homepage.url}/${issue.reference}.html

</issueReplyTemplate>

<subscriberNotificationTemplate>Hello,

there is news on an entity you are subscribed to:

${entity.reference} ${entity.label}

${homepage.url}/${entity.reference}.html

${change.message}

---

Unsubscribe from ${entity.reference}: ${unsubscribe.url}

Unsubscribe from all entities: ${unsubscribeall.url}

</subscriberNotificationTemplate>

<lastOpenedDateAndTime>2014-02-03 06:24:44</lastOpenedDateAndTime>

<freeDays>0</freeDays>

<autoCreateTasksFromQualities>false</autoCreateTasksFromQualities>

</project>

Thanks once again