Transforming XML to HTM with VBA

[JoyceW]

Oke, to start of I'm pretty much a beginner when in comes to VBA. For a
project I got an XML file that just won't import correctly into Access, it
leaves out columns and or data. My predecessor solved this with a workaround
by importing the XML into Excel, doing some data/text changes and then
importing the xls into Access, and also importing the XML into Access and
then combining both with a query.

I figured there had to be an easier way and ended up making a XML
transfersheet to convert the XML into HTML, putting the data in the correct
order and already renaming the headings. So far so good. I just don't want to
manually convert every XML (they are invoices we receive) we get. Then I
found this website:
http://www.oreillynet.com/xml/blog/2005/04/transforming_xml_in_microsoft.html
and now I'm stuck.

I've gotten this far:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

' Load data.
source.async = False
source.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.
xml"

' Load style sheet.
stylesheet.async = False
stylesheet.Load "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\
transform.xsl"

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.
reason)
Else
' Do the transform.
source.transformNodeToObject stylesheet, result
result.Save "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\
importeren.htm"
End If
End If

End Sub

and calling the transform:

Transform "Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\XML_Essent.
xml", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\transform.xsl", _
"Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.htm"

DoCmd.TransferText SpecificationName:="acimporthtml", TableName:="TY_OUTPUT",
_
FileName:="Y:\Staff\ufb\AC\DataExchange\XML\Essent\in te lezen\importeren.
htm", HasFieldNames:=True


But it doesn't reckognize the Transform as a Sub and I don't have enough
knowledge to solve this. Hope someone can help me with this.

Regards, Joyce