CODEWITHSUNDEEP
xmlvbaexcel
Tara
Tara

Reputation: 1

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

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

Upvotes: 0

Views: 2043

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

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.

Upvotes: 1

Related Questions