Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    batch xml 2 xls (2003)

    I need to convert all "xml" files in a specific folder to " xls"
    Doing it manually it works fine first question asked is "how to open list?"
    I choose first option "as xml list"
    Second popup "specified xml source does not have schema Excel will create..........."
    I press ok
    The resulting file saved as xls is what I require
    Many thnx
    Smbs

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: batch xml 2 xls (2003)

    If you run this macro, using the path of one of your XML files, does it open OK?
    <pre>Sub TestXML()
    Workbooks.OpenXML "C:blahtest.xml"
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch xml 2 xls (2003)

    Yes it runs stating read only mode. The opened excel file is not in correct format--- no pivit tables
    many thanx
    SMBS

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: batch xml 2 xls (2003)

    Ah, they added an option in 2003 - does it work if you change the code to:
    <pre>Sub OpenXML()
    Workbooks.OpenXML _
    Filename:="C:blahtest.xml", _
    LoadOption:=xlXmlLoadImportToList
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch xml 2 xls (2003)

    Yeah that opens just fine!
    Smbs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: batch xml 2 xls (2003)

    I'm not in front of a machine with 2003 to test this, but see if it works for you, replacing the path as necessary:
    <pre>Sub ConvertXMLFiles()
    Dim fs As FileSearch
    Dim lngCounter As Long
    Dim wbk As Workbook
    Dim strParentFolder As String

    Application.ScreenUpdating = False

    ' Change path to XML files as needed
    strParentFolder = "C:blah"

    Set fs = Application.FileSearch
    With fs
    .NewSearch
    .LookIn = strParentFolder
    .SearchSubFolders = True
    .FileName = "*.xml"
    .MatchTextExactly = True
    .FileType = msoFileTypeAllFiles
    .Execute
    ' Loop through all the found files
    For lngCounter = 1 To .FoundFiles.Count
    Set wbk = Workbooks.OpenXML(FileName:=.FoundFiles(lngCounter ), _
    LoadOption:=xlXmlLoadImportToList)
    wbk.SaveAs Replace$(.FoundFiles(lngCounter), ".xml", ".xls"), _
    xlWorkbookNormal
    wbk.Close False
    Next lngCounter

    Set wbk = Nothing
    End With
    Set fs = Nothing

    Application.ScreenUpdating = True
    End Sub


    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: batch xml 2 xls (2003)

    Great --- Tested works just fine!
    Many thanx
    Smbs

Posting Permissions

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