create XML from excel file

[DotNetGnat]

Guys,

How do i create XML file from excel sheet...

any pointers, suggestions are much appreciated...

Thanks

-DNG

 

[k5tm]

I find a lot of helpful information in the Microsoft Excel help file, in the topic titled, Export XML data.

Tom Morrison

http://www.liant.com

 

[cvdtbrrsln]

Let's write a macro.

1. How can we reach content of any cell?

For A2 cell, we have 5 ways,

   [b]a.[/b] Cells(1,2)
      [i]Cells(row-index,col-index)[/i]
   
   [b]b.[/b] Worksheets("Sheet1").Range("A2").Value

   [b]c.[/b] Worksheets("Sheet1").Cells(1,2).Value   

   [b]d.[/b] Sheet1.Cells(1, 2).Value   

   [b]e.[/b] Sheets("Sheet1").Range("A2")

2. How can we create xml file from excel sheet? It's your question...

Open "C:\Windows\Desktop\sample.xml" For Output As #1

Open "path\fileName.extension" For Output As #file_ ID
file_ID : An Integer value.

Print #1,"Peace at home, peace in the world."

Print #file_ID,"content"

Close #1

Close #file_ID

3. It's end of the story.

A B C D
1 Book-id Author-id ISBN Name
2 123 12345 123-45-6789 A macro for XML
3
4

Sub XML()
   Open "C:\Windows\Desktop\books.xml" For Output As #1
   Print #1, "<?xml version=""1.0"" encoding=""iso-8859-9"" ?>"
   Print #1, "<books>"
   Dim row-index
   row-index = 2 ' First record.

    Do Until Cells(row-index, 1) = ""

     Print #1, "<book id =" & Chr(34) & Cells(row-index, 1) & Chr(34) & "author-id =" & Chr(34) & Cells(row-index, 2) & Chr(34)&  "isbn =" & Chr(34) & Cells(row-index, 3) & Chr(34) & ">" & Cells(row-index, 4) &"</book>"    

     row-index = row-index + 1
    Loop

    Print #1, "</books>"
    Close #1
End Sub

<?xml version="1.0" encoding="iso-8859-9" ?>
<books>
<book id ="123" author-id ="12345" isbn ="123-45-789">A macro for XML</book>    
.
.
.
</books>

4. What is the Chr(34)?

Chr(34) character code represents "

   ....  id=" & Chr(34) & "content" & Chr(34) &" .... 
   ....  id=""content"" ....
   ....  id='FB'  ....

All of these are the same.

5. Do you have any question?

 

[jv71]

Hi I've got the same problem, I've tried your macro but its not working......my issue is a bit different as I have to parse it to a schema. I've done this on excel but when its exported into the system that its to be uploaded, the Excel tegs are showing.

My programming knowledge is not very strong as Im' more on the business side of things so the simpler the response the better.

Thanks,

 

[cvdtbrrsln]

Sub XML()
   Open "C:\books.xml" For Output As #1
   Print #1, "<?xml version=""1.0"" encoding=""iso-8859-9"" ?>"
   Print #1, "<books>"
'[COLOR=green]Sorry. In VB, variable's name do not contain "-". It would be underscore "_".[/color]
   Dim row_index
   row_index = 2 ' First record.

    Do Until Cells(row_index, 1) = ""
[COLOR=green]'And I've added two spaces to following lines of codes. [/color]
     Print #1, "<book id =" & Chr(34) & Cells(row_index, 1) & Chr(34) & [COLOR=red]" [/color]author-id =" & Chr(34) & Cells(row_index, 2) & Chr(34) & [COLOR=red]" [/color] isbn =" & Chr(34) & Cells(row_index, 3) & Chr(34) & ">" & Cells(row_index, 4) & "</book>"

     row_index = row_index + 1
    Loop

    Print #1, "</books>"
    Close #1
End Sub

"iso-8859-9" represents Turkish character set. You can use "utf-8".