Solved: XML to Word

[jokevdmeer]

Hello Word VBA users,

Today it is the first time I am visiting this site. Knowing a bit about VB, I thought it must be very easy to convert a XML file into MS Word.

The XML file is a file without carriage returns, I thought reading the file row by row. But that is not possible!

Can someone give some help/information for reading a XML file row by row.(maybe with the help of a dll file)

Nice regards,

Joke.

[MOS MASTER]

Hoi en welkom bij VBAX Joke!

Writing a XML file to Word is definitly possible but it isn't as easy as you might think.
The XML is normally well structured and you need to read the Nodes / childnodes from the DOM.

Perhaps you have a small XML example for the data structure and a Word document which looks how you want it filled in? (attach zipped file)

If you have that I'll take a look it for yah.

Ik ben wel een beetje druk op het moment dus ik kan niet direct reageren.

[jokevdmeer]

Hello Mos Master,

Thank you very much for your reaction, like me you are from Holland but I will go further in the English language.

You ask for a sample of the xml file which I am trying to get into Word. The code must read sentence for sentence the information from the xml file.

Good luck and greetings,

Joke.

xml code:

<?xml version="1.0"?>
<Map><Algemeen><jaar>2005</jaar><zaken>3</zaken><zaken_af_te_handelen>0</zaken_af_te_handelen><Totaal_afgehandeld>3</Totaal_afgehandeld><Aantal_te_doen>3</Aantal_te_doen><Aantal_niet_gedaan>0</Aantal_niet_gedaan></Algemeen><Logs><Log><Zaaknaam>zaak_1</Zaaknaam><Zaaknummer>456</Zaaknummer><nummer/><zaakbegindatum/><zaakeindedatum/><Afdeling/><Vernietigingsnr/><Behandelaar>Mevr. de Groot</Behandelaar><Verwerkt_door>Janssens</Verwerkt_door><Datum_verwerkt>17-06-2005</Datum_verwerkt></Log><Log><Zaaknaam>zaak_2</Zaaknaam><Zaaknummer>456</Zaaknummer><nummer/><zaakbegindatum/><zaakeindedatum/><Afdeling/><Vernietigingsnr/><Behandelaar>Mevr. de Groot</Behandelaar><Verwerkt_door>Janssens</Verwerkt_door><Datum_verwerkt>17-06-2005</Datum_verwerkt></Log><Log><Zaaknaam>zaak_3</Zaaknaam><Zaaknummer>456</Zaaknummer><nummer/><zaakbegindatum>07.06.2005</zaakbegindatum><zaakeindedatum>16.06.2005</zaakeindedatum><Afdeling/><Vernietigingsnr/><Behandelaar>Mevr. de Groot</Behandelaar><Verwerkt_door>Janssens</Verwerkt_door><Datum_verwerkt>17-06-2005</Datum_verwerkt></Log></Logs></Map>

[jokevdmeer]

Hallo Joost,

Ik zag geen mogelijkheid om een file aan de reply te plakken, hier is de test.zip.

Groetjes,

Joke

[TonyJollans]

Hi Joke,

Welkom bij VBAX!

Niet te geloven maar zijn er hier anderstaligen so we must speak English

You are in very capable hands with Joost but a couple of questions if I may - What version of Word are you using and how do you want the end result to appear in Word?

[MOS MASTER]

Hi Tony,

You're absolutly right!, but you know I can't help myself..and it gives you some practice as well!

I don't know how Joke would like the data formatted in Word and don't know here version so I've build my sollution in 2000 so the reference will be upward compatible.

Hi Joke,

