CODEWITHSUNDEEP
xmlvbaexcel
LuFaMa
LuFaMa

Reputation: 346

Copy content of Excel sheet to a text or XML file

I have an Excel (2010) workbook. One of the sheets contains about 800 lines of XML tags.

It looks something like this:

<?xml version="1.0" encoding="UTF-8" ?>
<Data>
 <Entry> 
  <Name>John<Name>
  <Age>58<Age>
 </Entry>
 <Entry>
  <Name>Amy<Name>
  <Age>29<Age>
 </Entry>
</Data>

Each element is on a seperate row/cell.

A1 = <?xml version="1.0" encoding="UTF-8" ?>
A2 = <Data>
A3 = <Entry> 
A4 = <Name>John<Name>
A5 = <Age>58<Age>

. . .

And so on.

These aren't plain text values - the content is generated via a CONCATENATE across different cells in a different sheet.

The formula for cell A3 is as follows:

=CONCATENATE(XML!A3,XML!B3,XML!C3)

I want to create a VBA macro that outputs the content of this sheet into an XML file or a text file. It's already formatted as an XML file so I just want the content saved out.

All I'm finding online is how to save the sheet itself as an XML but that's converting all my data into garbage since it's already formatted, at least in appearance. If that makes sense.

Any advice would be appreciated as I haven't worked a lot with VBA.

Thanks in advance!

Upvotes: 0

Views: 3547

Answers (1)

techturtle
techturtle

Reputation: 2587

Sub SaveAsXML()
    Dim strFileName As String
    strFileName = Application.GetSaveAsFilename(filefilter:="XML Files (*.xml),*xml")
    ActiveSheet.SaveAs Filename:=strFileName, FileFormat:=xlTextPrinter, CreateBackup:=False
End Sub

The FileFormat has to be xlTextPrinter or else it does weird things with any quotes in the string.

Upvotes: 5

Related Questions