Results 1 to 1 of 1

Scrit for Importing multiple XML files into Excel - Small bug, need to order elements

Threaded View

Tamam Scrit for Importing multiple... 09-24-2013, 03:48 AM
  1. #1
    Registered User
    Join Date
    09-24-2013
    Location
    Bangkok
    MS-Off Ver
    Excel 2010
    Posts
    1

    Exclamation Scrit for Importing multiple XML files into Excel - Small bug, need to order elements

    I have some code developed by a friend which imports multiple XML files into Excel (very useful tool!). However the order of the elements in the XML files is imported in random order, is there a small line of code that can be inserted to fix this?

    Here is the code:

    Sub ReadXML()
        Dim strFile As String
        MsgBox "I'll start reading please don't touch the computer"
        Dim strPath As String
        Dim colFiles As New Collection
        Dim i As Integer
        Dim wb As Workbook
        strPath = ActiveSheet.Range("C2")
        strFile = Dir(strPath)
        While strFile <> ""
            colFiles.Add strFile
            strFile = Dir
        Wend
        If colFiles.Count > 0 Then
            For i = 1 To colFiles.Count
                 Application.ScreenUpdating = False
                 Application.DisplayAlerts = False
                 strTargetFile = strPath & colFiles(i)
                 
                 Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadOpenXml)
                 Application.DisplayAlerts = True
            
                 wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("XMLDATA").Range("A" & i * 3 + 1)
                 ThisWorkbook.Sheets("XMLDATA").Rows(i * 3 + 3).Copy ThisWorkbook.Sheets("Result").Range("A" & i + 2)
                 wb.Close False
                 Application.ScreenUpdating = True
                 ActiveSheet.Range("P2") = i
            Next i
            ThisWorkbook.Sheets("XMLDATA").Rows(4).Copy ThisWorkbook.Sheets("Result").Rows(1)
            ThisWorkbook.Sheets("XMLDATA").Rows(5).Copy ThisWorkbook.Sheets("Result").Rows(2)
            MsgBox "I'm Done!"
        End If
    End Sub
    Moderator Note:

    Pls use code tags around your code next time as per forum rules.
    Last edited by Fotis1991; 09-24-2013 at 03:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Importing Multiple txt files in excel
    By celiojr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2013, 11:00 AM
  2. Replies: 0
    Last Post: 11-27-2012, 01:43 PM
  3. Importing multiple excel files into 1
    By vascobmcastro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-25-2010, 01:29 PM
  4. Importing multiple Text files into Excel
    By cerezoluis@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2006, 08:40 AM
  5. [SOLVED] Importing from multiple Excel files
    By Tired of wasting time in forum Excel General
    Replies: 2
    Last Post: 09-21-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1