locked
Converting XML files to XLSX files without opening them RRS feed

  • Question

  • I have a program which generates reports in an old version of XML which is no longer supported by our network security, thus Excel refuses to open them. Is there any way to get VBA to transfer the data from the XML file (which conveniently is named file.xls) to .xlsx file without actually opening the sheet?
    Friday, July 15, 2016 10:30 AM

Answers

  • Hi,

    >>Is there any way to get VBA to transfer the data from the XML file (which conveniently is named file.xls) to .xlsx file without actually opening the sheet

    >> It's just a standard XML file disguised as a spreadsheet

    Is the xml file with extension .xml or .xls? Follow here to show its extension.

    I think it is impossible for us to get the data without opening(refers to a state instead of an action).

    However, we can let Office do what we want in the background and doesn't display, then it looks like we don't open it. Actually, the file is opened programmatically.

     

    A simple code below converts XML to XLSX without showing. Replace the path under your requirement. As for the loading option(bold parts), see Workbooks.OpenXML Method (Excel)

     

    Sub test()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Dim wb As Workbook

    With Workbooks.OpenXML("C:\Users\Administrator\Desktop\migration.xml", , xlXmlLoadImportToList)

    .SaveAs ("C:\Users\Administrator\Desktop\test.xlsx")

    .Close

    End With

    End Sub

     

    If the file extension is .xls, and now you want to convert it to .xlsx,

    Sub test()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Dim wb As Workbook

    With Workbooks.Open("C:\Users\Administrator\Desktop\output.xls")

    .SaveAs ("C:\Users\Administrator\Desktop\test.xlsx")

    .Close

    End With

    End Sub

    Monday, July 18, 2016 9:31 AM

All replies

  • Could you share a sample XML file?

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Friday, July 15, 2016 11:07 AM
  • Unfortunately no I can't.  It's got private information on it and I can't delete it as I can't open the file to do so.  It's just a standard XML file disguised as a spreadsheet.  Up until recently I'd open it, hit the convert button and close it again... but they broke me being able to open it to do that.
    • Edited by Myysterio Friday, July 15, 2016 11:29 AM
    Friday, July 15, 2016 11:28 AM
  • This link might be helpful

    http://officetricks.com/convert-xml-to-excel/

    -----------------------------------------

    Free .NET Excel supporting Create, Manipulate, Convert .XLS and .XLSX file formats.

    Monday, July 18, 2016 2:14 AM
  • Hi,

    >>Is there any way to get VBA to transfer the data from the XML file (which conveniently is named file.xls) to .xlsx file without actually opening the sheet

    >> It's just a standard XML file disguised as a spreadsheet

    Is the xml file with extension .xml or .xls? Follow here to show its extension.

    I think it is impossible for us to get the data without opening(refers to a state instead of an action).

    However, we can let Office do what we want in the background and doesn't display, then it looks like we don't open it. Actually, the file is opened programmatically.

     

    A simple code below converts XML to XLSX without showing. Replace the path under your requirement. As for the loading option(bold parts), see Workbooks.OpenXML Method (Excel)

     

    Sub test()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Dim wb As Workbook

    With Workbooks.OpenXML("C:\Users\Administrator\Desktop\migration.xml", , xlXmlLoadImportToList)

    .SaveAs ("C:\Users\Administrator\Desktop\test.xlsx")

    .Close

    End With

    End Sub

     

    If the file extension is .xls, and now you want to convert it to .xlsx,

    Sub test()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Dim wb As Workbook

    With Workbooks.Open("C:\Users\Administrator\Desktop\output.xls")

    .SaveAs ("C:\Users\Administrator\Desktop\test.xlsx")

    .Close

    End With

    End Sub

    Monday, July 18, 2016 9:31 AM
  • If your network security has locked down these xml/xls files and the lock-down is very robust, then anything you do that resembles an 'open file' will likely fail; if you can programmatically open the file and bypass the security, then the security is pretty sucky and you probably need to let the security group know.

    So, assuming you have a good/robust security system in place and you are locked out of these files, you need to contact the security group, explain the issue, and then have them help you find a solution. We live under a very robust security environment and I just work with the security folks for fixes; often they'll disable the rules on my pc for enough time to get <whatever task I need to do> completed, then they re-enable the rules.

    Monday, July 18, 2016 11:09 AM
  • how to run it for multiple files in a folder. so that the converted file get the name of the source file .
    • Edited by jindaldk Monday, October 2, 2017 1:28 PM
    Monday, October 2, 2017 1:26 PM
  • Hi jindaldk,

    For your own issue, I would suggest you post a new thread, and then we could focus on discussing your issue.

    Regards,

    Tony


    Help each other

    Wednesday, October 4, 2017 3:13 AM