I've changed your XML file to reformat the XML so I can read it properly. I really don't like those Web programers who trow everything on one line! (Also I've added the missing data to check if my code works)

You've asked me to write the data in the XML Island to the Word document in the way it is presented in the XML file. For easy reading I've included the Nodename so you know where the data came from.

This is the code I'm using and you must set a reference to:
Microsoft XML. 4.0 (or higher or lower) (Tools/References)

The code:[VBA]
Option Explicit
'Set reference to Microsoft XML. 4.0 (or higher or lower)
Sub GetXML()
Dim oWordDoc As Word.Document
Dim oXmlDoc As DOMDocument
Dim oXmlNode As IXMLDOMNode
Dim oXmlChild As IXMLDOMNode
Dim errXml As IXMLDOMParseError
Dim iCnt As Integer
Application.ScreenUpdating = False

Set oXmlDoc = New DOMDocument
oXmlDoc.Load ThisDocument.Path & Application.PathSeparator & "Joke.xml"
Set errXml = oXmlDoc.parseError
If (errXml.errorCode <> 0) Then
MsgBox ("XML Parse error: " & errXml.reason)
Else
Set oWordDoc = Application.Documents.Add

oWordDoc.Range.ParagraphFormat.TabStops.Add _
Position:=CentimetersToPoints(7), _
Alignment:=wdAlignTabLeft, _
Leader:=wdTabLeaderSpaces

Set oXmlNode = oXmlDoc.selectSingleNode("Map/Algemeen")

If Not oXmlNode Is Nothing Then
For Each oXmlChild In oXmlNode.childNodes
oWordDoc.Range.InsertAfter Text:= _
oXmlChild.nodeName & vbTab & _
oXmlChild.nodeTypedValue & vbCr
Next
End If

oWordDoc.Range.InsertAfter Text:=vbCr

For iCnt = 0 To 3
Set oXmlNode = oXmlDoc.selectSingleNode("Map/Logs").childNodes(iCnt)

If Not oXmlNode Is Nothing Then
For Each oXmlChild In oXmlNode.childNodes
oWordDoc.Range.InsertAfter Text:= _
oXmlChild.nodeName & vbTab & _
oXmlChild.nodeTypedValue & vbCr
Next
End If

oWordDoc.Range.InsertAfter Text:=vbCr
Next
End If

Set oXmlChild = Nothing
Set oXmlNode = Nothing
Set errXml = Nothing
Set oXmlDoc = Nothing
Set oWordDoc = Nothing
End Sub
[/vba]

See the attachment for how it works you can execute the macro at once.

Enjoy!

[jokevdmeer]

Hello TonyJollans & Mos Master

For TonyJollans, I am very sorry to send information in Dutch, entiousiastic to found a good site for finding an possible answer for my question.

For Mos Master, the code is simple and good, thanks a lot for the information!

GREAT!

Joke.

PS. for MM: no problem, but the xls.doc started with an error!

[TonyJollans]

Hi Joke,

No need to apologise. It is natural for two Dutch people to speak Dutch together and as Joost says I need the practice

The reason I asked what version of Word you are using is that I can open your XML file directly in Word 2003 but Joost has done a super job for you.

Joost - I opened and ran (after setting reference) on Word 2000 but I couldn't open your document on Word 2003 - I haven't investigated why yet.

[jokevdmeer]

Hello Tony

I did use the code given by the Mos Master for filling tables in Word (2000). Searching the internet gave not a good result for reading a xml file and was desperating reading the xml file byte by byte but after receiving information from the MM it made everything more easy!

The MM made more then a super job for me (vbaexpress members)!

Joke.

[MOS MASTER]

Hello TonyJollans & Mos Master

For TonyJollans, I am very sorry to send information in Dutch, entiousiastic to found a good site for finding an possible answer for my question.

For Mos Master, the code is simple and good, thanks a lot for the information!

GREAT!

Joke.

PS. for MM: no problem, but the xls.doc started with an error!

Hi Joke,
You're most welcome!

It's strange the Word document is causing a error I've tested on all version's of Word! (Most be something in the enviroment)

What version are you running?

[MOS MASTER]

The reason I asked what version of Word you are using is that I can open your XML file directly in Word 2003 but Joost has done a super job for you.

Joost - I opened and ran (after setting reference) on Word 2000 but I couldn't open your document on Word 2003 - I haven't investigated why yet.

Hi Tony,

Thanks for the kuddos!

It's strange that you had to reset the reference in Word 2000 cause the app was made in Word 2000 PRO UK version....(Should make no difference with yours)

And over here I've got no problems with opening the XML in Word 2003 or 2002 or 2000 ......(Though it might be possible I've mistyped a tag cause I did it real fast and had to add a lot of them to fill in the blanks)

But since you've received the error in 2003 I suspect it has something to do with some new Security feature in Word. Don't know which one cause I usually kill such features if I encounter them. (Like the SQL security warning in mailmerge since 2003)

Let me know if you find the source?

[MOS MASTER]

Hello Tony

I did use the code given by the Mos Master for filling tables in Word (2000). Searching the internet gave not a good result for reading a xml file and was desperating reading the xml file byte by byte but after receiving information from the MM it made everything more easy!

The MM made more then a super job for me (vbaexpress members)!

Joke.

Yummy more Kuddos! I'll sleep so much better tonight...Good luck on your project...

[TonyJollans]

Hi Joost,

The problem seems to be the reference.

On Word 2000 (on another machine) it opens successfully with a MISSING XML 4.0 reference. On that machine I have XML v2.0 and setting that as a reference instead makes it all work.

On Word XP (on the same machine as 2003) it opens with a reference automatically set to the XML v5.0 library (part of Office 2003). If I save it in XP, without making any changes myself, it gets saved with the v5.0 reference and can then be opened in 2003.

[MOS MASTER]

Hi Tony,

I thought it would have something the do with the environment.
I took a closer look and found that on the 2000 machine I've had 2.0 3.0 and 4.0. This is caused by other tools I've installed. (So I think 2000 usually only has 2.0)

In the future I will set reference to the lowest version for upwards compatibilaty.

Later..

[TonyJollans]

Hi Joost,

There's more to it than that - why can't I open it in Word 2003? You say you can. I saved a copy of it with a reference to XML v2.0 in 2000 but still couldn't open it in 2003 - the fact that XP can open it in the same environment suggests the problem is with Word 2003 itself rather than the environment, but I have been known to be wrong

I just did a bit of testing before posting and cut and pasted the code and added a reference (v2.0) on 2000 and could open the new doc in 2003 no problem - so there is some other problem with your document.

As I said earlier - if I open it in XP and change the reference (to XML v5.0) I can then open the resulting document in 2003 so obviously that is a factor but it is interacting in some way with something else. I see you have a lot of styles with Dutch language set and wonder whether that is at all relevant. I'm afraid it's all guesswork at present.

[MOS MASTER]

Hi Tony,

Well the reference bit seams the most logic to me. I've made the document in 2000 and set reference to 4.0 and perhaps I've should have used 2.0 instead...

I've tried my document on at least 5 different machines the last days to see if I could get the error. (3 UK machines and 2 dutch)
I'm not able to do this.

My best guess is that all the machines I work on have a lot of libraries due to the development I'm doing so I never come across a machine that has a light install of Office. (Mine is always full with all extras)

My bet is still on different environment...but perhaps we'll never know.
And for that matter I've been proven wrong in the past and thank god that's helped me growing!

The thing I learned from this topic is that I better reference a lower library in the future. (As a general point of attention)

Later..