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 & _
"<trsptranscription</trsp " & vbCrLf & _
"<defn " & vbCrLf & _
"<abbrabbreviation</abbr " & vbCrLf & _
"<trnstranslation</trns " & vbCrLf & _
"definition " & vbCrLf & _
"<usgusage</usg " & vbCrLf & _
"<srcsource</src " & vbCrLf & _
"<synsynonym</syn " & vbCrLf & _
"<seesee also</see " & vbCrLf & _
"<addressaddress</address " & vbCrLf & _
"<phonephone 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







"Ivaylo Ivanov" wrote in message
...


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<trsptranscription</trsp<defn<abbrabbreviation</a
bbr<trnstranslation</trnsdefinition<usgusage</usg<srcsource</src<
synsynonym</syn<seesee
also</see<addressaddress</address<phonephone
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_ad...w_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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!