Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Import multiple XML files into Excel

    I have adapted a VBA code to import all the XML files I have on a folder into excel. I have tried to run the code with only two files and it takes about 30 seconds. Several minutes to import less than 20 files.

    I need to import more than 200,000 files. Can you please help me on how to improve my code?

    My code is the following:

    Sub ListFiles()
    Dim ShellApplication As Object
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder( 0, "Please choose a folder", 0, OpenAt)
    Path = ShellApplication.self.Path
    Set ShellApplication = Nothing
    [a3] = "XML"
    [b3] = "Files"
    '--------------------------------------------------------------------
    'DEFAULT PATH FROM HIDDEN SHEET
    Call ListMyFiles(Path, True)
    End Sub


    Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    'With SearchXML
    Application.ScreenUpdating = False


    '--------------------------------------------------------------------
    'FIND XML FILES ONLY, APPLY SEARCH CRIERIA, DISPLAY MATCHES ONLY
    For Each myfile In mySource.Files
    If Right(myfile.Name, 3) = "XML" Or Right(myfile.Name, 3) = "xml" Then 'IS XML?
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

    '-------------------------------------------------------------
    'IMPORT XML FILE
    Application.DisplayAlerts = False
    ActiveWorkbook.XmlImport URL:=mySource & "\" & myfile.Name, _
    ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$" & LastRow + 1)
    Cells(LastRow + 1, 1) = myfile.Name
    '------------------------------------------------------------
    'DELETE MAPS
    maps = ActiveWorkbook.XmlMaps.Count
    For i = 1 To maps
    ActiveWorkbook.XmlMaps(1).Delete
    Next i
    End If
    Next
    If IncludeSubfolders Then 'SEARCH SUBFOLDERS FOR SAME CRITERIA
    For Each MySubFolder In mySource.SubFolders
    Call ListMyFiles(MySubFolder.Path, True)
    Next
    End If
    'End With
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Sheets("XMLData").UsedRange.WrapText = False
    End Sub

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    jfs20,

    I setup an XML folder with one subfolder and populated with a total of 14 XML files. The mapping contains 42 items and each file had 20 records. The code ran in less than 1 second. Your code however. may be running much slower due to the size of the XML files, processor speed, memory, etc.. How many records on average do each of your XML files contain along with the number of items mapped? With 200,000 files, how many files are you able to import before Excel exceeds the 1,048,576 row limit and how are you handling that in your code?

    I would place break points at the beginning of each section (commented section) of the code and run those portions of the code sequentially while noting the time it takes to get through each. Post back where the slow down is and we'll see if we can speed it up at that point.

    Maud

  3. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    jfs20 (2015-12-06),Vandhaal (2016-04-18)

  4. #3
    New Lounger
    Join Date
    Nov 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maudibe,

    The XML files I have are like the one attached. I have tried to run the code on a folder with 14 XML files like that and it took about 5 min, so I did not even try with more than that.

    The true is that I do not need all the information in the files. From each file I only need, for instance, the following:
    1. nif="500134936" (tax ID number) It is on the first line of the XML file, in the node starting by <IES-DA xmlns="http://www.mj.gov.pt/2011/IES-DA"....
    2. All the info contained in the node <Q05A-0521A-divulgacao>, i.e. "Ano Descrição Mercado interno Mercado comunitário Mercado extra-comunitário Total 31-03-2011 Venda de bens 23.671.917,07 ..........:"

    However, the files I have contain all that data that is unnecessary for me and make the import slower. Do you know how can we make it faster? Thanks for your help.

    500134936_1.zip

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    working on it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •