ExcelBanter - Help me to convert Excel to XML

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help me to convert Excel to XML (https://www.excelbanter.com/excel-programming/316977-help-me-convert-excel-xml.html)

Help me to convert Excel to XML

Help me to convert Excel to XML

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" wrote in message
om...
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|addre ss|phone
2.
term|T|TE|20|definition|abbreviation|translation|u sage|source|synonym|see
also|address|phone

whe
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
<trsptranscription</trsp
- <defn
<abbrabbreviation</abbr
<trnstranslation</trns
definition
<usgusage</usg
<srcsource</src
<synsynonym</syn
<seesee also</see
<addressaddress</address
<phonephone number</phone
</defn
</line
</glossword

Thank you for all your help

Regards,

Ivaylo

Help me to convert Excel to XML

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!

Help me to convert Excel to XML

Try one of these

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

"Tim Williams" <saxifrax at pacbell dot net wrote in message ...
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" wrote in message
om...
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|addre ss|phone
2.
term|T|TE|20|definition|abbreviation|translation|u sage|source|synonym|see
also|address|phone

whe
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
<trsptranscription</trsp
- <defn
<abbrabbreviation</abbr
<trnstranslation</trns
definition
<usgusage</usg
<srcsource</src
<synsynonym</syn
<seesee also</see
<addressaddress</address
<phonephone number</phone
</defn
</line
</glossword

Thank you for all your help

Regards,

Ivaylo

Help me to convert Excel to XML

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!