Results 1 to 2 of 2
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Importing multiple XML files into Excel

    This is a continuation of an old thread that was too old to add additional posts so a member PM'd me instead with an issue he was experiencing and referred me back to it.

    http://windowssecrets.com/forums/showthread.php/157369-Importing-multiple-XML-files-into-Excel


    Little did I realized that several other members had posed questions that I was not aware of. I will try, and hope other members will help out, to provide solutions to their issues as well.

    First, let me say that .xml files are influenced by the styles and maps that provide structure to the tags. There are excellent questions posed but without samples to analyze, it is unlikely to provide some working solutions.

    I am using the script, but having a problem. I am importing xml files that comes from different adobe forms most of the forms works perfect; but one of the forms puts a line with the "headers" first, and then repeats the data 3 times
    crj9 and PaulFaoro MWas: The issure you are seeing with repetitive lines is becaused your .xml files are mapped as tables each with their own header. To remove or hide the header for each of the files except for the first, the code must be adjusted similar to the following with the changes highlighted in blue:

    Code:
    Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    '--------------------------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim SelectedCell As Range
        Dim TableName As String
        Dim ActiveTable As ListObject
        Set MyObject = New Scripting.FileSystemObject
        Set mySource = MyObject.GetFolder(mySourcePath)
        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?
                NextRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                '-------------------------------------------------------------
                'IMPORT XML FILE
                ActiveWorkbook.XmlImport URL:=mySource & "\" & myfile.Name, _
                    ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$" & NextRow + 1)
                Lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                '-------------------------------------------------------------
                'GET NAME OF TABLE
                Cells(NextRow + 1, 1).Select
                Set SelectedCell = ActiveCell
                TableName = SelectedCell.ListObject.Name
                Set ActiveTable = ActiveSheet.ListObjects(TableName)
                 '-------------------------------------------------------------
                'SHOW ONLY FIRST HEADER
                If NextRow = 3 Then
                    ActiveTable.ShowHeaders = True
                    ActiveTable.ShowAutoFilter = False
                Else:
                    ActiveTable.ShowHeaders = False
                End If
                '------------------------------------------------------------
                'DELETE MAPS
                maps = ActiveWorkbook.XmlMaps.Count
                For I = 1 To maps
                    ActiveWorkbook.XmlMaps(1).Delete
                Next I
            End If
        Next
    '--------------------------------------------------------------------
    'SEARCH SUBFOLDERS FOR SAME CRITERIA
        If IncludeSubfolders Then
            For Each MySubFolder In mySource.SubFolders
                Call ListMyFiles(MySubFolder.Path, True)
            Next
        End If
        Application.ScreenUpdating = True
    End Sub
    If you have any additional questions, please post samples so we can help you.

    Regards,
    Maud
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Maud,

    In my situation I want to import XML files with file browse option (Select file to import) once 1st file is imported it will ask for, Do you want to import another file? if selected yes then it will open browse option for selecting 2nd file, then import 2nd file and delete the header as the header is present from the first file, then option for 3rd import will pop up if terminated then macro for convert Column A will execute.

    Convert (Sheet Student) Column A to House sheet and then create a summary of group on sheet3

    For more i have attached the sample .xml files along with workbook.

    Thanks in advance.
    Attached Files Attached Files

Posting Permissions

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