How to read in data from XML file into a Excel file using VBA

Question

I have the following problem, I have a very big XML file, which have to be read in Excel via VBA to fill in the different columns of the Excel sheet table.

I wrote the following code to open the XML file

Sub openxXML()
    Dim xml As Workbook
    Dim varxml As Variant

    varxml = Application.GetOpenFilename("Alle Data (*.*), *.*,XML-Data(*.xml),*xml*", 2, "Choose XML-File", _
      "choose", False)

    If varxml = False Then
        MsgBox "No Data selected"
    End If     
End Sub

But now I don't know how I turn this data into my Excel table with many columns.
Can anybody tell me if the code above is okay and how to continue?

Thank u and sorry for my English, I am from France

Please share a sample of XML and the Excel table you hope to produce. — Parfait
– Parfait, Commented Feb 23, 2018 at 15:33

Pᴇʜ · Accepted Answer · 2018-02-23 10:33:45Z

1

With Application.GetOpenFilename you only get the filename of the file a user selected. It does not open the file itself, just select a file from your hard drive.

So varxml contains something like C:\Temp\test.xml, a filename with its full path.

You then need to open/import/whatever you want to do.

For example to import:

Workbooks.OpenXML Filename:=varxml, LoadOption:=xlXmlLoadImportToList

An easy way would be using the macro recorder in Excel, and then perform the import manually once, so you get an idea of how, the import or whatever might look like in VBA.

answered Feb 23, 2018 at 10:33

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read in data from XML file into a Excel file using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related