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. Anonymous
    2015-03-26T21:46:35+00:00

    Great!  I got 2/3 through my 800 or so .xml files.  They all populated in different tabs and the mapping was preserved in each!

    Then I got another error.  It turns out that some of my files have the exact same name up to and beyond 30 characters.  So there error told me that I can not have two sheets with the same name.  How can we modify this script so that every sheet has a name that is less that 31 characters and yet has a completely unique name?  31 is actually the limit not 30. 

    Or even better would be how can we convince excel to let us have names that are longer then 31 characters?  It would be great to be able to preserve the whole name??

    Thank you,

    Jonathan

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-03-26T20:00:57+00:00

    add the bold line

    .................

    s = sFile

    s = Replace(s, ".xml", "")

    If Len(s) > 30 Then s = Left(s, 30)

    For Each ws In ThisWorkbook.Sheets

    If ws.Name = s Then ws.Delete

    Next

    ...............

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-03-26T19:10:14+00:00

    We are so close.  It populated the first two .xml file in separate tabs with all of the mapping!

    then it stopped and showed this error

    I think this is because the third .xml file has a very long name and the sheet labels can only have 31 characters.  How can we change the code so that it only asks for the first 30 characters on the file name?

    So close

    Thank you for your help

    Jonathan

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-03-26T17:19:37+00:00

    Okay,

    in order to avoid this problem,

    try this code

    Sub Load_ALL_XML_02()

    'Mar 26, 2015

    'step1: delete all old maps

    Dim xm As XMLMap

    For Each xm In ActiveWorkbook.XmlMaps

    xm.Delete

    Next

    'step2 load all xml in separate shts

    Dim sPath

    sPath = "c:\folder XML" '<< change path

    Dim sFile

    sFile = Dir(sPath & "*.xml")

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Dim ws: Dim s

    Do While sFile <> ""

    s = sFile

    s = Replace(s, ".xml", "")

    For Each ws In ThisWorkbook.Sheets

    If ws.Name = s Then ws.Delete

    Next

    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

    ActiveSheet.Name = s

    ActiveWorkbook.XmlImport URL:=sPath & sFile, ImportMap:=Nothing, Overwrite:=True, Destination:=[A1]

    sFile = Dir()

    Loop

    Application.DisplayAlerts = True

    Application.ScreenUpdating = True

    ActiveWorkbook.Save

    End Sub

    Was this answer helpful?

    0 comments