If you want to perform import or export using VBA, you need to use the corresponding method.
To import XML data into an Excel workbook , use the XmlImport method:
expression.XmlImport(Url, ImportMap, Overwrite, Destination)
- expression — a reference to the object (the workbook).
- Url — a URL or full path to the file with XML data.
- ImportMap — the map used when importing the file; if the data was imported earlier, this contains a reference to the object that stores the XML map.
- Overwrite — determines whether to overwrite data that has been mapped by the map specified in the ImportMap parameter; set True to overwrite existing mapped data, set False to append new data to existing data; by default this parameter is True.
- Destination — specifies the range where the imported data will be placed; this parameter holds a reference to the top-left cell of the range.
Importing an XML file
Sub Imp()
ActiveWorkbook.XmlImport _
URL:="D:\Data.xml", _
ImportMap:=Nothing, _
Overwrite:=True, _
Destination:=Range("$A$1")
End Sub
To export data from an Excel workbook that contains a map, use the Export method of an XML map (the text refers to ExportXml, but the example shows exporting via an XmlMap object’s Export):
expression.ExportXml(Data)
- expression — a reference to the object (the workbook) that contains the XML map.
- Data — the full path to the file to be exported.
Exporting data to an XML file
Sub Exp()
ActiveWorkbook.XmlMaps("employees_map").Export _
URL:="D:\Export.xml"
End Sub