Convert xml file to xlsx file with VBA

Question

How can I convert xml files to xlsx?

The xml files existing in a folder and I want to save the xlsx files to an other folder.

Example:

Existing file: C:\Users\John\Documents\xml\data.xml
Convert to this: C:\Users\John\Documents\xlsx\data.xlsx

The best way would be to write a macro in excel or that import your xml to excel. Excel has a property to import xml as table. You can extend it over a VBA macro. — Dev Utkarsh, Jul 26 '16 at 10:49
Try recording a macro with @anonxss mentioned method and see if what comes out will work for you. — Gary Evans, Jul 26 '16 at 12:51
Yes, the macro is what I am asking for! :) Could somebody write this code? Because I don't. This folder contains more xml files so the VBA code should be write according to this case. — vergab, Jul 26 '16 at 13:47
Possible duplicate of [VBA code to change file format of multiple files in a folder](http://stackoverflow.com/questions/38572836/vba-code-to-change-file-format-of-multiple-files-in-a-folder) — Mikegrann, Jul 26 '16 at 13:54
There is something wrong with that code. Because I can not change it as Scott Holtzman wrote. It shows error message: "end of statement" — vergab, Jul 26 '16 at 14:14
Anybody can show me a properly working VBA code for this issue? — vergab, Jul 27 '16 at 09:20

score 2 · Accepted Answer · answered Aug 02 '16 at 12:57

I did it.

Public Sub ConvertXmlToXlsx()

Application.DisplayAlerts = False

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object

xmlFolder = "C:\Users\John\Documents\xml\"
convFolder = "C:\Users\John\Documents\xls\"


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(xmlFolder)
For Each objFile In objFolder.Files
    If UCase(Right(objFile.Name, Len(XML))) = UCase(XML) Then
        NewFileName = convFolder & objFile.Name & "_conv.xlsx"

        Set ConvertThis = Workbooks.Open(objFolder & "\" & objFile.Name)
        ConvertThis.SaveAs Filename:=NewFileName, FileFormat:= _
        xlOpenXMLWorkbook
        ConvertThis.Close
    End If
Next objFile

End Sub

0m3r · Answer 2 · 2016-08-02T08:14:26.200

Is this what your trying to do?

Option Explicit
Public Sub XML_To_xlsx()
    Dim FilePath As String
    Dim Book As Workbook

'   Load XML Data to New Workbook
    FilePath = "C:\Users\John\Documents\xml\data.xml"
    Set Book = Workbooks.OpenXML(FilePath)

   'Copy to active Worksheet
    Book.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")

    'Close New Workbook
    Book.Close False
End Sub

Convert xml file to xlsx file with VBA

2 Answers2