Using VBA to convert XML to CSV

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Using VBA to convert XML to CSV

Logged in as: Guest

arrSession:exec spGetSession 2,7,58196

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [General Forum] >> Other Programming/Scripting Languages >> Using VBA to convert XML to CSV
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Using VBA to convert XML to CSV - 3/19/2008 3:53:03 AM

bobwhosmiles

Posts: 1
Score: 0
Joined: 3/19/2008
Status: offline

Hi All,

Near the start of the thread below:

http://www.visualbasicscript.com/m_25624/tm.htm

mconnelly posts a bit of VBA and XSL code.

I'm testing this out as VBA is exactly what I want to use but I always just end up with an empty CSV file.

My XML looks like this:

My XSL file looks like this:

And finally my VBA looks like:

Sub xmltocsv()
Dim oDOM As MSXML2.DOMDocument
Dim oXML As MSXML2.DOMDocument
Dim oXSL As MSXML2.DOMDocument
Dim strHTML As String
Dim strTransform As String
Set oDOM = CreateObject("MSXML2.DOMDocument")
oDOM.async = False
oDOM.Load ("C:\Documents and Settings\Robert Peck\Desktop\PT Form\PT-Webform_data.xml")
Set oXSL = CreateObject("MSXML2.DOMDocument")
oXSL.async = False
oXSL.Load "C:\Documents and Settings\Robert Peck\Desktop\PT Form\ConvertToCsv.xsl"
'your XSLT stylesheet should be saved as unicode or UTF not ansii
'note encoding instruction maybe needed for european language encoding  say swedish characters
strTransform = oDOM.transformNode(oXSL)
strHTML = "<?xml version='1.0' encoding=""ISO-8859-1""?>" & vbCrLf & _
          "<root>" & strTransform & "</root>"
WriteFile "C:\Documents and Settings\Robert Peck\Desktop\PT Form\CSVversion" & Format(Now, "yyyymmddhhmmss") & ".csv", strTransform
'
'the above XSLT transform with xsl file converts this to a flat csv format file
Set oDOM = Nothing
Set oXML = Nothing
Set oXSL = Nothing
End Sub

Public Sub WriteFile(ByVal sFileName As String, ByVal sContents As String)
' Dump XML  String to File for debugging
  Dim fhFile As Integer
  fhFile = FreeFile
 ' Debug.Print "Length of string=" & Len(sContents)
  Open sFileName For Output As #fhFile
  Print #fhFile, sContents;
  Close #fhFile
  Debug.Print "Out File" & sFileName
End Sub

Can anyone help with why this isn't working?

cheers,

Bob

Post #: 1

RE: Using VBA to convert XML to CSV - 4/18/2008 11:39:06 AM

daluu

Posts: 26
Score: 0
Joined: 4/17/2006
Status: offline

Sorry I don't have time to look at your code. However, in general, if you are using XSLT to do the transform, VBScript or any other language used is very simple to code - open/load XML doc into XML DOM object, same for XSL file, then do the transform of the XML with the XSL.

So any problems you get are from your XSL file, unless you have syntactically bad XML. You'll just have to keep on tweaking the XSL until you get the desired output. Go see some XSLT tutorials online to familiarize yourself with how to work with XSL.

(in reply to bobwhosmiles)

Post #: 2

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [General Forum] >> Other Programming/Scripting Languages >> Using VBA to convert XML to CSV

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts