Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help me to convert Excel to XML
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can the "convert" function in excel convert to UK gallons? | Excel Discussion (Misc queries) | |||
How do I convert a Macintosh version 1 excel file to excel 2003 | Excel Discussion (Misc queries) | |||
How to convert excel Docs to pocket excel on my mobile device | New Users to Excel | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) |