Help me to convert Excel to XML

[Ivaylo]

Hi all,

I need to convert data stored in MS Excel into a .xml file.
Can you help me to create a macro which transforms the following Excel
structure into xml:

1. term|t1|t2|id|defn|abbr|trns|usg|src|syn|see|address|phone
2. term|T|TE|20|definition|abbreviation|translation|usage|source|synonym|see
also|address|phone

where:
1. is the header row
2. is a single record
"|" marks the table colums

I need to convert the above structure like this (where "line"
coresponds to a row):

<?xml version="1.0" encoding="UTF-8" ?>
- <glossword>
- <line>
<term t1="T" t2="TE" id="20">term</term>
<trsp>transcription</trsp>
- <defn>
<abbr>abbreviation</abbr>
<trns>translation</trns>
definition
<usg>usage</usg>
<src>source</src>
<syn>synonym</syn>
<see>see also</see>
<address>address</address>
<phone>phone number</phone>
</defn>
</line>
</glossword>

Thank you for all your help

Regards,

Ivaylo

 

[Tim Williams]

Which part are you having a problem with ? Do you require help with
looping through the cells or constructing the XML string?

Assuming all lines have the same structure (ie the same header line) you can
use a "template" and just use replace() to replace tokens with the actual
values.
It would be more difficult to construct it completely from nothing using a
completely generic approach since there is no indication in your example as
to which values are attributes and which are values.

Tim.

 

[Ivaylo Ivanov]

Hi Tim,

Actually I'm a newbie in VBA and trying to construct a code by myself. I
use a web-based glossary system (Glossword) which supports import/export
in XML format. In order to populate the database I want to import my
existing glossaries (currently maintained in Excel) into the database. I
exported a sample entry from the glosary. And it looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<glossword>
<line><term t1="T" t2="TE"
id="20">term</term><trsp>transcription</trsp><defn><abbr>abbreviation</a
bbr><trns>translation</trns>definition<usg>usage</usg><src>source</src><
syn>synonym</syn><see>see
also</see><address>address</address><phone>phone
number</phone></defn></line>
</glossword>

I want to make an Excel template (containing a macro) which can
transform the table structure into XML file.

The header is the same for all the entries. The attributes t1 and t2
matks the first letter of the word, and the first two letters
respectively. You can check out the structure at the following link:

http://www.ivaylo.com/glossary/gw_admin/export/xml/00002_gw_xgytyg3_1_of
_1.xml?sid=f75d2518095466d72bd048eced536c6f

Maybe I need a simple find and replace looping bu I don't know how to do
it.

I'll be very thankful for your help

Thanks

Ivaylo

 

[Dsuperc]

Try one of these

http://office.microsoft.com/en-us/assistance/HA011019641033.aspx
http://www.sswug.org/see/20834

 

[Tim Williams]

Assuming your first "line" starts in A2 (so your headers are on the first
row) of a worksheet "Data" then this should get you started:

Option Explicit

Sub CreateXML()

Const NUM_REPLACE As Integer = 4 'change to suit = number of tokens to
replace

Const SHEADER = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & _
"<glossword>"

Const SFOOTER = "</glossword>"

'This is a template for a single "line" item - you will replace the "{x}"
' tokens with the contents of the relevant cells on the sheet
' alter to suit your layout (I just put in a few and they may not be
correct)
' {1} = value from first cell in row
' {2} = value from second cell in row
' etc etc
Const SLINE = "<line>" & vbCrLf & _
"<term t1=""{1}"" t2=""{2}"" id=""{3}"">{4}</term> " &
vbCrLf & _
"<trsp>transcription</trsp> " & vbCrLf & _
"<defn> " & vbCrLf & _
"<abbr>abbreviation</abbr> " & vbCrLf & _
"<trns>translation</trns> " & vbCrLf & _
"definition " & vbCrLf & _
"<usg>usage</usg> " & vbCrLf & _
"<src>source</src> " & vbCrLf & _
"<syn>synonym</syn> " & vbCrLf & _
"<see>see also</see> " & vbCrLf & _
"<address>address</address> " & vbCrLf & _
"<phone>phone number</phone> " & vbCrLf & _
"</defn> " & _
"</line>"

Dim sTemp As String
Dim sXML As String
Dim iRow As Integer
Dim i As Integer

sXML = SHEADER
iRow = 2

Do While ThisWorkbook.Sheets("Data").Cells(iRow, 1).Value <> ""

sTemp = SLINE
With ThisWorkbook.Sheets("Data").Rows(iRow)
For i = 1 To NUM_REPLACE
sTemp = Replace(sTemp, "{" & i & "}", .Cells(1).Value)
Next i
End With
iRow = iRow + 1
sXML = sXML & vbCrLf & sTemp
Loop


sXML = sXML & vbCrLf & SFOOTER

MsgBox sXML

End Sub