How to import multiple xml file into excel

Anonymous
2012-09-13T00:07:12+00:00

I have a lot of xml files which need to be imported into single excel file. I can able to import one by one but cannot select all at a time to import.

Is it possible to do through batch process or any other process?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

66 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2013-11-19T21:58:09+00:00

    I was able to find the solution. I made it reference the second row in the XML documents due to the first row just containing /my:my:Fields

    Thanks for everything,

    TPearl

    Sub Convert_XML_Files()

    On Error GoTo errh

    Const N As Long = 3  '<<< number of headers/columns in row 1,change

    Dim myWB As Workbook, WB As Workbook

    Set myWB = ThisWorkbook

    Application.ScreenUpdating = False

    Dim myPath

    myPath = "C:\Users\tp62215\Desktop\New folder" '<<< change path

    Dim myFile

    myFile = Dir(myPath & "*.xml")

    Dim t As Long, r As Long, c As Long, x As Long

    t = 2

    Dim cH As Range

    Do While myFile <> ""

    Set WB = Workbooks.OpenXML(Filename:=myPath & myFile)

    r = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    c = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    For x = 1 To N

    Set cH = WB.Sheets(1).Range(Cells(1, 1), Cells(2, c)).Find(What:=myWB.Sheets(1).Cells(1, x), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) '.Column

    If Not cH Is Nothing Then

    WB.Sheets(1).Range(Cells(2, cH.Column), Cells(r, cH.Column)).Copy myWB.Sheets(1).Cells(t, x)

    End If

    Next

    WB.Close False

    t = myWB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    myFile = Dir()

    Loop

    Application.ScreenUpdating = True

    myWB.Save

    Exit Sub

    errh:

    MsgBox "no files xml"

    End Sub

    Was this answer helpful?

    0 comments